change the Color of XL Formula Bar

[BLUE_SEA]

Hi all,

I am trying to change the Excel formula bar color to red and I am using the following code but unfortunately doesn't work:


Code :

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long

Declare Function FillRect Lib "user32" _
(ByVal hdc As Long, lpRect As RECT, ByVal hBrush As Long) As Long


Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal _
lpsz2 As String) As Long

Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long

Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hdc As Long) As Long


Sub ChangeFormulaBarColor()

Dim recto As RECT
Dim lngFrmBarHndle As Long
Dim lngFrmBarDC As Long

' Get the XL formula Bar window handle
lngFrmBarHndle = FindWindowEx(Application.hwnd, 0, "EXCEL<", vbNullString)

' Get its rectangle metrics
GetWindowRect lngFrmBarHndle, recto

' Get its window DC
lngFrmBarDC = GetDC(lngFrmBarHndle)

' Fill the Formula with Red Color
FillRect lngFrmBarDC, recto, &HFFFFFF + 1

' Release DC
ReleaseDC lngFrmBarHndle, lngFrmBarDC

End Sub
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Am I using the FillRect Function incorrectly ?


Regards.

[RobDog888]

Your code basically looks ok but since your trying to do this on a third party program you will need to subclass it using a C++ dll. When your code fires the toolbar get repainted again from Excel, thus overwritting your color.

There is a codebank thread with an example of subclassing other programs in case your interested.[/color]

[BLUE_SEA]

Your code basically looks ok but since your trying to do this on a third party program you will need to subclass it using a C++ dll. When your code fires the toolbar get repainted again from Excel, thus overwritting your color.

There is a codebank thread with an example of subclassing other programs in case your interested.[/color]

Thanks for the prompt reply.

Is there any other solution apart from using the C dll ? Any other API functions\hacks that we can use in VB without having to resort to C or external DLLs ?

I have used the SetBKcolor API function before to achieve this but it doesn't work consistently : ie It only works when you deactivate the XL app and then reactivate it again !

Regards.

[RobDog888]

Right, that is from the app not being subclased so you can intercept the WM_PAINT message. Thats when you would make the call to your procedure to paint the toolbar with the color you want.

I do not believe it is possible to do it in VB since you can not access another threads process without using a C++ dll.

Its not that hard using the dll. Here is the thread in question - http://www.vbforums.com/showthread.php?t=322261

[BLUE_SEA]

Right, that is from the app not being subclased so you can intercept the WM_PAINT message. Thats when you would make the call to your procedure to paint the toolbar with the color you want.

I do not believe it is possible to do it in VB since you can not access another threads process without using a C++ dll.

Its not that hard using the dll. Here is the thread in question - http://www.vbforums.com/showthread.php?t=322261

Thanks again,

Looks promising

I have never used any DLLs less so a C++ one ! ... however I'll give this a try and post back .

Regards.