Loop through controls on a userform

[shaneherron]

Hey guys, I hope someone can help me with this one cuz I'm stumped.

I have a userform that contains several textboxes, checkboxes, and comboboxes. There are also some command buttons, one of which says clear all. What I want to do is loop through the controls and clear the contents or change the value to false, depending on the type. I can do this by type the name.value = "", but there are alot of controls. If possible, a loop would be much more effecient.

 

[shaneherron]

Wow, Thanks Norie, that did the job.

I have never seen a select case done like that. I'll remember that.

 

[Colin Legg]

Hi Shane,

Just so you know, your approach of using an If...ElseIf...End If block is absolutely fine. The problem is the execution:

    Dim ctrl As MSForms.Control
 
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.CheckBox Then
            ctrl.Value = False
        ElseIf TypeOf crtl Is MSForms.TextBox Then
            ctrl.Value = ""
        ElseIf TypeOf ctro Is MSForms.ComboBox Then
            ctrl.Value = ""
        End If
 
    Next ctrl

You can see the typos highlighted in red.

To avoid this sort of thing you should add an Option Explicit statement to the top of your code modules. With an Option Explicit statement in place the code will not compile with these typos and they will be quickly highlighted to you so you can quickly identify and correct them.

Going forward you can have the Option Explicit statement automatically inserted for you in new code modules if, in the VBE, you go to Tools | Options | Editor tab and tick the "Require Variable Declaration" checkbox. There are other benefits to this too, such as your code in general will run more efficiently. I strongly recommend this.

The only other consideration you should give is the one we already mentioned: your code will also clear any optionbuttons (or togglebuttons) because TypeOf ctrl Is MSForms.CheckBox "can't tell the difference" between a Checkbox, an OptionButton and a ToggleButton.

Hope that helps...

 

[Norie]

Wow, Thanks Norie, that did the job.

I have never seen a select case done like that. I'll remember that.

I actually wouldn't recommend using Select Case like that on a regular basis.

I know it works but it's not 'standard' if you like, and can be rather confusing.

 

[Colin Legg]

Normally TypeOf() is preferrable, but I think here you have to use VBA.TypeName() to distinguish for a MSForms.CheckBox.

Of course, as has just been pointed out to me, one could use

            If TypeOf ctl Is MSForms.CheckBox Then
                If Not TypeOf ctl Is MSForms.ToggleButton Then
                    If Not TypeOf ctl Is MSForms.OptionButton Then
                        'we know we have a checkbox

 

[majid_mx4]

Try this.

Dim ctrl As MSForms.Control
 
For Each ctrl In Me.Controls
    Select Case True
        Case TypeOf ctrl Is MSForms.CheckBox
            ctrl.Value = False
        Case TypeOf ctrl Is MSForms.TextBox
            ctrl.Value = ""
        Case TypeOf ctrl Is MSForms.ComboBox
            ctrl.Value = ""
    End Select
    
Next ctrl

with many thanks of Norie , also you can use the below code that belong to excellency PGC (above Post) with little bit change as below:

Private Sub CommandButton1_Click()
Dim ctrl As MSForms.Control
 
For Each ctrl In UserForm1.Controls
   
    On Error Resume Next
    If ctrl.Value <> "" Then
 
 ctrl.Value = ""
    End If
Next ctrl

End Sub

with thank S.M.Mir (Iran-Tehran)