API Call with VBA

[hilyete]

I added some calls to the API to remove the close X from my userforms. I recently found this VB code to allow changing specified Window colors, but I haven't been able to turn it into "VBA" code acceptable to Excel. I wonder if someone can look at it and tell me what might need to be changed. It uses a userform with 2 control buttons. I get an error msg highlighting the "SetSysColors 19" in:

SetSysColors 19, IndexArray(0), SavedColors(0)
stating that a function or sub is expected.. ?

Declare Function GetSysColor Lib "User" (ByVal nIndex%) As Long
Declare Sub SetSysColors Lib "User" (ByVal nChanges%,lpSysColor%,lpColorValues&)


 Declare Function GetSysColor Lib "User" (ByVal nIndex%) As Long
   ' Enter the following Declare statement as one, single line:
   Declare Sub SetSysColors Lib "User" (ByVal nChanges%, lpSysColor%,lpColorValues&)
   Const COLOR_BACKGROUND = 1
   Const COLOR_ACTIVECAPTION = 2
   Const COLOR_WINDOWFRAME = 6
   Dim SavedColors(18) As Long


 Sub Form_Load ()

      ' Save current system colors:
      For i% = 0 To 18
         SavedColors(i%) = GetSysColor(i%)
      Next i%

   End Sub

Sub Form1_Unload ()

      ' Restore system colors:
      ReDim IndexArray(18) As Integer
      For i% = 0 To 18
         IndexArray(i%) = i%
      Next i%
      SetSysColors 19, IndexArray(0), SavedColors(0)

   End Sub

Sub Command1_Click ()

      ' Change all display elements:
      ReDim NewColors(18) As Long
      ReDim IndexArray(18) As Integer
      For i% = 0 to 18
         NewColors(i%) = QBColor(Int(16 * Rnd))
         IndexArray(i%) = i%
      Next i%
      SetSysColors 19, IndexArray(0), NewColors(0)

   End Sub


Sub Command2_Click ()

      ' Change desktop, window frames, and active caption:
      ReDim NewColors(18) As Long
      ReDim IndexArray(18) As Integer
      For i% = 0 to 18
         NewColors(i%) = QBColor(Int(16 * Rnd))
         IndexArray(i%) = i%
      Next i%
      SetSysColors 19, IndexArray(0), NewColors(0)

   End Sub

[Jaafar Tribak]

hilyete.

It's not a good idea to change the default system colors using the above APIs as they affect all the windons in the system during the current session.

My guess is that you just need to change the colors of one or some specific elements of the UserForm UI window. If that is the case then here is an example of how to change the color of the userform caption text to Yellow.

 
Private Declare Function GetSysColor Lib "user32" _
(ByVal nIndex As Long) As Long
Private Declare Function SetSysColors Lib "user32" _
(ByVal nChanges As Long, _
lpSysColor As Long, _
lpColorValues As Long) As Long
 
Private Const COLOR_CAPTIONTEXT As Long = 9
Private DEFAULT_COLOR_CAPTIONTEXT As Long

Private Sub UserForm_Initialize()
 
    'Caption text color to yellow.
    DEFAULT_COLOR_CAPTIONTEXT = GetSysColor(COLOR_CAPTIONTEXT)
    SetSysColors 1, COLOR_CAPTIONTEXT, vbYellow
 
End Sub
 
Private Sub UserForm_Terminate()
 
    ' Restore Caption text color.
    SetSysColors 1, COLOR_CAPTIONTEXT, DEFAULT_COLOR_CAPTIONTEXT
 
End Sub

It is important to run the Terminate event to restore the default color(s).

If you need to modify the color of more than one element then you will have to store the default colors in a module level array for later retrieval.

Below is a list of the Constantes associated to different Window UI elements :

 
Const COLOR_SCROLLBAR = 0 'The Scrollbar colour
Const COLOR_BACKGROUND = 1 'Colour of the background with no wallpaper
Const COLOR_ACTIVECAPTION = 2 'Caption of Active Window
Const COLOR_INACTIVECAPTION = 3 'Caption of Inactive window
Const COLOR_MENU = 4 'Menu
Const COLOR_WINDOW = 5 'Windows background
Const COLOR_WINDOWFRAME = 6 'Window frame
Const COLOR_MENUTEXT = 7 'Window Text
Const COLOR_WINDOWTEXT = 8 '3D dark shadow (Win95)
Const COLOR_CAPTIONTEXT = 9 'Text in window caption
Const COLOR_ACTIVEBORDER = 10 'Border of active window
Const COLOR_INACTIVEBORDER = 11 'Border of inactive window
Const COLOR_APPWORKSPACE = 12 'Background of MDI desktop
Const COLOR_HIGHLIGHT = 13 'Selected item background
Const COLOR_HIGHLIGHTTEXT = 14 'Selected menu item
Const COLOR_BTNFACE = 15 'Button
Const COLOR_BTNSHADOW = 16 '3D shading of button
Const COLOR_GRAYTEXT = 17 'Grey text, of zero if dithering is used.
Const COLOR_BTNTEXT = 18 'Button text
Const COLOR_INACTIVECAPTIONTEXT = 19 'Text of inactive window
Const COLOR_BTNHIGHLIGHT = 20 '3D highlight of button
Const COLOR_2NDACTIVECAPTION = 27 'Win98 only: 2nd active window color
Const COLOR_2NDINACTIVECAPTION = 28 'Win98 only: 2nd inactive window color

Regards.

[hilyete]

Thanks Jaafar. Actually, I'm trying to set the selection color to a different color than the default blue, and include an option for the user to choose different colors. This is because I'm using a routine that creates a row/column highlight using the selection property that doesn't interfere with the conditional formatting. I found that code and felt that I could figure out how to adapt it to change only the selection color. Will your code do that for me?

[hilyete]

Thanks Jaafar, I have it working now.

[Jaafar Tribak]

By Selection, do you mean the cells highlighted by the user with the mouse/keyboard or the entire row/column of the active cell ?

Regards.

Ok . Glad you found the solution.