|
Reset All Command Buttons on an Excel UserForm
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. |
Reset All Command Buttons on an Excel UserForm
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) wrote in message ... 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. |
Reset All Command Buttons on an Excel UserForm
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 |
Reset All Command Buttons on an Excel UserForm
Maybe you should be looking at ToggleButtons (not commandbuttons), but I'm
confused too. Regards, Peter T wrote in message ... 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 |
Reset All Command Buttons on an Excel UserForm
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 |
Reset All Command Buttons on an Excel UserForm
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 |
Reset All Command Buttons on an Excel UserForm
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 wrote in message ... 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 |
Reset All Command Buttons on an Excel UserForm
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 .... wrote: 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 -- Dave Peterson |
Reset All Command Buttons on an Excel UserForm
On 14 Jan, 17:42, Dave Peterson wrote:
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 ... wrote: 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 -- 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 |
Reset All Command Buttons on an Excel UserForm
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) wrote in message ... On 14 Jan, 17:42, Dave Peterson wrote: 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 ... wrote: 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 -- 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 |
Reset All Command Buttons on an Excel UserForm
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. wrote: <<snipped 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 |
Reset All Command Buttons on an Excel UserForm
On 14 Jan, 19:34, "Rick Rothstein"
wrote: 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) wrote in message ... On 14 Jan, 17:42, Dave Peterson wrote: 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 ... wrote: 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 -- 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- 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 he 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 |
Reset All Command Buttons on an Excel UserForm
On 14 Jan, 19:46, Dave Peterson wrote:
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. wrote: <<snipped 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- 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 |
Reset All Command Buttons on an Excel UserForm
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.) =========== wrote: On 14 Jan, 19:46, Dave Peterson wrote: 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. wrote: <<snipped 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- 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 |
Reset All Command Buttons on an Excel UserForm
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) "Dave Peterson" wrote in message ... 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.) =========== wrote: On 14 Jan, 19:46, Dave Peterson wrote: 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. wrote: <<snipped 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- 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 |
Reset All Command Buttons on an Excel UserForm
On 14 Jan, 21:33, "Rick Rothstein"
wrote: 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) "Dave Peterson" wrote in message ... 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.) =========== wrote: On 14 Jan, 19:46, Dave Peterson wrote: 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. wrote: <<snipped 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- 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- Hide quoted text - - 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 |
Reset All Command Buttons on an Excel UserForm
Erase is a very nice addition.
Rick Rothstein wrote: 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) "Dave Peterson" wrote in message ... 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.) =========== wrote: On 14 Jan, 19:46, Dave Peterson wrote: 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. wrote: <<snipped 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- 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 -- Dave Peterson |
Reset All Command Buttons on an Excel UserForm
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)? -- Rick (MVP - Excel) |
Reset All Command Buttons on an Excel UserForm
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 |
Reset All Command Buttons on an Excel UserForm
Rick, Dave,
Got it. I combined both of your codes (as you intimated Rick!) Hey Presto success. I can't thank you both enough for the time you've afforded me. Google Groups strike again! Thanks. Glen |
| All times are GMT +1. The time now is 07:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com