New to VBA, Need help outputting a list of the TextBox names from my 1st project

[Tom Murphy]

Hi everyone, I am very new to working with VBA in Excel. Excel version is 2010 & 2013. Win 7 Pro. I have a spreadsheet to gather the data I need for my work in networking. The spreadsheet has over 55 columns to place each data element. I have created a userform to enter the data. I created a header row in the spreadsheet and named the columns related to the data they will contain. I named the TextBoxes with the same names as the columns. There are currently 55 text boxes on the userform. I am looking for a way to loop through all the textboxes on the userform and output the name of each textbox to a Notepad text file. The purpose is to check that I have the correct names as I code the userform to transfer the data to the spreadsheet. I have been able to get the code to create a .txt file in the correct folder in my documents but it is empty. Is this the best way to track the textboxes you create during a project or is there another way?

Thanks in advance for any help. I will post the code I have so far as soon as I can.

Regards,

Tom

[mikerickson]

Perhaps some version of


dim OneHeader as Variant


For each oneHeader in Array("Name", "Address", "Phone")
With Userform1.Controls("tbx" & oneHeader)
Rem do something
End With
Next

Your question is real generic and so is my response. But the main point is the use of the Controls collection and building a TextBox's name as a string index for that collection.

[SamT]

Output directly to the Spreadsheet itself. When done testing, delete the data on the Sheet.

There are numerous ways to loop thru Controls. Mike's Array method is very versatile. As your form gets updated, merely changing the name in the array updates the I/O process. Moving a Column merely means moving the name in the Array.


Placing the Control names in Column Order lets very simple code do the outputting

For i = 0 to Array items count
Cells(LastRow, i + 1) = Me.Controls(Arr(i)),Value
Next i

For reading a Row into the Controls

For i = 0 to Array items count
Me.Controls(Arr(i)),Value = Cells(SelectedRow, i + 1)
Next i

Clearing the Controls for the next input uses the same array

For i = 0 to Array items count
Set Ctrl = Me.Controls(Arr(i))
If TypeName(Ctrl) = "Textbox" Then Ctrl.Value = ""
If TypeName(Ctrl) = "Listtbox" Then Ctrl.TopIndex = -1
Next i

[Tom Murphy]

Mike & Sam,

Thank you for your responses. I actually found a bit of code on the stackoverflow forums. The code worked perfectly to give me a list of all the "objects" with names that are on my userform. The code outputs to a separate worksheet in the workbook. I didn't realize that there are 90 different textboxes until I saw the list.


Sub ListControls()
Dim lCntr As Long
Dim aCtrls() As Variant
Dim ctlLoop As MSForms.Control


'Change UserForm Name In The Next Line
For Each ctlLoop In MyUserForm.Controls
lCntr = lCntr + 1: Redim Preserve aCtrls(1 To lCntr)

'Gets Type and name of Control
aCtrls(lCntr) = TypeName(ctlLoop)&":"&ctlLoop.Name
Next ctlLoop

'Change Worksheet Name In The Next Line
Worksheets("YrSheetName").Range("A1").Resize(UBound(aCtrls)).Value = Application.Transpose(aCtrls)

End Sub


This worked perfectly, outputting all controls to a manually created sheet. Make sure to read comments and make changes required for individual projects.
Thanks to the folks at Ozgrid who answered this question many moons ago. Lesson: keep trying different words in Google as long as you have options.

[Chikemartin]

Mike & Sam,

Thank you for your responses. I actually found a bit of code on the stackoverflow forums. The code worked perfectly to give me a list of all the "objects" with names that are on my userform. The code outputs to a separate worksheet in the workbook. I didn't realize that there are 90 different textboxes until I saw the list.


Sub ListControls()
Dim lCntr As Long
Dim aCtrls() As Variant
Dim ctlLoop As MSForms.Control


'Change UserForm Name In The Next Line
For Each ctlLoop In MyUserForm.Controls
lCntr = lCntr + 1: Redim Preserve aCtrls(1 To lCntr)

'Gets Type and name of Control
aCtrls(lCntr) = TypeName(ctlLoop)&":"&ctlLoop.Name
Next ctlLoop

'Change Worksheet Name In The Next Line
Worksheets("YrSheetName").Range("A1").Resize(UBound(aCtrls)).Value = Application.Transpose(aCtrls)

End Sub


This worked perfectly, outputting all controls to a manually created sheet. Make sure to read comments and make changes required for individual projects.
Thanks to the folks at Ozgrid who answered this question many moons ago. Lesson: keep trying different words in Google as long as you have options.

[Chikemartin]

This works perfectly:yes