CODEWITHSUNDEEP
vba
methuselah
methuselah

Reputation: 13206

Resetting form in VBA

I have a VBA form with a variety of selection options including drop downs, text fields, checkboxes and radios.

I just wanted to know about the best way to clear all these fields with a button press. A friend of mine has tried to help by emailing me the code below but unfortunately it doesn't work, I have checked the variable names.

Any advice on how I can improve it?

Thanks in advance.

Private Sub btnReset_Click()

Unload Me
UserForm.Show

End Sub

Here is the other code for the userform.

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

Upvotes: 9

Views: 106756

Answers (6)

Festus Abiatar
Festus Abiatar

Reputation: 81

I had a similar issue, and I solved it with the use of ESC key.

Private Sub cmdReset_Click()
SendKeys ("{ESC}")
End Sub

Upvotes: 0

Erik I
Erik I

Reputation: 35

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

ctl.Value = ctl.DefaultValue

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

Upvotes: 2

dRay
dRay

Reputation: 21

Microsoft has this documented fairly well now for latest version of Access. It looks like some of the answers above refer to older versions. The code below is working in my app perfectly. I only included the control types I need but you can find documentation for any others in the Microsoft links below.

Ref: 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 for the combo box you have to set the focus there before setting the value. Ref: https://msdn.microsoft.com/en-us/vba/access-vba/articles/combobox-seltext-property-access

Upvotes: 2

S. Stoyanov
S. Stoyanov

Reputation: 27

You may try this:

Private Sub btnReset_Click()

    Call UserForm_Initialize

End Sub

Upvotes: 1

Johan G
Johan G

Reputation: 427

I know this question is almost 2 years old BUT I was looking for an answer like this. However, I am using Access 2010 and discovered the function did not work entirely as expected:

  • ctl can be Dim-ed simply as Control
  • For a textbox, the ctl.Text property can only be assigned to if the control has focus (use ctl.Value instead)
  • If an OptionButton is part of an OptionGroup it can not be assigned a value

So with these issues in mind, 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

Upvotes: 8

Dick Kusleika
Dick Kusleika

Reputation: 33145

I think when it hits the Unload Me line, code execution stops and that's why it's not working for you. Here's a generic event procedure to reset all (most) of the controls on the 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 doesn't repopulate the ComboBoxes and ListBoxes, just clears the selection, which is what I assume you want.

Upvotes: 20

Related Questions