For Each... Next loop in VBA for controls on UserForm?

EmilyG · #1 (**permalink**) 10-08-04, 13:58

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
Case...
Case...
End Select
End If
Next

Thanks!
Emily

savbill · #2 (**permalink**) 10-09-04, 10:29

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.

Code:

' First Show the user Form
Sub ShowUserForm()

    UserForm1.Show

End Sub

' Then use an event to check controls on the form
' I connected a button to this Sub Process
 
Sub CountTBs()
For Each TB In UserForm1.Controls

    If TB.Name Like "TextBox?" Then
        tbCount = tbCount + 1
        strTBVal = TB.Text
        
        msgTxt = msgTxt & strTBVal & " - "
    End If
Next

MsgBox msgTxt

End Sub

' 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()
    Call CountTBs
End Sub

.

EmilyG · #3 (**permalink**) 10-11-04, 10:25

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!

Thanks again!
Emily

savbill · #4 (**permalink**) 10-11-04, 23:26

Quote:

Originally Posted by EmilyG

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.

.

EmilyG · #5 (**permalink**) 10-22-04, 12:51

Bill,

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.

Thanks again!
Emily

Internet Services

Web Development

Digital Marketing

Consumer Tech