Command button color

[art]

Hello:

I have a userform with 15 command buttons on it. I want to make the color of
the command button the same color of the sheet tab. So I have the following
code:

MyColor = Sheet1.Tab.Color
Me.CommandButton1.BackColor = MyColor

However, since I have 15 buttons, I would like to write a code that will do
it to all command buttons without having to have to write for each command
button this code. It looks like there must be an easier way with either a
loop, or with...

Please help me.

Thanks

 

[Rick Rothstein]

Give code something like this a try...

Dim C As Control
Dim Counter As Long
For Each C In Me.Controls
If TypeOf C Is CommandButton Then
Counter = Counter + 1
C.BackColor = Worksheets(Counter).Tab.Color
End If
Next

 

[art]

Thanks. However, now all the sheets that don't have any color applied to
their tab the command button for that sheet is black. Do you know why? It
should be the default color. Can you please correct.

Thanks

 

[Rick Rothstein]

Try this...

Dim C As Control
Dim Counter As Long
For Each C In Me.Controls
If TypeOf C Is CommandButton Then
Counter = Counter + 1
If Worksheets(Counter).Tab.Color Then
C.BackColor = Worksheets(Counter).Tab.Color
End If
End If
Next

 

[art]

thanks. I made a check box with your code. Now, the check box turns on the
color, how can I make that if the user uncheks the checkbox, the colors
should go back to the default?

Private Sub CheckBox1_Click()
If UserForm1.CheckBox1 = True Then
Dim C As Control
Dim Counter As Long
For Each C In Me.Controls
If TypeOf C Is CommandButton Then
Counter = Counter + 1
If Worksheets(Counter).Tab.Color Then
C.BackColor = Worksheets(Counter).Tab.Color
End If
End If
Next
Else
End If
End Sub

 

[JLGWhiz]

This should put it back to the default color.

c.BackColor = vbButtonFace

 

[art]

An error popped up for that code. I ended up uding the color value
(-2147483633).
Thanks

 

[Rick Rothstein]

This CheckBox Click event procedure should do what you want...

Private Sub CheckBox1_Click()
Dim C As Control
Dim Counter As Long
If CheckBox1.Value Then
For Each C In Me.Controls
If TypeOf C Is CommandButton Then
Counter = Counter + 1
If Worksheets(Counter).Tab.Color Then
C.BackColor = Worksheets(Counter).Tab.Color
End If
End If
Next
Else
For Each C In Me.Controls
If TypeOf C Is CommandButton Then C.BackColor = vbButtonFace
Next
End If
End Sub