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

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