I have an application with a number of user froms with many different
controls on each. I wish to list all userform controls, names and captions
on a new sheet. Further for each userform list and group the control types

I have looked at the Userforms collection and Controls collection but not
established the best method to extract and organise the controls by type etc.

Does anyone know of a utility to do this?

Thanks

Hi Nigel,
You can try:

Sub UserFormsProperties()
Dim i%, u%, y%, obj As Object
Application.ScreenUpdating = False
Workbooks.Add
With ThisWorkbook.VBProject
For Each obj In .VBComponents
If obj.Type = 3 Then
y = y + 1
Cells(1, y) = obj.Name
Cells(1, y).Font.Bold = True
For i = 1 To obj.Properties.Count
On Error Resume Next
Cells(i + 1, y) = obj.Properties(i).Name
Cells(i + 1, y + 1) = obj.Properties(i).Value
Next i
Call ControlsProperties(obj, i + 1, y)
y = y + 3
End If
Next obj
End With
Cells.Columns.AutoFit
End Sub

' Need reference to TypLib Information
' C:\Windows\System32\Tlbinf32.dll
Private Sub ControlsProperties(obj As Object, ByVal i%, ByVal y%)
Dim oMember As MemberInfo
Dim sProp$, sVal$, Ctl As Control
For Each Ctl In obj.Designer.Controls
i = i + 1
Cells(i, y) = Ctl.Name
Cells(i, y).Font.Bold = True
i = i + 1
Set oInfo = InterfaceInfoFromObject(Ctl)
For Each oMember In oInfo.Members
On Error Resume Next
If oMember.InvokeKind = 4 Then
sProp = oMember.Name
sVal = CallByName(Ctl, sProp, VbGet)
Cells(i, y) = oInfo.Name
Cells(i, y + 1) = sProp
Cells(i, y + 2) = sVal
i = i + 1
End If
Next oMember
Next Ctl
End Sub

Regards,
MP

"Nigel RS" a écrit dans le message de
news: ...
I have an application with a number of user froms with many different
controls on each. I wish to list all userform controls, names and
captions
on a new sheet. Further for each userform list and group the control
types

I have looked at the Userforms collection and Controls collection but not
established the best method to extract and organise the controls by type
etc.

Does anyone know of a utility to do this?

Thanks

Many thanks. works ok

--
Cheers
Nigel



"Michel Pierron" wrote in message
...
Hi Nigel,
You can try:

Sub UserFormsProperties()
Dim i%, u%, y%, obj As Object
Application.ScreenUpdating = False
Workbooks.Add
With ThisWorkbook.VBProject
For Each obj In .VBComponents
If obj.Type = 3 Then
y = y + 1
Cells(1, y) = obj.Name
Cells(1, y).Font.Bold = True
For i = 1 To obj.Properties.Count
On Error Resume Next
Cells(i + 1, y) = obj.Properties(i).Name
Cells(i + 1, y + 1) = obj.Properties(i).Value
Next i
Call ControlsProperties(obj, i + 1, y)
y = y + 3
End If
Next obj
End With
Cells.Columns.AutoFit
End Sub

' Need reference to TypLib Information
' C:\Windows\System32\Tlbinf32.dll
Private Sub ControlsProperties(obj As Object, ByVal i%, ByVal y%)
Dim oMember As MemberInfo
Dim sProp$, sVal$, Ctl As Control
For Each Ctl In obj.Designer.Controls
i = i + 1
Cells(i, y) = Ctl.Name
Cells(i, y).Font.Bold = True
i = i + 1
Set oInfo = InterfaceInfoFromObject(Ctl)
For Each oMember In oInfo.Members
On Error Resume Next
If oMember.InvokeKind = 4 Then
sProp = oMember.Name
sVal = CallByName(Ctl, sProp, VbGet)
Cells(i, y) = oInfo.Name
Cells(i, y + 1) = sProp
Cells(i, y + 2) = sVal
i = i + 1
End If
Next oMember
Next Ctl
End Sub

Regards,
MP

"Nigel RS" a écrit dans le message de
news: ...
I have an application with a number of user froms with many different
controls on each. I wish to list all userform controls, names and
captions
on a new sheet. Further for each userform list and group the control
types

I have looked at the Userforms collection and Controls collection but not
established the best method to extract and organise the controls by type
etc.

Does anyone know of a utility to do this?

Thanks