Anyone have code or know of a utility to list all userforms and controls
types and captions on a worksheet?

Thanks

Anyone have code or know of a utility to list all userforms and controls
types and captions on a worksheet?

The best you're going to do from a global macro is to get the Form
Filename, Form name and Form Caption. Things get VERY complicated when
delving any deeper on a generalized basis. This is because you can NOT get
at UserForm objects from outside the workbook that contains the form.
Unfortunately, Excel just doesn't expose them. Maybe 2007???? Anyone know?
Probably not because, IMHO, Microsoft only made 2007 prettier NOT more
functional.
I've written a macro below to get at the Form Filename, Form name and Form
Caption for you.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

'/=============================================/
Public Sub ListFormInfo()
Dim i As Integer, x As Integer
Dim iVbProjectsCount As Integer, iVBCompCount As Integer
Dim iRow As Long
Dim strNewSheet As String
Dim strVBProjectName As String
Dim strFilename As String
Dim strModuleName As String

On Error GoTo err_Sub

'Note: Types of VBComponents
'vbext_ct_StdModule = 1 = Standard Module
'vbext_ct_ClassModule = 2 = Class Module
'vbext_ct_MSForm = 3 = Microsoft Form
'vbext_ct_ActiveXDesigner = 11 = Active X Designer
'vbext_ct_Document = 100 = Document Module
' - worksheets / Graphs / etc

'check for an active workbook
'if no workbooks open, create one
If ActiveWorkbook Is Nothing Then
Workbooks.Add
End If

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move After:=Worksheets(Worksheets.Count)
strNewSheet = Worksheets(Worksheets.Count).Name

Worksheets(strNewSheet).Cells(1, 1) = "Filename"
Worksheets(strNewSheet).Cells(1, 2) = "Form Name"
Worksheets(strNewSheet).Cells(1, 3) = "Form Caption"

i = 0
iRow = 1

iVbProjectsCount = Application.VBE.VBProjects.Count

For i = 1 To iVbProjectsCount
strVBProjectName = Application.VBE.VBProjects(i).Filename
If Application.VBE.VBProjects(i).Protection = 0 Then
iVBCompCount = _
Application.VBE.VBProjects(i).VBComponents.Count

'Find the Modules and then process
For x = 1 To iVBCompCount
Err.Clear
'check for a Form - 3 = Microsoft Form
If Application.VBE.VBProjects(i).VBComponents(x).Type = 3 Then
'Get File Name only from Full File Name
strFilename = _
Trim(Application.VBE.VBProjects(i).Filename)
'get module name
strModuleName = _
Application.VBE.VBProjects(i).VBComponents(x).Name
iRow = iRow + 1
Worksheets(strNewSheet).Cells(iRow, 1) = strFilename
Worksheets(strNewSheet).Cells(iRow, 2) = strModuleName
Worksheets(strNewSheet).Cells(iRow, 3) = _

Application.VBE.VBProjects(i).VBComponents(x).Prop erties(39).Value
End If
Next x
End If
Next i

Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - " & _
Err.Description & " - " & Now()
Resume Next
End Sub
'/=============================================/

No sure what you're after, as a userform is not on a worksheet.

If you want the controls that are embedded on a worksheet, you could loop
through that WS's OLEObjects collection, testing for the type of control,
then it's caption, if applicable.

Dim Cntrl As Object

For Each Cntrl In Worksheets(1).OLEObjects
Debug.Print Cntrl.Name, Cntrl.progID
Next

As for userforms, I suppose you can get the info with
http://www.cpearson.com/excel/vbe.htm

NickHK

"Nigel RS" wrote in message
...
Anyone have code or know of a utility to list all userforms and controls
types and captions on a worksheet?

Thanks

Having reread your question, you get the userform to list its own details
using its .Controls collection:

Dim obj As Object

For Each obj In Me.Controls
Debug.Print obj.Name, TypeName(obj)
Next

The userform obviously has to be loaded for this to work.

NickHK

"Nigel RS" wrote in message
...
Anyone have code or know of a utility to list all userforms and controls
types and captions on a worksheet?

Thanks

Thanks for this. I suspected this was the case but you never know what
workarounds have been developed.

I will review code and see if it fits the bill
Cheers


"Gary L Brown" wrote:

Anyone have code or know of a utility to list all userforms and controls
types and captions on a worksheet?

The best you're going to do from a global macro is to get the Form
Filename, Form name and Form Caption. Things get VERY complicated when
delving any deeper on a generalized basis. This is because you can NOT get
at UserForm objects from outside the workbook that contains the form.
Unfortunately, Excel just doesn't expose them. Maybe 2007???? Anyone know?
Probably not because, IMHO, Microsoft only made 2007 prettier NOT more
functional.
I've written a macro below to get at the Form Filename, Form name and Form
Caption for you.
HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

'/=============================================/
Public Sub ListFormInfo()
Dim i As Integer, x As Integer
Dim iVbProjectsCount As Integer, iVBCompCount As Integer
Dim iRow As Long
Dim strNewSheet As String
Dim strVBProjectName As String
Dim strFilename As String
Dim strModuleName As String

On Error GoTo err_Sub

'Note: Types of VBComponents
'vbext_ct_StdModule = 1 = Standard Module
'vbext_ct_ClassModule = 2 = Class Module
'vbext_ct_MSForm = 3 = Microsoft Form
'vbext_ct_ActiveXDesigner = 11 = Active X Designer
'vbext_ct_Document = 100 = Document Module
' - worksheets / Graphs / etc

'check for an active workbook
'if no workbooks open, create one
If ActiveWorkbook Is Nothing Then
Workbooks.Add
End If

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move After:=Worksheets(Worksheets.Count)
strNewSheet = Worksheets(Worksheets.Count).Name

Worksheets(strNewSheet).Cells(1, 1) = "Filename"
Worksheets(strNewSheet).Cells(1, 2) = "Form Name"
Worksheets(strNewSheet).Cells(1, 3) = "Form Caption"

i = 0
iRow = 1

iVbProjectsCount = Application.VBE.VBProjects.Count

For i = 1 To iVbProjectsCount
strVBProjectName = Application.VBE.VBProjects(i).Filename
If Application.VBE.VBProjects(i).Protection = 0 Then
iVBCompCount = _
Application.VBE.VBProjects(i).VBComponents.Count

'Find the Modules and then process
For x = 1 To iVBCompCount
Err.Clear
'check for a Form - 3 = Microsoft Form
If Application.VBE.VBProjects(i).VBComponents(x).Type = 3 Then
'Get File Name only from Full File Name
strFilename = _
Trim(Application.VBE.VBProjects(i).Filename)
'get module name
strModuleName = _
Application.VBE.VBProjects(i).VBComponents(x).Name
iRow = iRow + 1
Worksheets(strNewSheet).Cells(iRow, 1) = strFilename
Worksheets(strNewSheet).Cells(iRow, 2) = strModuleName
Worksheets(strNewSheet).Cells(iRow, 3) = _

Application.VBE.VBProjects(i).VBComponents(x).Prop erties(39).Value
End If
Next x
End If
Next i

Cells.Select
Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - " & _
Err.Description & " - " & Now()
Resume Next
End Sub
'/=============================================/

Hi Nick
I am actaully creating soem documentation and at the same time creating a
list of ALL controls and their captions, particularly Command Buttons and
Labels, as these will be then subject to changing dynamically through an lok
up process.

Ther first task was to get the list of all controls on all forms (a total of
over 500). Which I did not wnat to do manually!

I have utilised the controls collection and the userfroms collection (after
loading all forms). But do not seem to be able to get the captions?

Cheers


"NickHK" wrote:

Having reread your question, you get the userform to list its own details
using its .Controls collection:

Dim obj As Object

For Each obj In Me.Controls
Debug.Print obj.Name, TypeName(obj)
Next

The userform obviously has to be loaded for this to work.

NickHK

"Nigel RS" wrote in message
...
Anyone have code or know of a utility to list all userforms and controls
types and captions on a worksheet?

Thanks

Nigel,
This works for me:

Dim obj As Control

For Each obj In Me.Controls
Debug.Print obj.Name, obj.Caption
Next

You should add error handling and/or test that the control is a type that
has a .Caption first

NickHK

"Nigel RS" wrote in message
...
Hi Nick
I am actaully creating soem documentation and at the same time creating a
list of ALL controls and their captions, particularly Command Buttons and
Labels, as these will be then subject to changing dynamically through an
lok
up process.

Ther first task was to get the list of all controls on all forms (a total
of
over 500). Which I did not wnat to do manually!

I have utilised the controls collection and the userfroms collection
(after
loading all forms). But do not seem to be able to get the captions?

Cheers


"NickHK" wrote:

Having reread your question, you get the userform to list its own
details
using its .Controls collection:

Dim obj As Object

For Each obj In Me.Controls
Debug.Print obj.Name, TypeName(obj)
Next

The userform obviously has to be loaded for this to work.

NickHK

"Nigel RS" wrote in message
...
Anyone have code or know of a utility to list all userforms and
controls
types and captions on a worksheet?

Thanks

Hi Nick
Got it! I will have to adjust the code to use outside the userform in
question but it works ok now
Cheers
Nigel

"NickHK" wrote:

Nigel,
This works for me:

Dim obj As Control

For Each obj In Me.Controls
Debug.Print obj.Name, obj.Caption
Next

You should add error handling and/or test that the control is a type that
has a .Caption first

NickHK

"Nigel RS" wrote in message
...
Hi Nick
I am actaully creating soem documentation and at the same time creating a
list of ALL controls and their captions, particularly Command Buttons and
Labels, as these will be then subject to changing dynamically through an
lok
up process.

Ther first task was to get the list of all controls on all forms (a total
of
over 500). Which I did not wnat to do manually!

I have utilised the controls collection and the userfroms collection
(after
loading all forms). But do not seem to be able to get the captions?

Cheers


"NickHK" wrote:

Having reread your question, you get the userform to list its own
details
using its .Controls collection:

Dim obj As Object

For Each obj In Me.Controls
Debug.Print obj.Name, TypeName(obj)
Next

The userform obviously has to be loaded for this to work.

NickHK

"Nigel RS" wrote in message
...
Anyone have code or know of a utility to list all userforms and
controls
types and captions on a worksheet?

Thanks