Is there an excel utility that will "dump" to a sheet (with headings) the
various Controls (name, caption, etc...) currently in use in a Project's
Forms folder? If so, where would I find such? I searched Google without any
luck.
TIA,

Here's some code

Dim ctl As Object
Dim i As Long

On Error Resume Next
For Each ctl In UserForm1.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with headings) the
various Controls (name, caption, etc...) currently in use in a Project's
Forms folder? If so, where would I find such? I searched Google without
any
luck.
TIA,

Bob, Thanks..
Would I put this in a standard module (is personal.xls an option?)? Where
at present will this output go? Don't
I need to reference in a sheetname, like say "UFControlReport" ?
Jim

"Bob Phillips" wrote in message
...
Here's some code

Dim ctl As Object
Dim i As Long

On Error Resume Next
For Each ctl In UserForm1.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with headings) the
various Controls (name, caption, etc...) currently in use in a Project's
Forms folder? If so, where would I find such? I searched Google without
any
luck.
TIA,

Never mind Bob, I got it;
Appreciate the code.
Jim

"Jim May" wrote in message
news:clJ8f.28500$OM4.5891@dukeread06...
Bob, Thanks..
Would I put this in a standard module (is personal.xls an option?)?
Where at present will this output go? Don't
I need to reference in a sheetname, like say "UFControlReport" ?
Jim

"Bob Phillips" wrote in message
...
Here's some code

Dim ctl As Object
Dim i As Long

On Error Resume Next
For Each ctl In UserForm1.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with headings)
the
various Controls (name, caption, etc...) currently in use in a Project's
Forms folder? If so, where would I find such? I searched Google without
any
luck.
TIA,

Bob,
I have multiple Userforms in my app;
I have inserted a line in your code

Dim MyFormName as String
MyFormName = Inputbox("Enter Form Name")

On Stepping-thru (even of inception) I get
Compile error
Invalid Qualifier

With MyFormName highlite within the statement:
For Each ctl In MyFormName.Controls

What have I done wrong? Can it be fixed? How?
TIA,
Jim


"Bob Phillips" wrote in message
...
Here's some code

Dim ctl As Object
Dim i As Long

On Error Resume Next
For Each ctl In UserForm1.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with headings) the
various Controls (name, caption, etc...) currently in use in a Project's
Forms folder? If so, where would I find such? I searched Google without
any
luck.
TIA,

Jim,

You cannot reference a userform in that way, as MyFormName is just a string,
not a userform object.

Unfortunately, you can't just use

For Each ctl In Userforms(MyFormName).Controls

either, as the userforms collection only holds loaded forms, not all forms.

What you have to do is force a load based on string name, then reference
that object, like so


Dim MyFormName As String
Dim oUserForm As Object

MyFormName = InputBox("Enter Form Name")
On Error Resume Next
Set oUserForm = UserForms.Add(MyFormName)
On Error GoTo 0
For Each ctl In oUserForm.Controls

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:XOK8f.28506$OM4.10173@dukeread06...
Bob,
I have multiple Userforms in my app;
I have inserted a line in your code

Dim MyFormName as String
MyFormName = Inputbox("Enter Form Name")

On Stepping-thru (even of inception) I get
Compile error
Invalid Qualifier

With MyFormName highlite within the statement:
For Each ctl In MyFormName.Controls

What have I done wrong? Can it be fixed? How?
TIA,
Jim


"Bob Phillips" wrote in message
...
Here's some code

Dim ctl As Object
Dim i As Long

On Error Resume Next
For Each ctl In UserForm1.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with headings)
the
various Controls (name, caption, etc...) currently in use in a
Project's
Forms folder? If so, where would I find such? I searched Google
without
any
luck.
TIA,

My Lastest Code:

Producing R/T 438
Object doesn't support this
property or method.

Bombing on 1st line of output column D .value
so I commented out .cell value, then
then it later bombed on 3rd line of output Column C.caption
Common element - these fields are null/blank ... hummm

Sub ShowMyControls()
Dim ctl As Object
Dim i As Long
Dim MyFormName As String
Dim oUserForm As Object
On Error Resume Next
Worksheets.Add
ActiveSheet.Name = "Report of Controls"
Range("A4").Value = "TypeControl"
Range("B4").Value = "ControlName"
Range("C4").Value = "ControlCaption"
Range("D4").Value = "ControlValue"
Range("A4:D4").Font.Bold = True
Range("A4:D4").HorizontalAlignment = xlCenter
Range("A5").Select
MyFormName = InputBox("Enter Form Name Desired")
On Error Resume Next
Set oUserForm = UserForms.Add(MyFormName)
On Error GoTo 0
i = 4
For Each ctl In oUserForm.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl
Sheets("Report of Controls").Columns("A:D").AutoFit
End Sub






"Bob Phillips" wrote in message
...
Jim,

You cannot reference a userform in that way, as MyFormName is just a
string,
not a userform object.

Unfortunately, you can't just use

For Each ctl In Userforms(MyFormName).Controls

either, as the userforms collection only holds loaded forms, not all
forms.

What you have to do is force a load based on string name, then reference
that object, like so


Dim MyFormName As String
Dim oUserForm As Object

MyFormName = InputBox("Enter Form Name")
On Error Resume Next
Set oUserForm = UserForms.Add(MyFormName)
On Error GoTo 0
For Each ctl In oUserForm.Controls

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:XOK8f.28506$OM4.10173@dukeread06...
Bob,
I have multiple Userforms in my app;
I have inserted a line in your code

Dim MyFormName as String
MyFormName = Inputbox("Enter Form Name")

On Stepping-thru (even of inception) I get
Compile error
Invalid Qualifier

With MyFormName highlite within the statement:
For Each ctl In MyFormName.Controls

What have I done wrong? Can it be fixed? How?
TIA,
Jim


"Bob Phillips" wrote in message
...
Here's some code

Dim ctl As Object
Dim i As Long

On Error Resume Next
For Each ctl In UserForm1.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with headings)
the
various Controls (name, caption, etc...) currently in use in a
Project's
Forms folder? If so, where would I find such? I searched Google
without
any
luck.
TIA,

Jim,

That was my fault. I reset the error handler in the code (OnError Goto 0)
when they are still ignorable errors around. Just remove it.

You might also be interested in this variation, which gets all userforms in
the project and details them, no need to prompt for it

#If Not EarlyBound Then
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3
#End If

'----------------------------------------------------------------
Sub ShowMyControls()
'----------------------------------------------------------------
Dim oVBMod As Object
Dim i As Long
Dim sh As Worksheet

On Error Resume Next
Set sh = Worksheets("Report of Controls")
If sh Is Nothing Then
Set sh = Worksheets.Add
sh.Name = "Report of Controls"
Else
sh.Cells.ClearContents
sh.Activate
End If
With sh
.Range("A4").Value = "FormName"
.Range("B4").Value = "TypeControl"
.Range("C4").Value = "ControlName"
.Range("D4").Value = "ControlCaption"
.Range("E4").Value = "ControlValue"
.Range("A4:E4").Font.Bold = True
.Range("A4:E4").HorizontalAlignment = xlCenter
.Range("A5").Select
End With
i = 4

With ActiveWorkbook.VBProject
For Each oVBMod In .VBComponents
Select Case oVBMod.Type
Case vbext_ct_StdModule:
Case vbext_ct_MSForm:
i = i + 1
myControls oVBMod.Name, sh, i
Case vbext_ct_ClassModule:
End Select
Next oVBMod
End With

End Sub

Private Sub myControls(ByVal FormName As String, sh As Worksheet, _
ByRef i As Long)
Dim ctl As Object
Dim oUserForm As Object

On Error Resume Next
Set oUserForm = UserForms.Add(FormName)
If Not oUserForm Is Nothing Then
sh.Cells(i, "a").Value = FormName
For Each ctl In oUserForm.Controls
i = i + 1
sh.Cells(i, "B").Value = TypeName(ctl)
sh.Cells(i, "C").Value = ctl.Name
sh.Cells(i, "D").Value = ctl.Caption
sh.Cells(i, "E").Value = ctl.Value
Next ctl
End If
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:g%L8f.29460$OM4.1577@dukeread06...
My Lastest Code:

Producing R/T 438
Object doesn't support this
property or method.

Bombing on 1st line of output column D .value
so I commented out .cell value, then
then it later bombed on 3rd line of output Column C.caption
Common element - these fields are null/blank ... hummm

Sub ShowMyControls()
Dim ctl As Object
Dim i As Long
Dim MyFormName As String
Dim oUserForm As Object
On Error Resume Next
Worksheets.Add
ActiveSheet.Name = "Report of Controls"
Range("A4").Value = "TypeControl"
Range("B4").Value = "ControlName"
Range("C4").Value = "ControlCaption"
Range("D4").Value = "ControlValue"
Range("A4:D4").Font.Bold = True
Range("A4:D4").HorizontalAlignment = xlCenter
Range("A5").Select
MyFormName = InputBox("Enter Form Name Desired")
On Error Resume Next
Set oUserForm = UserForms.Add(MyFormName)
On Error GoTo 0
i = 4
For Each ctl In oUserForm.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl
Sheets("Report of Controls").Columns("A:D").AutoFit
End Sub






"Bob Phillips" wrote in message
...
Jim,

You cannot reference a userform in that way, as MyFormName is just a
string,
not a userform object.

Unfortunately, you can't just use

For Each ctl In Userforms(MyFormName).Controls

either, as the userforms collection only holds loaded forms, not all
forms.

What you have to do is force a load based on string name, then reference
that object, like so


Dim MyFormName As String
Dim oUserForm As Object

MyFormName = InputBox("Enter Form Name")
On Error Resume Next
Set oUserForm = UserForms.Add(MyFormName)
On Error GoTo 0
For Each ctl In oUserForm.Controls

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:XOK8f.28506$OM4.10173@dukeread06...
Bob,
I have multiple Userforms in my app;
I have inserted a line in your code

Dim MyFormName as String
MyFormName = Inputbox("Enter Form Name")

On Stepping-thru (even of inception) I get
Compile error
Invalid Qualifier

With MyFormName highlite within the statement:
For Each ctl In MyFormName.Controls

What have I done wrong? Can it be fixed? How?
TIA,
Jim


"Bob Phillips" wrote in message
...
Here's some code

Dim ctl As Object
Dim i As Long

On Error Resume Next
For Each ctl In UserForm1.Controls
i = i + 1
Cells(i, "A").Value = TypeName(ctl)
Cells(i, "B").Value = ctl.Name
Cells(i, "C").Value = ctl.Caption
Cells(i, "D").Value = ctl.Value
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with
headings)
the
various Controls (name, caption, etc...) currently in use in a
Project's
Forms folder? If so, where would I find such? I searched Google
without
any
luck.
TIA,

Look at the post of 21October: get all the labels of all forms in array.
That shows how to get all controls of all forms.

RBS


"Jim May" wrote in message
news:zdH8f.28498$OM4.3029@dukeread06...
Is there an excel utility that will "dump" to a sheet (with headings) the
various Controls (name, caption, etc...) currently in use in a Project's
Forms folder? If so, where would I find such? I searched Google without
any luck.
TIA,