Change colour to show which button was last clicked

[fawnlemur]

Hi

I've researched and I can' find what I want the code do:

I want a code that will change a button's colour when it's clicked, but when another button is clicked it would change that button's colour and reset the previous ones.

I'm not too sure if there is a way to do it without linking to a specific button as I've got too many button.

I have tried to do it but came out with nothing, so I don't have any decent example code.

Thank you very much for your help

 

[Gerald Higgins]

There are probably better ways of doing this, but using the macro recorder I got code like this . . .

    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
        .Transparency = 0
        .Solid
    End With
    
    ActiveSheet.Shapes.Range(Array("Rectangle 2", "Rectangle 3")).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 255)
        .Transparency = 0
        .Solid
    End With

If by button you mean a shape such as a rectangle, and you assign the macro including this code to rectangle 1, then it turns rectangle 1 yellow, and rectangles 2 and 3 purple.
Play with the RGB values to get different colours.

 

[My Aswer Is This]

If your using Activex command buttons.

This script loops through all the Active sheet activex command buttons
I wrote this script but I do not know how to exclude the command button that activated the script.

Private Sub CommandButton1_Click()
'Modified  1/16/2019  9:12:21 AM  EST
For Each OLEobj In ActiveSheet.OLEObjects
    If OLEobj.progID = "Forms.CommandButton.1" Then
        With OLEobj.Object
            .Font.Size = 16
            .BackColor = vbRed
            .ForeColor = vbBlue
        End With
    End If
Next OLEobj
End Sub

 

[fawnlemur]

If your using Activex command buttons.

This script loops through all the Active sheet activex command buttons
I wrote this script but I do not know how to exclude the command button that activated the script.

Private Sub CommandButton1_Click()
'Modified  1/16/2019  9:12:21 AM  EST
For Each OLEobj In ActiveSheet.OLEObjects
    If OLEobj.progID = "Forms.CommandButton.1" Then
        With OLEobj.Object
            .Font.Size = 16
            .BackColor = vbRed
            .ForeColor = vbBlue
        End With
    End If
Next OLEobj
End Sub

Would this work on normal buttons as I'm not using ActiveX buttons

Thank you

 

[My Aswer Is This]

What is your deffinition of a Normal Button. Activex Buttons are normal buttons in my opinion. What are you using Shapes?

Would this work on normal buttons as I'm not using ActiveX buttons

Thank you

 

[fawnlemur]

Sorry, I meant form control button

 

[My Aswer Is This]

Well My script really does not do what you wanted. It was just a part of the code needed. Maybe someone else here will have a complete answer.

 

[My Aswer Is This]

I have discovered Form Control Command Buttons cannot have colors. They all must be default Gray.

 

[fawnlemur]

Is there any way just to make it look like it was clicked so it looks different from the other buttons


Thanks

 

[My Aswer Is This]

I'm curious what all these buttons do.

Normally a user should not need numerous buttons on a sheet. There are a lot of better ways to do things then to have numerous buttons doing things.

Did you know you can just click or double click or right click on a cell to have a script run.
And having to put scripts into numerous buttons can be a real task.