Reset Form Button

[HTSCF Fareha]

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!

[Bob Phillips]

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

[HTSCF Fareha]

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

[gmayor]

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

[HTSCF Fareha]

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

[gmayor]

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

[HTSCF Fareha]

Ah, yes, I see what you mean.

Thanks again, gmayor!