Dump Utility for User form Application
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,
|