Not so easy color changing problem for buttons

Hello gang :)

I'd appreciate any kind of help with this, I've tried all I could find on google and using the VBA help, with no success.

I'm using Excel 2007 and I created a bunch of ActiveX Controls, some buttons basically that are all similar in behaviour, they just call the same sub with different paramenters. Well, what I want to do when one of the buttons is pressed (besides calling the specific sub with its parameters) is make that particular button change its backcolor. No pb so far, easy thing to do. But I want one more thing - let's say the default color for them buttons is Green, and after I press it they turn Red. Well, I click button A and it turns Red as expected, but after that I press button B and I want it to turn Red while, at the same time, make all the other buttons turn to their defult color, i.e. green.

Anyone has any ideas about this? I've been trying to use ShapeRanges and alike, but I'm a complete rookie with that so no success.

Furthermore (one more complication :) ), not all buttons have the same default color. I have three sets of buttons - red ones, green ones and blue ones - and they all turn black when pressed. So what I want to do is make a button turn black when pressed, while at the same time making all the other buttons return to their original color (that way I can always tell which button was pressed last). I know I could use some workaournds, like display in a cell which button was pressed and so on and not worry about them darn colors, but that is my last option :)

Thanks for any kinda help!

I'm not using user controls, just standard plain ole buttons on a UserForm, but I think the principle is the same.

Open the attached spreadsheet and a userform with a bunch of buttons will pop up.

Just start clicking the buttons. Is that what you meant?

Hack damn it, you were fast :)

I want something similar to that, indeed. The main diff is that not all buttons have the same default color.

I looked at your code and I think I got it, although I never used something similar (rookied :D)

Anyways, I thought of a different way to to this - each time I press a button I will remember (in a cell or somewhere) its name and color. Then, when I press a new button, I will first check to see which button was pressed last (excludin the current one) and make that button revert to it's default color. So what I need now is a way to extract the name of the control button from within the sub that gets called when the button is pressed, so if sub is called BlaBla1_Click I want to extract the string BlaBla and store it somewhere. Any clues?

Thanks a lot Hack, guess I'll use your approach someday, now it's too late for my sh|t as I didn't use a UserForm :)

Cheers!

If you aren't using a user form, then the code to iterate through controls on a worksheet is similiar, but with a couple of changes

Code:

---

Dim wks As Worksheet
 Dim OLEObj As OLEObject
 Set wks = Worksheets("Sheet1")

 For Each OLEObj In wks.OLEObjects
    If TypeOf OLEObj.Object Is MSForms.CommandButton Then
            'do whatever you need to do here
    End If
 Next

---