API Call with VBA

hilyete

Active Member
Joined
Aug 19, 2009
Messages
293
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.. ?


Code:
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
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.

Code:
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 :

Code:
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top