Problem: you have a userform and need a way to quickly reset all of its controls. The code needs to be self-scaling, so you don't have to remember to update it every time you add or remove a control from your form.
Solution: Run this code, passing it a copy of your Form object:
Private Function ClearControls(frm As MSForms.UserForm) As Boolean
' if any error occurs, just exit
On Error GoTo ExitProc
' clear out all controls
Dim ctl As Control
' loop through controls, figure out type and
' use appropriate method to clear it
For Each ctl In frm.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox"
ctl.Value = False
Case "ComboBox"
ctl.Value = ""
Case "ListBox"
ctl.Value = ""
Case "OptionButton"
ctl.Value = False
Case "ToggleButton"
ctl.Value = False
End Select
Next ctl
' if we made it this far, assume success
ClearControls = True
ExitProc:
Set ctl = Nothing
End Function
The code should be placed in the form's class module (i.e. double click on the form itself in Design mode to view the code module behind it), and you call the code as follows:
Dim success As Boolean success = ClearControls(UserForm1) ' or whatever the code name of your form is ' or success = ClearControls(Me) If success Then MsgBox "All controls clear" End If
The code loops through the most common controls you would need to reset.
- TextBox – clears out any text
- CheckBox – unchecks it
- ComboBox – clears currently selected value, but does not remove any entries
- ListBox – de-selects currently selected value (if applicable), does not remove any entries
- OptionButton – de-selects option
- ToggleButton – un-toggles button
If anyone can think of any more controls, I'll be happy to update the code. I simply create a Reset button on my forms and put this code behind it, and forget about it.
I created a workbook with a sample form and the code above, along with additional code that loads each input field with random data.
About JP
Follow Me