Output GUI Components' Names of Form

[sheeeng]

Hi all :hi: ,

I have a problem here. Can Excel VBA Userform GUI components be recorded to a Word/Excel document? Please advise. :doh:

eg. Userform1 has Label1, ListBox1, and TextBox2. Then, all the name of GUI components are recorded in a Word or Excel format.

Thanks.

[Jacob Hilderbrand]

This will put the names and captions (if applicable) in a worksheet.



Option Explicit

Private Sub UserForm_Click()
Dim i As Long
Dim n As Long
n = Me.Controls.Count
On Error Resume Next
For i = 1 To n
Range("A" & i).Value = Me.Controls(i).Name
Range("B" & i).Value = Me.Controls(i).Caption
Next i
On Error GoTo 0
Unload Me
End Sub

[sheeeng]

Thanks, DRJ. It works!!

What if I need to output to a Word file? Any suggestions?

[Jacob Hilderbrand]

Try this. First set a reference to the Word Object Library.

In the VBE, Tools | References.
Browse for Microsoft Word #.# Object Library and add the reference. (Where #.# will be a number dependant on your Office version ~ 9.0, 10.0, 11.0).



Option Explicit

Private Sub UserForm_Click()
Dim i As Long
Dim n As Long
Dim AppWrd As New Word.Application
Dim Doc As Document
Set Doc = AppWrd.Documents.Add
n = Me.Controls.Count
On Error Resume Next
With AppWrd.Selection
For i = 1 To n
.TypeText Text:=Me.Controls(i).Name
.TypeText Text:=vbTab
.TypeText Text:=Me.Controls(i).Caption
.TypeParagraph
Next i
End With
On Error GoTo 0
AppWrd.Visible = True
Set Doc = Nothing
Set AppWrd = Nothing
Unload Me
End Sub

[sheeeng]

Thanks. That's Great!!