Unanswered: For Each... Next loop in VBA for controls on UserForm?
I know that I can use a For Each... Next loop to cycle through the controls on an Access form, retrieving the value of each control (i.e. text in a textbox).
I've been trying to figure out a way to do the same thing with a UserForm in a VBA application that uses Excel. However, it doesn't seem like UserForm controls have the right properties available that would allow me to retrieve their value. Does anyone have a workaround or a suggestion?
Here's basically what I was hoping to do, except of course that the Name and Text properties don't seem to exist when accessed through the UserForm.Controls collection:
Dim txtValue as String
For Each Control in UserForm.Controls
If Left(Control.Name,3)="txt" Then 'To identify only TextBoxes 'based on prefix
txtValue = Control.Text
Select Case txtValue
Hi Emily, You are on the right track with this. I think the problem has to do with the state of the Userform. Remember the userform has to be active in order to access the controls and values on it. You show it using the UserForm.Show method. you can use Me.Hide in the userform code module to Hide the userform and still have access to the controls and values on the form. To close the userform, use Unload Me (in the userform class module) or Unload userformname, in a procedure or userform class module.
' First Show the user Form
' Then use an event to check controls on the form
' I connected a button to this Sub Process
For Each TB In UserForm1.Controls
If TB.Name Like "TextBox?" Then
tbCount = tbCount + 1
strTBVal = TB.Text
msgTxt = msgTxt & strTBVal & " - "
' In the project Explorer frame right-click
' the UserForm Object and Select "View Code"
' this is the code attached to the button
' to loop the controls by calling the CountTBs sub above
Private Sub CommandButton1_Click()
Thanks, Bill! I was actually already showing the UserForm, as the code was being called from a command button on it.
With your assurance that it was possible, though, I did manage to figure this out. I was thrown by the fact that if I declared the variable for the controls I was cycling through as a Control, the .Text property didn't show up in the IntelliSense help, and didn't seem to be there in the Object Browser for UserForm Controls either. However, if I ignored the help and used the .Text property anyway, lo and behold, it worked! Go figure!
the .Text property didn't show up in the IntelliSense help, and didn't seem to be there in the Object Browser for UserForm Controls either.
Not sure what intelliSense help is? Guess you are talking about the Excel on-line VB help that ships with Excel?
If you have 'Auto List Members' or auto data? checked in the VB options you should see a pick list displayed to auto-complete your statements. This would show .Text as a property of the textbox control, but auto complete only works if you are referring directly to the control, not when you are using object variables.
When I referred to IntelliSense help, I was referring to the Auto List Members function of it that you referred to.
Yes, .Text is a member of the TextBox control, but it's not a member of a plain old Control (dim x as Control, not dim x as TextBox). But it does seem to work anyway, even though it doesn't show .Text in Auto List Members.