Reset All Command Buttons on an Excel UserForm

[gmcnaugh]

Hi,

I’m hoping someone can help with this. I have a workbook with some 80
user forms each one containing up to 40 command buttons. I would like
to add another command button which when pressed will reset all of the
other buttons to false. THis function will enable the user to
'refresh' the form should he make a mistake. I do not want to reload
(Unload Me) the form as there is some information pre popluated which
has to be kept.

I have been scouring the internet for a couple of days now and the
closest I have come is:

Private Sub ClearAllTextboxes()

Dim objTemp As Control
For Each objTemp In Me.Controls
If TypeOf objTemp Is TextBox Then objTemp.Text = ""

Next
End Sub

This code as you can see was used to clear text from various text
boxes on a form, however, by substituting “TextBox” for
“CommandButton” and “.Text = “”” for “.Visible = False”I can get all
of the buttons to disappear, including the “Reset Button” which makes
me think this piece of code would suit if only I knew what should be
used in place of “.Text = “””

I am using Boolean with the commandbuttons.

Hope you can help.

Thanks.

 

[Rick Rothstein]

I'm a little confused what you are trying to do with your CommandButtons.
What do you mean by "reset all the other buttons to false" as it applies to
your CommandButtons? Exactly what are you trying to do with the
CommandButtons?

--
Rick (MVP - Excel)


Hi,

I’m hoping someone can help with this. I have a workbook with some 80
user forms each one containing up to 40 command buttons. I would like
to add another command button which when pressed will reset all of the
other buttons to false. THis function will enable the user to
'refresh' the form should he make a mistake. I do not want to reload
(Unload Me) the form as there is some information pre popluated which
has to be kept.

I have been scouring the internet for a couple of days now and the
closest I have come is:

Private Sub ClearAllTextboxes()

Dim objTemp As Control
For Each objTemp In Me.Controls
If TypeOf objTemp Is TextBox Then objTemp.Text = ""

Next
End Sub

This code as you can see was used to clear text from various text
boxes on a form, however, by substituting “TextBox” for
“CommandButton” and “.Text = “”” for “.Visible = False” I can get all
of the buttons to disappear, including the “Reset Button” which makes
me think this piece of code would suit if only I knew what should be
used in place of “.Text = “””

I am using Boolean with the commandbuttons.

Hope you can help.

Thanks.

 

[gmcnaugh]

Rick,

Basically I am creating is an Electronic Point of Sale type of tool.
So as the user selects (pushes) each button from the selection 2 text
boxes are populated with strings of text, 1 is a code and the other is
wording reflecting the code. However, as testing has gone on it has
become apparent from the users that they would like the ability to
'reset' the form i.e. cancel all selections and return the form to its
initial state. I do not want to use 'Unload Me' because on initial
opening of the form the user is requested via 3 pop up forms to enter
certain data which is then displayed on the main form.

When the buttons are selected the boolean becomes true, e.g.

Private Sub CommandButton9_Click()
If booCommandButton9 = False Then
booCommandButton9 = True
Else: booCommandButton9 = False
End If
If booCommandButton9 Then
CommandButton9.BackColor = &HFFFFFF
Else: CommandButton9.BackColor = &H808080
End If
Call StringBuilder
End Sub

I would like the reset button to set the buttons to their initial
state and clear the text boxes (I think I know how to reset the text
boxes but there may be a routine which will reset the buttons and text
boxes together (?).

As I said in my initial message, the code I posted seems to be
something like I need if only I knew what to replace the last part
with.

Hopefully I have managed to clarify what I am after.

Thanks for your assistance.

Glen

 

[Peter T]

Maybe you should be looking at ToggleButtons (not commandbuttons), but I'm
confused too.

Regards,
Peter T

 

[gmcnaugh]

Peter,

By toggle buttons do you mean radio buttons?

The reason for commandbuttons is that the HMI is a touchscreen with
data being entered by personnel wearing gloves (sometimes). We
initially had radio buttons and check boxes but these proved to be too
small for gloved fingers, hence the reason for the commandbutton
choice.

Please let me know which part of my question you are confused with and
I will try and clarify further.

Thanks,

Glen

 

[Dave Peterson]

Do you mean Checkboxes?

Dim ctrl as control
for each ctrl in me.controls
if typeof ctrl is msforms.checkbox then
ctrl.value = false
end if
next ctrl

 

[gmcnaugh]

Dave,

No I am using comandButtons, however, your code is similar to that
which I have seen already.

I have tried your code replacing ".checkbox" with "CommandButton" but
that is not working.

On my userform when a button is pressed the boolean becomes True
generating a code and text in 2 Text Fields. And obviously when it is
depressed it becomes False, removing the code and text from the
fields. An example of the button coding is shown below:

Private Sub CommandButton9_Click()
If booCommandButton9 = False Then
booCommandButton9 = True
Else: booCommandButton9 = False
End If
If booCommandButton9 Then
CommandButton9.BackColor = &HFFFFFF
Else: CommandButton9.BackColor = &H808080
End If
Call StringBuilder
End Sub


The StringBuilder Sub does what it says on the tin, it generates the
code and text.

Hopefully this clarifies my query.

Thanks,

Glen

 

[Peter T]

By toggle buttons do you mean radio buttons?

No I meant "ToggleButton" controls. These can look like CommandButtons but
they can be raised or depressed, as reflected or changed by the Value
property (which fires its click event). By default they do not work like
radio buttons (OptionButtons) though with a little more code they can be
made to do so.

Put two ToggleButton's on the form, run the form and repeatedly click the
form to toggle all of them

Private Sub ToggleButton1_Click()
If Me.ToggleButton1.Value Then
' do depressed state stuff
Else
' do raised state stuff
End If
End Sub

Private Sub UserForm_Click()
Dim ctrl As MSForms.Control
Static bVal As Boolean

bVal = Not bVal
For Each ctrl In Me.Controls
If TypeName(ctrl) = "ToggleButton" Then
ctrl.Value = bVal
End If
Next
End Sub

Regards,
Peter T

 

[Dave Peterson]

It looks like booCommandButton9 is just a boolean variable.

Create a subroutine that just changes all those variables to false.

booCommandbutton1 = false
booCommandbutton2 = false
....
booCommandbutton9 = false
....

 

[gmcnaugh]

It looks like booCommandButton9 is just a boolean variable.

Create a subroutine that just changes all those variables to false.

booCommandbutton1 = false
booCommandbutton2 = false
...
booCommandbutton9 = false
...













--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

You are correct in assuming that the buttons are boolean variables and
it would be easy to do as you say, however, not all of the 80 forms
have the same amount of buttons. Some have as many as 45. I also
only want to rest the ones which have been selected.

I am currently trying to adapt the code which Peter posted but it is
cycling all of the buttons (I should have made the fact that I only
want the selected buttons to be deselected earlier so apologies for
that).

I'd be extremely grateful if anyone could assist in some code to only
reset the 'active' buttons.

Once again many thanks.

Glen

 

[Rick Rothstein]

The "buttons" are Boolean values? That doesn't really make sense to me. Do
you have CommandButtons on your UserForm? If so, what part of them is True
and/or False... their Captions? Some linked cell? If you describe what you
have in some more detail than you currently have done, I'm sure you will get
a much faster answer. Remember... we can't see what you are talking about,
so you need to tells us... in detail... so we will know what you appear to
be taking for granted.

--
Rick (MVP - Excel)

It looks like booCommandButton9 is just a boolean variable.

Create a subroutine that just changes all those variables to false.

booCommandbutton1 = false
booCommandbutton2 = false
...
booCommandbutton9 = false
...













--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

You are correct in assuming that the buttons are boolean variables and
it would be easy to do as you say, however, not all of the 80 forms
have the same amount of buttons. Some have as many as 45. I also
only want to rest the ones which have been selected.

I am currently trying to adapt the code which Peter posted but it is
cycling all of the buttons (I should have made the fact that I only
want the selected buttons to be deselected earlier so apologies for
that).

I'd be extremely grateful if anyone could assist in some code to only
reset the 'active' buttons.

Once again many thanks.

Glen

 

[Dave Peterson]

If the boolean variable myBln is already false, then:
myBln = false
won't hurt a bit.

You could use:
if mybln = true then mybln = false

But I don't see much of an improvement.

======
Have you thought about using an array of booleans? Then you could loop through
the array.

 

[gmcnaugh]

The "buttons" are Boolean values? That doesn't really make sense to me. Do
you have CommandButtons on your UserForm? If so, what part of them is True
and/or False... their Captions? Some linked cell? If you describe what you
have in some more detail than you currently have done, I'm sure you will get
a much faster answer. Remember... we can't see what you are talking about,
so you need to tells us... in detail... so we will know what you appear to
be taking for granted.

--
Rick (MVP - Excel)








Dave,

You are correct in assuming that the buttons are boolean variables and
it would be easy to do as you say, however, not all of the 80 forms
have the same amount of buttons.  Some have as many as 45.  I also
only want to rest the ones which have been selected.

I am currently trying to adapt the code which Peter posted but it is
cycling all of the buttons (I should have made the fact that I only
want the selected buttons to be deselected earlier so apologies for
that).

I'd be extremely grateful if anyone could assist in some code to only
reset the 'active' buttons.

Once again many thanks.

Glen- Hide quoted text -

- Show quoted text -

Rick,
Each of my 80 UserForms has anything between 5 and 45 CommandButtons
which when ‘pressed’ by the user generate a code in a textbox and
associated text which is a decode of the code in another textbox.

I would like to place another commandbutton on each userform which
will ‘reset’ only the ‘selected’ commandbuttons, thus in effect
resetting the form.
An example of the commandbutton coding is shown here:

Private Sub CommandButton9_Click()
If booCommandButton9 = False Then
booCommandButton9 = True
Else: booCommandButton9 = False
End If
If booCommandButton9 Then
CommandButton9.BackColor = &HFFFFFF
Else: CommandButton9.BackColor = &H808080
End If
‘StringBuilder is the routine which builds the code and text strings
Call StringBuilder
End Sub

As you can see these work like ‘togglebuttons’. 1st press selects the
button and generates code/text. A subsequent press deselects the
button and removes the code/text.

As I say I am hoping to be able to code 1 button on each form which
when pressed will deselect any button previously selected, effectively
resetting the form. I do not want to use ‘Unload Me’ as the form
comes pre populated with some data generated from 3 other ‘pop up’
forms.

Hope this clarifies things.

Glen

 

[gmcnaugh]

If the boolean variable myBln is already false, then:
myBln = false
won't hurt a bit.

You could use:
if mybln = true then mybln = false

But I don't see much of an improvement.

======
Have you thought about using an array of booleans?  Then you could loopthrough
the array.







(e-mail address removed) wrote:








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

I am fairly new to vba so I am not sure what you mean about using an
array of booleans. Could you clarify please?

Thanks,

Glen

 

[Dave Peterson]

Dim booCommandButton(1 to 80) as boolean

Private Sub CommandButton9_Click()
booCommandButton(9) = not boocommandbutton(9)

If booCommandButton(9) = true Then
CommandButton9.BackColor = &HFFFFFF
Else
CommandButton9.BackColor = &H808080
End If

Call StringBuilder

End Sub

Then in some buttonclick event:

Private Sub cBtnResetAllBooleans()
dim iCtr as long

for ictr = lbound(booCommandButton) to ubound(booCommandButton)
me.controls("commandbutton" & ictr).value = false
booCommandButton(ictr) = false
next ictr

End sub

(Untested, uncompiled.)

===========

 

[Rick Rothstein]

Coupling your Boolean array concept with the OP's original request... if the
OP's "reset all" command button is named CommandButtonResetAll, then this
code (added to his existing UserForm code for the UserForm where the
CommandButtonResetAll button is located) will reset all the Boolean
variables to False, clear out all TextBox'es, and reset the back color of
each CommandButton (with the exception of the button named
CommandButtonResetAll)...

Dim booCommandButton(1 To 80) As Boolean

Private Sub CommandButtonResetAll_Click()
Dim objTemp As Control
Dim objForm As UserForm
For Each objForm In UserForms
For Each objTemp In objForm.Controls
If TypeOf objTemp Is MSForms.TextBox Then
objTemp.Text = ""
ElseIf TypeOf objTemp Is CommandButton Then
If objTemp.Name <> CommandButtonResetAll.Name Then
Erase booCommandButton
objTemp.BackColor = &H808080
End If
End If
Next
Next
End Sub

 

[gmcnaugh]

Coupling your Boolean array concept with the OP's original request... if the
OP's "reset all" command button is named CommandButtonResetAll, then this
code (added to his existing UserForm code for the UserForm where the
CommandButtonResetAll button is located) will reset all the Boolean
variables to False, clear out all TextBox'es, and reset the back color of
each CommandButton (with the exception of the button named
CommandButtonResetAll)...

Dim booCommandButton(1 To 80) As Boolean

Private Sub CommandButtonResetAll_Click()
  Dim objTemp As Control
  Dim objForm As UserForm
  For Each objForm In UserForms
    For Each objTemp In objForm.Controls
      If TypeOf objTemp Is MSForms.TextBox Then
        objTemp.Text = ""
      ElseIf TypeOf objTemp Is CommandButton Then
        If objTemp.Name <> CommandButtonResetAll.Name Then
          Erase booCommandButton
          objTemp.BackColor = &H808080
        End If
      End If
    Next
  Next
End Sub

--
Rick (MVP - Excel)















- Show quoted text -

Rick,

Many thanks, it is almost there. The only problem is that although
the buttons are changing colour they are not actually 'deselecting'
i.e. I don't think the boolean variables are changing to false.
Any thoughts?

I am off to bed now but will have another try in the morning.

Dave,

I did not get a chance to try yours but will give it a go AM.

Many thanks for the patience you are affording me.

Glen

 

[Dave Peterson]

Erase is a very nice addition.

Coupling your Boolean array concept with the OP's original request... if the
OP's "reset all" command button is named CommandButtonResetAll, then this
code (added to his existing UserForm code for the UserForm where the
CommandButtonResetAll button is located) will reset all the Boolean
variables to False, clear out all TextBox'es, and reset the back color of
each CommandButton (with the exception of the button named
CommandButtonResetAll)...

Dim booCommandButton(1 To 80) As Boolean

Private Sub CommandButtonResetAll_Click()
Dim objTemp As Control
Dim objForm As UserForm
For Each objForm In UserForms
For Each objTemp In objForm.Controls
If TypeOf objTemp Is MSForms.TextBox Then
objTemp.Text = ""
ElseIf TypeOf objTemp Is CommandButton Then
If objTemp.Name <> CommandButtonResetAll.Name Then
Erase booCommandButton
objTemp.BackColor = &H808080
End If
End If
Next
Next
End Sub

 

[Rick Rothstein]

Erase booCommandButton

Rick,

Many thanks, it is almost there. The only problem is that
although the buttons are changing colour they are not actually
'deselecting' i.e. I don't think the boolean variables are
changing to false. Any thoughts?

They should be all changing to False... that is what the Erase statement
should be forcing upon them. Did you try the code as it was posted (with the
declaration for the Boolean array in the General-Declarations section of the
code window and *not* inside any particular subroutine or function)?

 

[gmcnaugh]

Rick,

Have now had a proper chance to try your code, however, as I said the
'Erase' does not seem to be 'falsifying' the 'boocommandbuttons.'

I have stepped through a userform and find that after pressing the
reset button the boocommandbuttons still remain in their true state.

Any thoughts.

Glen