Command Buttons 1

[Harley78]

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?

 

[handleman]

Sure. A button can be assigned to any macro, and any number of buttons can be assigned to a single macro. Just write the macro once and assign all your buttons accordingly.

-handleman, CSWP (The new, easy test)

 

[Harley78]

Thats my problem, I do not have a clue on how to that. Can you help with an example? I appreciate your input.

 

[handleman]

Right-click the button and hit "Assign Macro". You can then choose from any macros in any spreadsheets that are open.

-handleman, CSWP (The new, easy test)

 

[electricpete]

There are two different kinds of "buttons"... form toolbar buttons and command toolbar 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.

 

[Harley78]

I am so sorry... I have already done the command buttons and assigned the codes. I was just wondering if there was a shorter example method of the code so each one doesn't have to receive the code in my above post.

 

[electricpete]

A first attempt to try to consolidate:

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.

 

[Harley78]

Thanks Pete, and here is what I am doing.

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

 

[electricpete]

I don't quite get the task. Trying to connect to the original code, I do recognize now that you were trying to construct a simple toggle funciton.

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.

 

[Harley78]

That is basically what I was doing with all the fancy macro's. However, I wish the button would get darker instead of light when true. Otherwise that would be easier.

Thanks so much for your suggestion.

 

[electricpete]

Assuming your togglebutton name is togglebutton2

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.

 

[electricpete]

By the way, one small tip you can use to make your worksheet a little more manageable if you are going to spend some time maintaining and customizing those numerous button procedures.

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.

 

[Harley78]

You are correct, however it is much faster to change the button name than it is to change Cell, and button name.

Thanks so much for the great idea.