Change Button color
Andrew Henderson 
I'm using excel and I'm having trouble, I need buttons that will change color when clicked. I can use a command button but it needs me to label each of them individual "CommandButton1, CommandButton2 ,CommandButton3 ...CommandButton1000." I will be using many of them and wanted to know what I should be doing so I could have one general macros program a can slap on a shape/command button. This program only needs to change the color back and forth when clicked. This is what I'm working with.
'Private Sub CommandButton_Click()
Select Case CommandButton1.BackColor Case -2147483633 'Default Grey col = 228 'FP Red Case Else col = -2147483633 'Default Grey
End Select
CommandButton1.BackColor = col
End Sub1 Answer
I would recommend inserting a module into the workbook and write generic color changing code there. Then in the sheet with the buttons, simply call that code and pass the sub the button clicked. The module sub would look something like this:
Public Sub ColorChange(ByRef btn As Object) Select Case btn.Name Case "CommandButton1" btn.BackColor = RGB(200, 0, 0) Case "CommandButton2" btn.BackColor = RGB(0, 200, 0) Case "CommandButton3" btn.BackColor = RGB(0, 0, 200) Case "CommandButton4" btn.BackColor = RGB(200, 0, 200) End Select
End Sub 'ColorChangeThe button click would look something like this:
Private Sub CommandButton1_Click() Call ColorChange(Me.CommandButton1)
End SubYou can put whatever changes you like depending on which button is clicked. Also, you can name the buttons whatever you want. Right click the button and go to properties and you can change the name. Typical naming convention for a button is to prefix the control with cmd or btn and a name. For example, btnColor1. But that's all up to you, of course.
P.S. Control arrays for large numbers of controls is probably a good idea. Check this out:
0
