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,

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,

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,

Bob,
Thanks for your help on this "project";
Sorry for the "back and forth" action,,
But you helped me immensely..

Haven't totally utilized you last suggestion which
reports on ALL userforms (cause I didn't know
what to do with the lines:)

#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

appearing before the normal code.
Can you specify (what to do with it - with explaination)..
Jim May


"Bob Phillips" wrote in message
...
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,

Hi Jim,

The extra code that I gave you uses the Microsoft Visual Basic For
Application Extensibility library. The code uses some constants in that
library, so you can set a reference to that library in the VBIDE, or you can
use late binding where you replace the constants with the actual value. What
I do is add code similar to the lines that you mention that do conditional
compilation, #If Not EarlyBound Then, and creates my own constants with the
same names and the appropriate values.

What this all means is that by including this code at the start of the
module, after any Option declarations but before any procedures, I can
either have my code late binding by doing nothing else, or I can have my
code early binding, by adding a reference to that library and by adding
another line before these lines of

#Const EarlyBound = True

To be absolutely factual, the code should read

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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

I can then just flip the constant as required
--

HTH

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


"Jim May" wrote in message
news:7mT8f.31495$OM4.7158@dukeread06...
Bob,
Thanks for your help on this "project";
Sorry for the "back and forth" action,,
But you helped me immensely..

Haven't totally utilized you last suggestion which
reports on ALL userforms (cause I didn't know
what to do with the lines:)

#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

appearing before the normal code.
Can you specify (what to do with it - with explaination)..
Jim May

Embarrisingly, I just this morning tried the ALL Uforms
dump, yet got compile error @ line:
(Sub or Function not Defined)
myControls ovbmod.Name, sh, i << with "myControls" being highlighted

What can be done (to proceed)?
Jim

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

The extra code that I gave you uses the Microsoft Visual Basic For
Application Extensibility library. The code uses some constants in that
library, so you can set a reference to that library in the VBIDE, or you
can
use late binding where you replace the constants with the actual value.
What
I do is add code similar to the lines that you mention that do conditional
compilation, #If Not EarlyBound Then, and creates my own constants with
the
same names and the appropriate values.

What this all means is that by including this code at the start of the
module, after any Option declarations but before any procedures, I can
either have my code late binding by doing nothing else, or I can have my
code early binding, by adding a reference to that library and by adding
another line before these lines of

#Const EarlyBound = True

To be absolutely factual, the code should read

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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

I can then just flip the constant as required
--

HTH

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


"Jim May" wrote in message
news:7mT8f.31495$OM4.7158@dukeread06...
Bob,
Thanks for your help on this "project";
Sorry for the "back and forth" action,,
But you helped me immensely..

Haven't totally utilized you last suggestion which
reports on ALL userforms (cause I didn't know
what to do with the lines:)

#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

appearing before the normal code.
Can you specify (what to do with it - with explaination)..
Jim May

Jim,

It sounds that the myControls sub and the call are out of sync. Can you post
all of the code.

--

HTH

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


"Jim May" wrote in message
news:RC49f.34200$OM4.4631@dukeread06...
Embarrisingly, I just this morning tried the ALL Uforms
dump, yet got compile error @ line:
(Sub or Function not Defined)
myControls ovbmod.Name, sh, i << with "myControls" being highlighted

What can be done (to proceed)?
Jim

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

The extra code that I gave you uses the Microsoft Visual Basic For
Application Extensibility library. The code uses some constants in that
library, so you can set a reference to that library in the VBIDE, or you
can
use late binding where you replace the constants with the actual value.
What
I do is add code similar to the lines that you mention that do
conditional
compilation, #If Not EarlyBound Then, and creates my own constants with
the
same names and the appropriate values.

What this all means is that by including this code at the start of the
module, after any Option declarations but before any procedures, I can
either have my code late binding by doing nothing else, or I can have my
code early binding, by adding a reference to that library and by adding
another line before these lines of

#Const EarlyBound = True

To be absolutely factual, the code should read

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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

I can then just flip the constant as required
--

HTH

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


"Jim May" wrote in message
news:7mT8f.31495$OM4.7158@dukeread06...
Bob,
Thanks for your help on this "project";
Sorry for the "back and forth" action,,
But you helped me immensely..

Haven't totally utilized you last suggestion which
reports on ALL userforms (cause I didn't know
what to do with the lines:)

#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

appearing before the normal code.
Can you specify (what to do with it - with explaination)..
Jim May

Here you go (from the top of Module1):

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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 = "TabIndex"
.Range("F4").Value = "ControlValue"
.Range("A4:F4").Font.Bold = True
.Range("A4:F4").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

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

It sounds that the myControls sub and the call are out of sync. Can you
post
all of the code.

--

HTH

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


"Jim May" wrote in message
news:RC49f.34200$OM4.4631@dukeread06...
Embarrisingly, I just this morning tried the ALL Uforms
dump, yet got compile error @ line:
(Sub or Function not Defined)
myControls ovbmod.Name, sh, i << with "myControls" being highlighted

What can be done (to proceed)?
Jim

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

The extra code that I gave you uses the Microsoft Visual Basic For
Application Extensibility library. The code uses some constants in that
library, so you can set a reference to that library in the VBIDE, or
you
can
use late binding where you replace the constants with the actual value.
What
I do is add code similar to the lines that you mention that do
conditional
compilation, #If Not EarlyBound Then, and creates my own constants with
the
same names and the appropriate values.

What this all means is that by including this code at the start of the
module, after any Option declarations but before any procedures, I can
either have my code late binding by doing nothing else, or I can have
my
code early binding, by adding a reference to that library and by adding
another line before these lines of

#Const EarlyBound = True

To be absolutely factual, the code should read

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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

I can then just flip the constant as required
--

HTH

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


"Jim May" wrote in message
news:7mT8f.31495$OM4.7158@dukeread06...
Bob,
Thanks for your help on this "project";
Sorry for the "back and forth" action,,
But you helped me immensely..

Haven't totally utilized you last suggestion which
reports on ALL userforms (cause I didn't know
what to do with the lines:)

#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

appearing before the normal code.
Can you specify (what to do with it - with explaination)..
Jim May

Jim,

It's missing the sub altogether. Just tagit at the end

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:9z59f.34991$OM4.25401@dukeread06...
Here you go (from the top of Module1):

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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 = "TabIndex"
.Range("F4").Value = "ControlValue"
.Range("A4:F4").Font.Bold = True
.Range("A4:F4").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

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

It sounds that the myControls sub and the call are out of sync. Can you
post
all of the code.

--

HTH

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


"Jim May" wrote in message
news:RC49f.34200$OM4.4631@dukeread06...
Embarrisingly, I just this morning tried the ALL Uforms
dump, yet got compile error @ line:
(Sub or Function not Defined)
myControls ovbmod.Name, sh, i << with "myControls" being highlighted

What can be done (to proceed)?
Jim

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

The extra code that I gave you uses the Microsoft Visual Basic For
Application Extensibility library. The code uses some constants in
that
library, so you can set a reference to that library in the VBIDE, or
you
can
use late binding where you replace the constants with the actual
value.
What
I do is add code similar to the lines that you mention that do
conditional
compilation, #If Not EarlyBound Then, and creates my own constants
with
the
same names and the appropriate values.

What this all means is that by including this code at the start of
the
module, after any Option declarations but before any procedures, I
can
either have my code late binding by doing nothing else, or I can have
my
code early binding, by adding a reference to that library and by
adding
another line before these lines of

#Const EarlyBound = True

To be absolutely factual, the code should read

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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

I can then just flip the constant as required
--

HTH

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


"Jim May" wrote in message
news:7mT8f.31495$OM4.7158@dukeread06...
Bob,
Thanks for your help on this "project";
Sorry for the "back and forth" action,,
But you helped me immensely..

Haven't totally utilized you last suggestion which
reports on ALL userforms (cause I didn't know
what to do with the lines:)

#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

appearing before the normal code.
Can you specify (what to do with it - with explaination)..
Jim May

Bob, thanks..
I had replace the original (2) Subs from your 1st mailing
with the (1) Sub from your 2nd mailing (not knowing you
only had the 1st Sub in your 2nd mailing)... Sorry,,
It works like a charm!!
Jim

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

It's missing the sub altogether. Just tagit at the end

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:9z59f.34991$OM4.25401@dukeread06...
Here you go (from the top of Module1):

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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 = "TabIndex"
.Range("F4").Value = "ControlValue"
.Range("A4:F4").Font.Bold = True
.Range("A4:F4").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

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

It sounds that the myControls sub and the call are out of sync. Can you
post
all of the code.

--

HTH

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


"Jim May" wrote in message
news:RC49f.34200$OM4.4631@dukeread06...
Embarrisingly, I just this morning tried the ALL Uforms
dump, yet got compile error @ line:
(Sub or Function not Defined)
myControls ovbmod.Name, sh, i << with "myControls" being highlighted

What can be done (to proceed)?
Jim

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

The extra code that I gave you uses the Microsoft Visual Basic For
Application Extensibility library. The code uses some constants in
that
library, so you can set a reference to that library in the VBIDE, or
you
can
use late binding where you replace the constants with the actual
value.
What
I do is add code similar to the lines that you mention that do
conditional
compilation, #If Not EarlyBound Then, and creates my own constants
with
the
same names and the appropriate values.

What this all means is that by including this code at the start of
the
module, after any Option declarations but before any procedures, I
can
either have my code late binding by doing nothing else, or I can
have
my
code early binding, by adding a reference to that library and by
adding
another line before these lines of

#Const EarlyBound = True

To be absolutely factual, the code should read

#Const EarlyBound = False

#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()
'----------------------------------------------------------------
#If Not EarlyBound Then
Dim ovbmod As Object
#Else
Dim ovbmod As VBIDE.VBComponent
#End If
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

I can then just flip the constant as required
--

HTH

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


"Jim May" wrote in message
news:7mT8f.31495$OM4.7158@dukeread06...
Bob,
Thanks for your help on this "project";
Sorry for the "back and forth" action,,
But you helped me immensely..

Haven't totally utilized you last suggestion which
reports on ALL userforms (cause I didn't know
what to do with the lines:)

#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

appearing before the normal code.
Can you specify (what to do with it - with explaination)..
Jim May