Consulting

Results 1 to 7 of 7

Thread: Reset Form Button

  1. #1
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location

    Reset Form Button

    I currently have the following attached to a button to reset a form.

    'Reset button
    Private Sub ResetBut_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 = True
                Case "ComboBox", "ListBox"
                    ctl.ListIndex = 0
            End Select
        Next ctl
    End Sub
    This works fine except for one thing - Any items that have been selected in a ListBox remain selected.

    I'm sure that
    Listbox1.ListIndex = -1
    might be what is required, but I do not know how to effectively incorporate it in my Reset Button code. Mind you, this will only deselect anything in ListBox1. What could be used to clear all ListBoxes?

    Alternatively someone might a have a better and complete option that negates using my code altogether!

    Thanks!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Can't say I have tried it, but wouldn't it just be

    'Reset button
    Private Sub ResetBut_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 = True
                Case "ComboBox", "ListBox"
                    ctl.ListIndex = -1
            End Select
        Next ctl
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Unfortunately this hasn't worked. It does nothing to the LisBox, but does remove all the preset values in the ComboBox!


    I've managed to modify my code to get the desired effect, although this only resets ListBox1.

    'Reset button
    Private Sub ResetBut_Click()
        Dim ctl         As MSForms.Control
        ListBox1.ListIndex = -1
        For Each ctl In Me.Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = ""
                Case "CheckBox", "OptionButton", "ToggleButton"
                    ctl.Value = True
                Case "ComboBox", "ListBox"
                    ctl.ListIndex = 0
            End Select
            
            Dim i       As Integer
            For i = 0 To ListBox1.ListCount
                ListBox1.Selected(i) = False
            Next
            
        Next ctl
    End Sub

  4. #4
    Save the form as a template and create new documents from it and resetting is unnecessary. However the following should work

    Private Sub ResetBut_Click()
    Dim ctl As MSForms.Control
    Dim i As Integer
        For Each ctl In Me.Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = ""
                Case "CheckBox", "OptionButton", "ToggleButton"
                    ctl.value = False
                Case "ComboBox"
                    ctl.ListIndex = -1
                Case "ListBox"
                    For i = 0 To ctl.ListCount
                        ctl.Selected(i) = False
                    Next i
            End Select
        Next ctl
        Set ctl = Nothing
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Thanks, gmayor.

    This nearly achieved the result I was looking for, although it completely cleared the ComboBoxes of all their default values. This was my fault as I wasn't specific when describing my needs.

    Your suggested code has pointed me in the right direction. This seems to work nicely though.

    'Reset button
    Private Sub ResetBut_Click()
        Dim ctl         As MSForms.Control
        Dim i           As Integer
        For Each ctl In Me.Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = ""
                Case "CheckBox", "OptionButton", "ToggleButton"
                    ctl.Value = True
                Case "ComboBox"
                    ctl.ListIndex = 0
                Case "ListBox"
                    For i = 0 To ctl.ListCount
                        ctl.Selected(i) = False
                    Next i
            End Select
        Next ctl
        Set ctl = Nothing
    End Sub

  6. #6
    You cannot have all option boxes in the same range set to true as setting one toggles the rest off, so you would end up with only the last one processed set to true.
    The macro didn't remove any entries from the comboboxes, it merely set the default value as -1, i.e. nothing selected. Setting it at 0 is a valid option and would select the first listed item
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Ah, yes, I see what you mean.

    Thanks again, gmayor!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •