Editing controls on a userform en masse

  • Thread starter Thread starter Shane
  • Start date Start date
S

Shane

I have a userform that has multiple pages, each page has various controls
(TextBox, ComboBox, CheckBox). Is there code that I can use to clear all of
the values in the userform?

I was thinking "For Each", but I am new to code and keep getting lost...

Any help is appreciated!
 
Shane,

The code below is untested and will need to be altered for your specific
case, but it should work. The code assumes that the MultiPage control on the
form is named "frmPgs".

Best,

Matthew Herbert

Dim Pg As Page
Dim Ctrl As MSForms.Control
'check "Tools | Reference | Microsoft Forms 2.0 Object Library" to
' use MSForms; otherwise, simply use Dim Ctrl As Control

'loop through each page in the Pages collection of the form
For Each Pg In frmPgs.Pages

'loop through each control on the page
For Each Ctrl In Pg.Controls

'get the desired control type
If TypeOf Ctrl Is MSForms.TextBox Then

'set the control value
Ctrl.Value = ""
End If
Next Ctrl
Next Pg
 
Using code like this, I get a type mismatch error... here is the code I am
using. It runs on clicking a commandbutton on the userform, but outside of
the multipage control. I want it to reset EVERY control in the userform.

Dim Ctrl As MSForms.Control
Dim Pg As Page
For Each Pg In UserForm1.MultiPage1.Pages
For Each Ctrl In Pg.Controls
If TypeOf Ctrl Is MSForms.TextBox Then
Ctrl.Value = ""
Else
If TypeOf Ctrl Is MSForms.ComboBox Then
Ctrl.Value = ""
Else
If TypeOf Ctrl Is MSForms.CheckBox Then
Ctrl.Value = False
Else
If TypeOf Ctrl Is MSForms.OptionButton Then
Ctrl.Value = False
End If
End If
End If
End If
Next Ctrl
Next Pg
 
Shane,

I threw together some quick code and got the same error. It's odd to me
that VBA isn't assign a single Page from the MultiPage to the Pg variable
(which is dimensioned as a Page). Anyhow, change the Pg data type from Page
to Object. Also, you can narrow up your nested If Statements with some
ElseIf statements (see below).

Best,

Matt

Dim objPg As Object
Dim Ctrl As MSForms.Control

For Each objPg In UserForm1.MultiPage1.Pages

For Each Ctrl In objPg.Controls

If TypeOf Ctrl Is MSForms.TextBox Then
Ctrl.Value = ""
ElseIf TypeOf Ctrl Is MSForms.ComboBox Then
Ctrl.Value = ""
ElseIf TypeOf Ctrl Is MSForms.CheckBox Then
Ctrl.Value = False
ElseIf TypeOf Ctrl Is MSForms.OptionButton Then
Ctrl.Value = False
End If

Next Ctrl
Next objPg
 
Works fantastic! Thank you so much!

Matthew Herbert said:
Shane,

I threw together some quick code and got the same error. It's odd to me
that VBA isn't assign a single Page from the MultiPage to the Pg variable
(which is dimensioned as a Page). Anyhow, change the Pg data type from Page
to Object. Also, you can narrow up your nested If Statements with some
ElseIf statements (see below).

Best,

Matt

Dim objPg As Object
Dim Ctrl As MSForms.Control

For Each objPg In UserForm1.MultiPage1.Pages

For Each Ctrl In objPg.Controls

If TypeOf Ctrl Is MSForms.TextBox Then
Ctrl.Value = ""
ElseIf TypeOf Ctrl Is MSForms.ComboBox Then
Ctrl.Value = ""
ElseIf TypeOf Ctrl Is MSForms.CheckBox Then
Ctrl.Value = False
ElseIf TypeOf Ctrl Is MSForms.OptionButton Then
Ctrl.Value = False
End If

Next Ctrl
Next objPg
Click to expand...
 
Back
Top