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
'/=============================================/