Command Button Color Palette

[Greg in CO]

Hi All!

I have added several command buttons to my worksheet to run macros
triggering custom views to allow the users to only see the rows applicable to
the phase they are currently working (all in an effort to make the worksheet
easier on the eyes).

The header rows for each phase have a specific color, with the cells being
fille din using the palette in the Format Cells function.

When I go into the properties for the Command Button while in design mode,
Excel shows a similar color palette, but not with the exact same colors as
appear when coloring a cell. I want the button to be "Gold" as in the
regular Excel cell color palette, but the Command Button palette does not
have that color. The closest I can get is either pale yellow or pumpkin
orange. The orange has a code of &H000080FF&. Is there a way to get the
saem colors? Is there a way to get the code for the Gold color I am looking
for?

Thanks!

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure if there is an easier way to do this, but the following will
work. First, go to a code window (any one will do) and copy/paste this code
into it...

'***************** START OF CODE *****************
Sub GetRGB()
Dim OldColorIndex As Long
Dim CellColor As Long
Const NewColorIndex As Long = 47
OldColorIndex = Range("A1").Interior.ColorIndex
Range("A1").Interior.ColorIndex = NewColorIndex
CellColor = CLng(Range("A1").Interior.Color)
Range("A1").Interior.ColorIndex = OldColorIndex
Debug.Print "Red: " & RGorB(CellColor, "R")
Debug.Print "Gree: " & RGorB(CellColor, "G")
Debug.Print "Blue: " & RGorB(CellColor, "B")
End Sub

Function RGorB(RGBvalue As Long, R_G_or_B As String) As Integer
Dim Exponent As Long
Exponent = InStr("RGB", UCase$(Left$(R_G_or_B, 1))) - 1
If Exponent = -1 Or RGBvalue < 0 Or RGBvalue > 16777216 Then
RGorB = -1
Else
RGorB = RGBvalue \ 256 ^ Exponent Mod 256
End If
End Function
'***************** END OF CODE *****************

Change the NewColorIndex constant in the GetRGB subroutine to the ColorIndex
value you want to match and then run the GetRGB subroutine... it will print
out the RGB values for the ColorIndex you specified. Now, go to the
Properties Window for the CommandButton, click the down arrow for the
BackColor property and select the Palette tab. Now, right click any of the
16 white boxes at the bottom and type in the Red, Green and Blue values that
the above code printed out to the Immediate Window, then click the "Add
Color" button. Your CommandButton should now be colored with the same color
as the ColorIndex you specified.

Rick

 

[Greg in CO]

Wow! How do I find the color index value (i.e. for the "gold" color square
on the default Excel palette?).

Not a VBA person by any stretch of the imagination, so this is uncharted
territory for me....

 

[Rick Rothstein \(MVP - VB\)]

Actually, the ColorIndex gives you access to more colors than the Excel
palette. Here is how you can see the colors. Go to a blank worksheet and
right click its tab, select View Code from the pop up menu and copy/paste
this code into the code window that appeared (you can do this to get to a
code window to execute the first code I posted to this thread)...

Sub ShowColorIndexColors()
Dim X As Long
For X = 1 To 56
Cells(X, "A").Interior.ColorIndex = X
Next
End Sub

Go back to the worksheet, press Alt+F8 and select ShowColorIndexColors from
the list, then click the Run button. The first 56 cells in Column A should
fill with colors. Find a color you like, and note the row number it is on
(the row number will correspond to the ColorIndex number of that color).
Now, go back to my first posting and use the above found row number for the
NewColorIndex constant's value assignment and then follow the rest of the
instruction I gave you there.

Rick

 

[Greg in CO]

Hi Rick! This is amazing.....

I'm almost there. The color I wanted was on line 44 in the blank worksheet
where I followed your second set of instructions. So, I replaced the
NewColorIndex entry of 47 (from your post) to 44, then went to the VBA screen
toolbar and clicked on "Run".

You mentioned it printing out the RGB values....I looked but did not see any
response showing the RGB values...I'm sure I'm not looking in the correct
area or not doing something.

Here is what I had in my VBA window:

Sub GetRGB()
Dim OldColorIndex As Long
Dim CellColor As Long
Const NewColorIndex As Long = 44
OldColorIndex = Range("A1").Interior.ColorIndex
Range("A1").Interior.ColorIndex = NewColorIndex
CellColor = CLng(Range("A1").Interior.Color)
Range("A1").Interior.ColorIndex = OldColorIndex
Debug.Print "Red: " & RGorB(CellColor, "R")
Debug.Print "Gree: " & RGorB(CellColor, "G")
Debug.Print "Blue: " & RGorB(CellColor, "B")
End Sub

Function RGorB(RGBvalue As Long, R_G_or_B As String) As Integer
Dim Exponent As Long
Exponent = InStr("RGB", UCase$(Left$(R_G_or_B, 1))) - 1
If Exponent = -1 Or RGBvalue < 0 Or RGBvalue > 16777216 Then
RGorB = -1
Else
RGorB = RGBvalue \ 256 ^ Exponent Mod 256
End If
End Function

 

[Rick Rothstein \(MVP - VB\)]

It will print the values out to the Immediate Window... perhaps you don't
have that displayed... click View/Immediate Window in the VB editor's menu
bar or key in Ctrl+G (also when in the VB editor)... then rerun the GetRGB
subroutine. An alternative is to change the Debug.Print to MsgBox (this will
give you 3 MessageBox'es, one for each color).

Rick

Hi Rick! This is amazing.....

I'm almost there. The color I wanted was on line 44 in the blank
worksheet
where I followed your second set of instructions. So, I replaced the
NewColorIndex entry of 47 (from your post) to 44, then went to the VBA
screen
toolbar and clicked on "Run".

You mentioned it printing out the RGB values....I looked but did not see
any
response showing the RGB values...I'm sure I'm not looking in the correct
area or not doing something.

Here is what I had in my VBA window:

Sub GetRGB()
Dim OldColorIndex As Long
Dim CellColor As Long
Const NewColorIndex As Long = 44
OldColorIndex = Range("A1").Interior.ColorIndex
Range("A1").Interior.ColorIndex = NewColorIndex
CellColor = CLng(Range("A1").Interior.Color)
Range("A1").Interior.ColorIndex = OldColorIndex
Debug.Print "Red: " & RGorB(CellColor, "R")
Debug.Print "Gree: " & RGorB(CellColor, "G")
Debug.Print "Blue: " & RGorB(CellColor, "B")
End Sub

Function RGorB(RGBvalue As Long, R_G_or_B As String) As Integer
Dim Exponent As Long
Exponent = InStr("RGB", UCase$(Left$(R_G_or_B, 1))) - 1
If Exponent = -1 Or RGBvalue < 0 Or RGBvalue > 16777216 Then
RGorB = -1
Else
RGorB = RGBvalue \ 256 ^ Exponent Mod 256
End If
End Function

 

[Greg in CO]

Hi Rick!

Thanks for the info...it worked like a charm!!! Thanks for being
patient...you win today's "You Rock!" award.

;o)

 

[Rick Rothstein \(MVP - VB\)]

You are quite welcome... I am glad we got it to work out for you.

Rick (a "You Rock" award winner) <g>