Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    5

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

    Thanks!
    Emily

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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
    .
    ~

    Bill

  3. #3
    Join Date
    Oct 2004
    Posts
    5

    Thanks!

    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

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    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.

    .
    ~

    Bill

  5. #5
    Join Date
    Oct 2004
    Posts
    5

    member of textbox, but not of control

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •