Command Buttons
Command Buttons
(OP)
Currently I have anywhere from 5-32 command buttons on each tab (worksheet)which I have 32 worksheets. Each Command Button Will change color if selected as well as enter data in a cell that I specified. As well As if selected again...... See current code,
Private Sub CommandButton38_Click()
If CommandButton38.BackColor = RGB(255, 255, 0) Then_CommandButton38.BackColor = RGB(255, 255, 255) Else_CommandButton38.BackColor = RGB(255, 255, 0)
Range("A37").Value = 1
If CommandButton38.BackColor = RGB(255, 255, 255) Then _Range("A37").Value = ""
End Sub
So my question is, Does anyone have any suggestion how I could condence this action instead of having to copy or write this code over and over?
Private Sub CommandButton38_Click()
If CommandButton38.BackColor = RGB(255, 255, 0) Then_CommandButton38.BackColor = RGB(255, 255, 255) Else_CommandButton38.BackColor = RGB(255, 255, 0)
Range("A37").Value = 1
If CommandButton38.BackColor = RGB(255, 255, 255) Then _Range("A37").Value = ""
End Sub
So my question is, Does anyone have any suggestion how I could condence this action instead of having to copy or write this code over and over?





RE: Command Buttons
-handleman, CSWP (The new, easy test)
RE: Command Buttons
RE: Command Buttons
-handleman, CSWP (The new, easy test)
RE: Command Buttons
If you use forms toolsbar buttons, you can do it exactly the way handleman described. If you use control toolbar buttons, you have to enter design mode, double-click the button to enter the button event handler module, and enter the name of the macro you which to call within that module.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Command Buttons
RE: Command Buttons
Private Sub CommandButton38_Click()
Dim mybutton As Control
Set mybutton = Something ' need to fix this part
Call generalbuttonclick(mybutton)
End Sub
Public Sub generalbuttonclick(thebutton As Control)
' This sub needs to go in a general module
If thebutton.BackColor = RGB(255, 255, 0) Then
thebutton.BackColor = RGB(255, 255, 255)
Else
thebutton.BackColor = RGB(255, 255, 0)
End If
Range("A37").Value = 1
If thebutton.BackColor = RGB(255, 255, 255) Then
Range("A37").Value = ""
End If
Stop
End Sub
THE PROBLEM - – you need to figure out some real code to substitue for Set mybutton = Something, i.e. some way to tell the general module about the calling button. I don't see any way to do it where you have the same code for each calling button. Could probably be done by writing custom (different) code for each calling button, but that kind of defeats the purpose.
If you are interested to describe the big picture of your project, I'm guessing there might be easier ways to do it.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Command Buttons
I have basically a visio drawing in Excel. This drawing allows one to configure a complete part (this example a wheel).
Example. Rim, Spokes, Tube, Tire. each one also being a different size. So this worksheet will have 16 command buttons.
I have made Command boxes for each selection so below will be a list of parts needed. This is a simple example, but there are worksheets that have 35+ command buttons.
Maybe you can suggest a different approach as you stated.
Thanks for all your help
RE: Command Buttons
You can implement a toggle function a number of ways.
One is to use the forms toolbar checkbox. Use right-click / format / control / cell link to set the cell. Then the values in the cell will be true or false.
Another would be to use data / validation / list to control the value entered in a cell to 1 of two values.
In both the above cases, you could use conditional formatting to provide additional color cue about the status.
There is one other control that I think does exactly what you want without any code. The control toolbar "togglebutton". It's icon looks like two command buttons one on top of the other (if it's not on your control toolbar you may have to go to the right of that toolbar where it allows you to select more buttons).
Once you have inserted a togglebutton, right click on it (in design mode) to edit it's properties. If you enter a cell address in the "linked cell" property, then that cell will toggle between true and false as the button is pressed. Also the appearance of the button toggles when it is pressed.
You shouldn't really need to do anything with the code. But if you do want to use the code, you can test the new true/false value of the controlled object after the button was pressed using togglebutton1.Object.value (where togglebutton1 is the name of the button). You can also change the caption displayed to the user on the fly within the togglebutton_click procedure using togglebutton1.caption Other fields are available for your use as you can see in the properties window.
Don't know if this helps. I'd be glad to hear more if it isn't what you're looking for.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Command Buttons
Thanks so much for your suggestion.
RE: Command Buttons
Put this in your togglebutton2_click() event
Private Sub ToggleButton2_Click()
With ToggleButton2
If .Object.Value Then
.BackColor = &H80000002
Else
.BackColor = &H80000003
End If
End With
End Sub
You would put the same code into all your buttons, except you have to edit the name of the button which appears after togglebutton2.... which amounts to customizing each one... which is what you were trying to get away from. Hmm.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Command Buttons
The first thing you should do when you create a new button (before you double-click to go to it's click event procedure) is change the property labeled (Name). Instead of Togglebutton2, call it SpokeButton, or something similarly descriptive. Then when you double-click to edite the click procedure, the procedure will be named SpokeButton_Click(). You can go through all the buttons in your spreadsheet and recognize the name without having to refer to the spreadsheet to find the button number.
Also note the difference between (Name) and caption. (Name) is what the programmer sees... caption is what the user sees.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Command Buttons
Thanks so much for the great idea.