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

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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

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" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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

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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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

This should put it back to the default color.

c.BackColor = vbButtonFace

"art" wrote:

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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"art" wrote in message
...
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