Results 1 to 5 of 5

API Call with VBA

This is a discussion on API Call with VBA within the Excel Questions forums, part of the Question Forums category; I added some calls to the API to remove the close X from my userforms. I recently found this VB ...

  1. #1
    Board Regular hilyete's Avatar
    Join Date
    Aug 2009
    Location
    Nashville, TN
    Posts
    291

    Default API Call with VBA

    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 by hilyete; Jan 17th, 2010 at 10:32 PM.

  2. #2
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,950

    Default Re: API Call with VBA

    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.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #3
    Board Regular hilyete's Avatar
    Join Date
    Aug 2009
    Location
    Nashville, TN
    Posts
    291

    Default Re: API Call with VBA

    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?

  4. #4
    Board Regular hilyete's Avatar
    Join Date
    Aug 2009
    Location
    Nashville, TN
    Posts
    291

    Default Re: API Call with VBA

    Thanks Jaafar, I have it working now.

  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,950

    Default Re: API Call with VBA

    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.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com