Loop through controls on a userform

S

shaneherron

Board Regular
Joined
Nov 3, 2009
Messages
51
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi

You can use the Controls collection of the userform, like in:

Code: 
Sub ListControls()
Dim ctrl As MSForms.Control
 
For Each ctrl In UserForm1.Controls
    MsgBox ctrl.Name
Next ctrl
End Sub
 
Upvote 0
OK, that got me closer. But I am getting a type mismatch. The debugger is pointing to the line '"For Each ctrl In Me.Controls"

Here is my code

Code: 
Private Sub Clear_Click()
Dim sure
On Error Resume Next
sure = MsgBox("YOU ARE ABOUT TO DELETE THE COMPANY INFOMATION FROM THE WRITE UP SHEET!!! ARE YOU SURE YOU WANT TO CLEAR THE FORM,", 20, "Warning!!!")

If sure = vbNo Then Exit Sub

Dim ctrl As MSForms.CheckBox
 
For Each ctrl In Me.Controls
    ctrl.Value = False
Next ctrl



End Sub
 
Upvote 0
Hi

Code: 
Dim ctrl As MSForms.CheckBox

This is wrong, ctrl is any control from the controls collection and so you have to declare it with the generic type MSForms.Control

If you are only interested in the CheckBoxes, loop through the controls and test if the control is a CheckBox.

Example:

Code: 
Sub ListChexkBoxes()
Dim ctrl As MSForms.Control
 
For Each ctrl In UserForm1.Controls
    If TypeOf ctrl Is MSForms.CheckBox Then
        MsgBox "Hi! I'm checkbox " & ctrl.Name
    End If
Next ctrl
End Sub
 
Upvote 0
What I'm trying to do is loop through the controls and set the value to all checkboxes to false, and the value to all textboxes or comboboxes to "". How do I tell it if it is a textbox, then.value = "" and if it is a checkbox then .value = false.
 
Upvote 0
Hi,

I thought this might be of interest -

Code: 
If TypeOf ctrl Is MSForms.CheckBox Then
There's a caveat when using TypeOf to check for a MSForms.CheckBox: it won't distinguish between an MSForms.CheckBox and a MSForms.OptionButton. When a control is an MSForms.OptionButton, both of these statements return True:
Code: 
If TypeOf ctrl Is MSForms.CheckBox Then
'
If TypeOf ctrl Is MSForms.OptionButton Then

I think this is because the OptionButton class inherits from the Checkbox class.

Normally TypeOf() is preferrable, but I think here you have to use VBA.TypeName() to distinguish for a MSForms.CheckBox.
 
Upvote 0
Ok I got it to clear the checkboxes, but it doesn't clear the text or comboboxes. It doesn't error out. It just doesn't clear them.

Code: 
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
 
Upvote 0
Colin Legg said:
There's a caveat when using TypeOf to check for a MSForms.CheckBox: it won't distinguish between an MSForms.CheckBox and a MSForms.OptionButton.
Click to expand...

or a ToggleButton, IIRC.
 
Upvote 0
Colin, Rory

Thank you. I sometimes use Typename and other times TypeOf but I was not aware of this behaviour of TypeOf.
 
Upvote 0
Try this.
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Get Properties is selected from Dialogs(xlDialogFontProperties) at the UserForm Level (not Worksheet)
Replies
1
Views
52
jkpieterse
jkpieterse
A
  • Question Question
Userform "Reset" - Set user focus on a particular control, contents highlighted, ready for user to overwrite
Replies
2
Views
105
Jaafar Tribak
Jaafar Tribak
N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
359
Micron
M
hicksi
  • Question Question
Ensuring a Form is Activated
Replies
1
Views
45
NdNoviceHlp
NdNoviceHlp
P
  • Solved
Clear Userform but doesn't clear CombiBox
Replies
3
Views
570
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top