Cell color = Button face color. Possible?

[Sam Kuo]

Is there an equivalent "color index" for "system color" in VBA?

e.g. I'm thinking to color cells the same as a default button face color
(i.e. system color &H8000000F&) without having to create a shape or button,
but it doesn't seem to be possible from this very useful link I found in this
forum sometime ago...Is this right?

http://www.mvps.org/dmcritchie/excel/colors.htm

Sam

 

[FSt1]

hi,
i don't have 2007. 2002 here. but the RGB equivilent to the button color is
RGB(220,220,220). how you would adjust that in 2007, i don't know.
in 2002, i adust the colors using the RGB scale.

regards
FSt1

 

[Sam Kuo]

Thanks FSt1. But RGB(220,220,220) appears to be "Grey-25%" in the standard
Excel color platte, not Button Face color??

I'm using Excel 2003.

Sam

 

[Jon Peltier]

You need to use some VBA or similar magic to convert from the windows system
colors to RGB that you can apply to objects in Excel. Here's a couple
relevant links:

http://www.vbaccelerator.com/home/VB/Tips/Get_an_RGB_Color_from_an_OLE_COLOR/article.asp
http://www.vbaccelerator.com/tips/vba0018.htm

- Jon

 

[Sam Kuo]

I used the color detector downloaded from David McRitchie's website mentioned
in the last post.
It showed that the Button Face color is RGB(236,233,216). so I then run the
macro using this RGB setting, but the outcome cell color becomes
RGB(255,255,204) instead according to the same Color Detector used... Any
idea?

Thanks
Sam

 

[Sam Kuo]

Thanks Jon. I followed what the link said, but get a "run-time error 438"
after clicking the command button...Any idea?

Error occurs at this line:
Label1.BackColor = Combo1.ItemData(Combo1.ListIndex)

 

[Sam Kuo]

Looks like Excel only allows limited color as the cells interior color. Any
other RGB triple will just be mapped to the nearest color index.

http://support.microsoft.com/?id=170781

Thanks FSt1 and Jon for your knid help

 

[minimaster]

I had a similar question a while back when I was trying to format
pivot tables with VBA.
Excel shows the button face color in a cell in the header of a pivot
table when the pivot table is originally created. When you apply a
std. cell color to the header cells of the pivot table there seems to
be no way to restore the button face color for these cells other than
completely resetting the whole pivot table formatting by setting the
pivottable property .PreserveFormatting= false. I've spent some time
exploring the object model of the pivot table to see whether I can
find anything that defines this button face color for the header
cells, or that can be used directly to reset only the interior color
of these cells instead of resetting the format of the whole pivot
table. I couldn't find anything. Does someone know a better way???

 

[minimaster]

And by the way, you can precisely adjust a cell color by modifying the
RGB setting of a certain colorindex via "Tools"-> Options -> Color ->
Modify -> Custom.

 

[minimaster]

or via VBA

ActiveWorkbook.Colors(15) = RGB(236,233,216)

 

[Peter T]

The function can accept values 0 to 40 (don't think any system has that many
system colours) or system color constants from
-2147483608 to -2147483648

The two test procedures will customize the palette, so run in a new workbook

Public Declare Function GetSysColor Lib "user32" ( _
ByVal nIndex As Long) As Long

Function fnSysClr(ByVal nSysClr As Long) As Long
Dim lngColor As Long

If nSysClr < -2 ^ 31 + 40 Then
nSysClr = 2 ^ 31 + nSysClr
End If

If nSysClr >= 0 And nSysClr < 40 Then
fnSysClr = GetSysColor(nSysClr)
End If

End Function

Sub test1()
Dim nClr As Long, x&

x = vbActiveTitleBar
' x = vbButtonFace
nClr = fnSysClr(x)

With ActiveCell.Interior
.Color = nClr
' did the colour alread exist in the palette or was
' only the nearest colour applied
If .Color <> nClr Then
' customize a palette colour and reapply
ActiveWorkbook.Colors(49) = nClr
.ColorIndex = 49
Else
Debug.Print .ColorIndex
End If
End With

End Sub

Sub test2()
Dim i As Long
Dim nClr As Long
For i = 0 To 39 ' probably exceeds no. of system colours
nClr = fnSysClr(i)
If nClr >= 0 And nClr <= 17666215 Then
ActiveWorkbook.Colors(i + 16 + 1) = nClr
Cells(i + 1, 1).Interior.ColorIndex = i + 16
Cells(i + 1, 2) = i
Cells(i + 1, 3) = i - 2 ^ 31
End If
Next
Range("B:C").Columns.AutoFit

End Sub

Regards,
Peter T

 

[Sam Kuo]

Hi Pete

A compile error occurs at the first line (i.e. Public Declare Function...)
when I run the test?
"Compile error: Constants, fixed-length strings, arrays, user-defined types
and Declare statments not allowed as Public members of object modules"

 

[Peter T]

The description you quoted clearly describes the error. So, if you want to
place the API in an object module change Public to Private. Obviously you
will only be able to call the API from within that module.

Class, Userform, (Work/Chart)Sheet and ThisWorkbook modules are all Object
modules. Why not place the code in a Normal module is not an object module.

Regards,
Peter T