Excel CommandButton.BackColor change

[MarH]

Within excel I have developed a chunk of code the cyclically changes the selected command button background color every time the button is pressed.

This code has been used on around 50 buttons and works well.

The worksheet contains other chunks of code and has a reset feature (button) which returns the sheet to a default preferred state.

The challenge is to programmatically change the 50 or so command button colors back to a known state when the reset is pressed - being a newbie at this am struggling for a solution - my initial thoughts are to use a for next to loop through the command buttons and set the background properties.. Something like....

For ValBut= 1 To 50
CommandButton & ValBut.BackColor = &HE0E0E0
Next ValBut

Obviously this does not work...

Help Please!!

 

[rmikesmith]

Here's one way:

    For ValBut = 1 To 50
      Me.OLEObjects("CommandButton" & ValBut).Object.BackColor = &HE0E0E0
    Next ValBut

Where I assume this code is in the code module for the worksheet containing the CommandButtons.


Regards,
Mike

 

[Andrzejek]

If you want to change ALL command buttons on your Form,
try something like this:

Dim cntlControl As Control

For Each cntlControl In Me.Controls
    If TypeOf cntlControl Is CommandButton Then
        cntlControl.BackColor = vbButtonFace
    End If
Next

Have fun.

---- Andy

 

[MarH]

Mike/ Andy...

Both solutions work well for me thank you for your help!!!



M