BackColor, Forecolor, Bold, and Font Size for ComboBox in Commandbar

[SZ]

I have created a ComboBox in an Excel Commandbar. The ComboBox which
is an CommandBarComboBox object displays a white background and black
text. The properties of this object do not expose colors or Font
changes. (In contrast to the ComboBox in a UserForm which does)
I would like to spiff up this control by adding BackColor, ForeColor
and changing the Font and Font Bold property.

It seems that API calls are the way to go, but in using hwnd finders
such as WINSPY, I am not able to find the hwnd of the ComboBox that I
created in the CommandBar; thus I cannot 'see' the hwnd of a comboBox
in the Commandbar! (I can see the Commandbar) How do I find the
object in the Excel CommandBar or how do I reference the object such
that I could change its properties such as Color, Font etc.?

I have read up about OwnerDrawn controls and most code is C++ oriented
with MFC thrown in. I dont want to be a programming specialist in
C++/MFC but I can follow the VB code/process if there was a process to
make these changes for use in the Excel VBA environment.

Can anyone help with Code Example(s) that would identify the ComboBox
object in the CommandBar and then using the identifier to change the
Color and Font properties - I would be very grateful.

An extension of this request is how to identify/address the id of a
CommandButton, ListBox on a UserForm - I have considered the SetFocus
command when the UserForm has opened, but that is where I am at.
In terms of practicality, this request is to advance my knowledge of
API use/API flexibility of application of Controls in Excel in the VBA
environment.

TIA
SZ

 

[Rob van Gelder]

I have an example on my website for Progress Bar on CommandBar.
I'm not that pleased about the way I found the hwnd of the edit box.

Still, you might be able to use it for your purposes.

 

[keepITcool]

Rob,

I'll keep that one. ultra cool!


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

 

[Sergio Zanini]

Rob,
Thanks for the head start - the code you refer to finds the handle of
the object in the CommandBar - so easy, one can say (when you know
how!); as per your example and using a rectangle, I can color the
Listbox/Combobox Backcolor - now can you help with placing text on top
of the rectangle and then changing the Text ForeColor?
In addition, is there another type of ProgressBar that has the PerCent
complete on it? The one used in your code ("msctls_progress32") does
not exhibit the percent complete numeber value. Is there a way of
including a %complete number on the msctls_progress32 Progressbar?


extract from your code...
"GetClientRect hWndT, rct
hWnd = CreateWindowEX(0, "msctls_progress32", "", WS_CHILD Or
WS_VISIBLE Or PBS_SMOOTH, rct.Left, rct.Top, rct.Right - rct.Left + 2,
rct.Bottom - rct.Top + 1, hWndT, 0, 0, 0)
SendMessage hWnd, PBM_SETBKCOLOR, 0, ByVal vbWhite "
.........................................
By changing the last line to:
SendMessage hWnd, PBM_SETBKCOLOR, 0, RGB(0,0,255)
one can change the backcolor at will.
Can the BackColor placed on the rectangle that results [(RGB(0,0,255) =
Blue ] be made transparent?

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
As an aside,
(1) in your own code if you include the next two lines you can change
the BarColor on the ProgressBar:-
Const PBM_SETBARCOLOR = (WM_USER + 9) 'Bar Color
SendMessage hwnd, PBM_SETBARCOLOR, 0, ByVal RGB(0, 255, 0)
...this results in a green bar.

(2) if you change Const PBS_SMOOTH = 2 to
Const PBS_SMOOTH = 3 you will get a smooth 'fuel gauge' type bar.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Rob van Gelder]

The progressbar control which comes with comctl32 does not have an option
for displaying the percentage.
You dont need to set PBS_SMOOTH to 3 - in fact, you shouldn't. Just remove
Or PBS_SMOOTH from the code.

You have the handle of the window, you can call GetDC for the device context
and make your own progress bar!

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal
hdc As Long) As Long
Private Declare Function FillRect Lib "user32" (ByVal hdc As Long, ByRef
lpRect As RECT, ByVal hBrush As Long) As Long
Private Declare Function CreateSolidBrush Lib "gdi32" (ByVal crColor As
Long) As Long
Private Declare Function DeleteObject Lib "gdi32" (ByVal hObject As Long) As
Long
Private Declare Function GetSysColor Lib "user32" (ByVal nIndex As Long) As
Long

Const COLOR_HIGHLIGHT = 13, COLOR_WINDOW = 5

eg. This is a modification of the last section using the code from my
website.

If bln Then
hdc = GetDC(hWndT)

GetClientRect hWndT, rctArea
rctArea.Left = rctArea.Left + 1
rctArea.Right = rctArea.Right - 1
rctArea.Top = rctArea.Top + 1
rctArea.Bottom = rctArea.Bottom - 1

lngBrush = CreateSolidBrush(GetSysColor(COLOR_WINDOW))
FillRect hdc, rctArea, lngBrush
DeleteObject lngBrush

lngBrush = CreateSolidBrush(GetSysColor(COLOR_HIGHLIGHT))

For i = 1 To 10000
Range("A1").Value = i 'Do Something

rct = rctArea
rct.Right = rct.Left + (rct.Right - rct.Left) * i / 10000
FillRect hdc, rct, lngBrush
Next

DeleteObject lngBrush
lngBrush = CreateSolidBrush(GetSysColor(COLOR_WINDOW))
FillRect hdc, rctArea, lngBrush
DeleteObject lngBrush

ReleaseDC 0, hdc
End If


Use TextOut API for writing text out.
You'll probably need to set up the font before writing out.
Private Declare Function TextOut Lib "gdi32" Alias "TextOutA" (ByVal hdc As
Long, ByVal X As Long, ByVal Y As Long, ByVal lpString As String, ByVal
nCount As Long) As Long



--
Rob van Gelder - http://www.vangelder.co.nz/excel

Rob,
Thanks for the head start - the code you refer to finds the handle of
the object in the CommandBar - so easy, one can say (when you know
how!); as per your example and using a rectangle, I can color the
Listbox/Combobox Backcolor - now can you help with placing text on top
of the rectangle and then changing the Text ForeColor?
In addition, is there another type of ProgressBar that has the PerCent
complete on it? The one used in your code ("msctls_progress32") does
not exhibit the percent complete numeber value. Is there a way of
including a %complete number on the msctls_progress32 Progressbar?


extract from your code...
"GetClientRect hWndT, rct
hWnd = CreateWindowEX(0, "msctls_progress32", "", WS_CHILD Or
WS_VISIBLE Or PBS_SMOOTH, rct.Left, rct.Top, rct.Right - rct.Left + 2,
rct.Bottom - rct.Top + 1, hWndT, 0, 0, 0)
SendMessage hWnd, PBM_SETBKCOLOR, 0, ByVal vbWhite "
........................................
By changing the last line to:
SendMessage hWnd, PBM_SETBKCOLOR, 0, RGB(0,0,255)
one can change the backcolor at will.
Can the BackColor placed on the rectangle that results [(RGB(0,0,255) =
Blue ] be made transparent?

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
As an aside,
(1) in your own code if you include the next two lines you can change
the BarColor on the ProgressBar:-
Const PBM_SETBARCOLOR = (WM_USER + 9) 'Bar Color
SendMessage hwnd, PBM_SETBARCOLOR, 0, ByVal RGB(0, 255, 0)
..this results in a green bar.

(2) if you change Const PBS_SMOOTH = 2 to
Const PBS_SMOOTH = 3 you will get a smooth 'fuel gauge' type bar.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Rob van Gelder]

Jamie, how did you get Marquee working? It wouldn't refresh properly for me.

 

[Rob van Gelder]

There are two modes for progressbar. One is normal 0 -> 100% mode. The other
is marquee.

Marquee mode is handy when you dont know how long an operation is going to
take - such as a database query.
The marquee is a blue thing than bounces back and forth along the frame.

You get two styles, smooth and not smooth (not smooth being brick looking)

When I tried to implement Marquee mode a while ago, the window wouldn't
refresh for me.
It would look like it was stuck and never move.
If I let it run for 30 seconds or so and dragged a window overtop of Excel,
then the marquee changed positions. If I dragged it on and off really
quickly then I could tell the marquee was working. It had something to do
with the window refreshing / updating - I didn't get to the bottom of it.

Since Marquee mode is such a nice indicator for users, I think I'll take
another shot soonish.

 

[Michel Pierron]

Hi Rob,
Warning:
PBS_MARQUEE is only for Version 6.0 or later.
Comctl32.dll version 6 is not redistributable but it is included in
Microsoft Windows XP or later.
MP

 

[Rob van Gelder]

Yes, I believe that was one of the issues.

I've created a marquee by hand in the past - I may just find that code
instead.

Thanks Michel