Sub ImprimerContrôlesUserForm()
Dim c As Control, t$(), n%
For Each c In Controls
If TypeName(c) = "TextBox" Then
ReDim Preserve t(1, n) 'base 0
t(0, n) = c.Name
t(1, n) = "TextBox"
n = n + 1
ElseIf TypeName(c) = "ListBox" Then
ReDim Preserve t(1, n)
t(0, n) = c.Name
t(1, n) = "ListBox"
n = n + 1
End If
Next
If n Then
Application.ScreenUpdating = False
With Workbooks.Add.Sheets(1)
.[A1] = "Contrôle": .[A1].Font.Bold = True
.[B1] = "Type": .[B1].Font.Bold = True
.[A2].Resize(n, 2) = Application.Transpose(t)
.Columns.AutoFit 'ajustement largeur
.[A2].Resize(n, 2).Sort .[A2], Header:=xlNo 'tri sur colonne A
.PrintOut 'impression
.Parent.Close False
End With
Application.ScreenUpdating = True
End If
End Sub
Sub ImprimerContrôlesUserForm()
Dim c As Control, t$(), n%
For Each c In Controls
If TypeName(c) = "TextBox" Then
ReDim Preserve t(2, n) 'base 0
t(0, n) = c.Name
t(1, n) = "TextBox"
t(2, n) = c.Parent.Name
n = n + 1
ElseIf TypeName(c) = "ListBox" Then
ReDim Preserve t(2, n)
t(0, n) = c.Name
t(1, n) = "ListBox"
t(2, n) = c.Parent.Name
n = n + 1
End If
Next
If n Then
Application.ScreenUpdating = False
With Workbooks.Add.Sheets(1)
.[A1] = "Contrôle": .[A1].Font.Bold = True
.[B1] = "Type": .[B1].Font.Bold = True
.[C1] = "Page": .[C1].Font.Bold = True
.[A2].Resize(n, 3) = Application.Transpose(t)
.Columns.AutoFit 'ajustement largeur
.[A2].Resize(n, 3).Sort .[A2], Header:=xlNo 'tri sur colonne A
.PrintOut 'impression
.Parent.Close False
End With
Application.ScreenUpdating = True
End If
End Sub
For Each c In UserForm1.Controls 'nom de l'USF
Sub ImprimerContrôlesUserForm()
Dim c As Control, typ$, t$(), n%
For Each c In UserForm1.Controls
typ = TypeName(c)
If typ = "TextBox" Or typ = "ListBox" Then
ReDim Preserve t(2, n) 'base 0
t(0, n) = c.Name
t(1, n) = typ
t(2, n) = c.Parent.Name
n = n + 1
End If
Next
If n Then
Application.ScreenUpdating = False
With Workbooks.Add.Sheets(1)
.[A1] = "Contrôle": .[A1].Font.Bold = True
.[B1] = "Type": .[B1].Font.Bold = True
.[C1] = "Page": .[C1].Font.Bold = True
.[A2].Resize(n, 3) = Application.Transpose(t)
.Columns.AutoFit 'ajustement largeur
.[A2].Resize(n, 3).Sort .[A2], Header:=xlNo 'tri sur colonne A
.PrintOut 'impression
.Parent.Close False
End With
Application.ScreenUpdating = True
End If
End Sub