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

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