Reset form in VBA

Question

I have a VBA form with various choices, including drop-down lists, text fields, check boxes, and radio stations.

I just wanted to learn about the best way to clear all of these fields with the click of a button. My friend tried to help by sending me the code below, but unfortunately it does not work, I checked the variable names.

Any tips on how I can improve it?

Thanks in advance.

Private Sub btnReset_Click() Unload Me UserForm.Show End Sub 

Here is another code for a custom form.

 Dim DeptCode 'Holds department code Private Sub UserForm_Initialize() Dim c_deptCode As Range Dim c_deptName As Range Dim deptCodes As Variant Dim deptNames As Variant Dim ws_dept As Worksheet Set ws_dept = Worksheets("lookupDept") ' Assign each range to an array containing the values deptCodes = Choose(1, ws_dept.Range("deptCode")) deptNames = Choose(1, ws_dept.Range("deptName")) For i = 1 To ws_dept.Range("deptCode").Rows.Count ' Create the combined name (code + space + name) CombinedName = deptCodes(i, 1) & " - " & deptNames(i, 1) cbo_deptCode.AddItem CombinedName Next i End Sub 

Answer 1

I think that when it falls into the line "Unload me", the execution of the code stops and why it does not work for you. Here's the general event procedure for resetting all (most) controls on a form.

 Private Sub cmdReset_Click() Dim ctl As MSForms.Control For Each ctl In Me.Controls Select Case TypeName(ctl) Case "TextBox" ctl.Text = "" Case "CheckBox", "OptionButton", "ToggleButton" ctl.Value = False Case "ComboBox", "ListBox" ctl.ListIndex = -1 End Select Next ctl End Sub 

It does not overwrite ComboBoxes and ListBoxes, it just clears the selection that I believe you need.

Answer 2

I know that this question is almost 2 years old, BUT I was looking for such an answer. However, I am using Access 2010 and found that the function does not work fully as expected:

• ctl can be dim-ed just like control
• For a text field, the ctl.Text property can only be assigned if the control has focus (use ctl.Value instead)
• If an OptionButton is part of an OptionGroup, it cannot be assigned a value.

So, given these problems, here is my rewritten function:

 Private Sub resetForm() Dim ctl As Control ' Removed MSForms. For Each ctl In Me.Controls Select Case TypeName(ctl) Case "TextBox" ctl.value = "" Case "CheckBox", "ToggleButton" ' Removed OptionButton ctl.value = False Case "OptionGroup" ' Add OptionGroup ctl = Null Case "OptionButton" ' Add OptionButton ' Do not reset an optionbutton if it is part of an OptionGroup If TypeName(ctl.Parent) <> "OptionGroup" Then ctl.value = False Case "ComboBox", "ListBox" ctl.ListIndex = -1 End Select Next ctl End Sub 

Answer 3

You can try the following:

 Private Sub btnReset_Click() Call UserForm_Initialize End Sub 

Answer 4

Microsoft has already documented this version fairly well for the latest version of Access. Some of the answers above seem to be related to older versions. The code below works fine in my application. I just included the types of controls I need, but you can find documentation for any other Microsoft links below.

Link: https://msdn.microsoft.com/en-us/vba/access-vba/articles/textbox-controltype-property-access?f=255&MSPPError=-2147217396

 Dim ctl As Control For Each ctl In Me.Controls With ctl Select Case .ControlType Case acTextBox .Value = "" Case acCheckBox .Value = False Case acComboBox .SetFocus .SelText = "" Case acListBox .Value = Null End Select End With Next ctl 

Note that in the combo box, you must set the focus there before setting the value. Link: https://msdn.microsoft.com/en-us/vba/access-vba/articles/combobox-seltext-property-access

Answer 5

Adding to the most recent answer - if some of your controls have default values, you can use

ctl.Value = ctl.DefaultValue

Which works for me, at least for flags and combo boxes.