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

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.

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


"SZ" wrote in message
oups.com...
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,

I'll keep that one. ultra cool!


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


Rob van Gelder wrote :

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.

--
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!

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


"Sergio Zanini" wrote in message
...
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!

Sergio Zanini wrote:
Is there a way of
including a %complete number on the msctls_progress32 Progressbar

No. From the SDK:

"WM_GETFONT ... returns the handle to the current font. The progress
bar does not currently draw text, so sending this message has no effect
on the control."

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

Eek! It is very confusing to change the value of the PBS_SMOOTH
constant. I believe these are the correct values required:

Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_CHILD As Long = &H40000000
Private Const PBS_SMOOTH As Long = 1
Private Const PBS_MARQUEE As Long = 8

I have a demo for drawing a progress bar using these APIs, if you are
interested, but here's the snippet:

' Progressbar's style
Dim lngStyleProgress As Long
lngStyleProgress = WS_VISIBLE Or WS_CHILD
If SmoothBackground Then
lngStyleProgress = lngStyleProgress Or PBS_SMOOTH
Else
lngStyleProgress = lngStyleProgress Or PBS_MARQUEE
End If

' Create progressbar window
m_hProgress = _
CreateWindowEx(0, PROGRESS_CLASS_NAME, _
vbNullString, lngStyleProgress, lngLeft, _
lngTop, Width, Height, hwnd, 0, 0, 0)

Jamie.

--

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

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


"Jamie Collins" wrote in message
oups.com...

Sergio Zanini wrote:
Is there a way of
including a %complete number on the msctls_progress32 Progressbar

No. From the SDK:

"WM_GETFONT ... returns the handle to the current font. The progress
bar does not currently draw text, so sending this message has no effect
on the control."

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

Eek! It is very confusing to change the value of the PBS_SMOOTH
constant. I believe these are the correct values required:

Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_CHILD As Long = &H40000000
Private Const PBS_SMOOTH As Long = 1
Private Const PBS_MARQUEE As Long = 8

I have a demo for drawing a progress bar using these APIs, if you are
interested, but here's the snippet:

' Progressbar's style
Dim lngStyleProgress As Long
lngStyleProgress = WS_VISIBLE Or WS_CHILD
If SmoothBackground Then
lngStyleProgress = lngStyleProgress Or PBS_SMOOTH
Else
lngStyleProgress = lngStyleProgress Or PBS_MARQUEE
End If

' Create progressbar window
m_hProgress = _
CreateWindowEx(0, PROGRESS_CLASS_NAME, _
vbNullString, lngStyleProgress, lngLeft, _
lngTop, Width, Height, hwnd, 0, 0, 0)

Jamie.

--

Rob van Gelder wrote:
Jamie, how did you get Marquee working? It wouldn't refresh properly
for me.

Hi Rob,
Refresh? Do you mean change the style of an existing window from smooth
to marquee? I only know how to set the style with CreateWindow. Snippet
expanded:

Option Explicit

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private 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

Private Declare Function CreateWindowEx _
Lib "user32" Alias "CreateWindowExA" _
(ByVal lngStyleEx As Long, ByVal lpClassName As String, _
ByVal lpWindowName As String, ByVal lngStyle As Long, _
ByVal X As Long, ByVal Y As Long, ByVal nWidth As Long, _
ByVal nHeight As Long, ByVal hWndParent As Long, _
ByVal hMenu As Long, ByVal hInstance As Long, _
lpParam As Any) As Long

Private Declare Function SendMessage _
Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long

Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Private Declare Function DestroyWindow _
Lib "user32" (ByVal hwnd As Long) As Long

Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_CHILD As Long = &H40000000
Private Const PBS_SMOOTH As Long = 1
Private Const PBS_MARQUEE As Long = 8
Private Const WM_USER As Long = &H400
Private Const PBM_SETPOS As Long = WM_USER + 2
Private Const PROGRESS_CLASS_NAME As String = _
"msctls_progress32"

Sub test()

Const lngLeft As Long = 1
Const lngTop As Long = 5
Const Width As Long = 190
Const Height As Long = 10
Const SmoothBackground As Boolean = False

Dim m_hProgress As Long

' Get hWnd for Excel's statusbar
Dim hwnd As Long ' Get hWnd for Excel's statusbar
hwnd = FindWindow(vbNullString, Application.Caption)
hwnd = FindWindowEx(hwnd, 0, "EXCEL4", vbNullString)

' Progressbar's style
Dim lngStyleProgress As Long
lngStyleProgress = WS_VISIBLE Or WS_CHILD
If SmoothBackground Then
lngStyleProgress = lngStyleProgress Or PBS_SMOOTH
Else
lngStyleProgress = lngStyleProgress Or PBS_MARQUEE
End If

' Create progressbar window
m_hProgress = _
CreateWindowEx(0, PROGRESS_CLASS_NAME, _
vbNullString, lngStyleProgress, lngLeft, _
lngTop, Width, Height, hwnd, 0, 0, 0)

' Show progress
SendMessage _
m_hProgress, PBM_SETPOS, _
66, 0
DoEvents

' Pause 2 seconds
Sleep 2000

' Destroy progressbar window
DestroyWindow m_hProgress

End Sub


Jamie.

--

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.


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


"Jamie Collins" wrote in message
oups.com...

Rob van Gelder wrote:
Jamie, how did you get Marquee working? It wouldn't refresh properly
for me.

Hi Rob,
Refresh? Do you mean change the style of an existing window from smooth
to marquee? I only know how to set the style with CreateWindow. Snippet
expanded:

Option Explicit

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private 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

Private Declare Function CreateWindowEx _
Lib "user32" Alias "CreateWindowExA" _
(ByVal lngStyleEx As Long, ByVal lpClassName As String, _
ByVal lpWindowName As String, ByVal lngStyle As Long, _
ByVal X As Long, ByVal Y As Long, ByVal nWidth As Long, _
ByVal nHeight As Long, ByVal hWndParent As Long, _
ByVal hMenu As Long, ByVal hInstance As Long, _
lpParam As Any) As Long

Private Declare Function SendMessage _
Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long

Private Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)

Private Declare Function DestroyWindow _
Lib "user32" (ByVal hwnd As Long) As Long

Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_CHILD As Long = &H40000000
Private Const PBS_SMOOTH As Long = 1
Private Const PBS_MARQUEE As Long = 8
Private Const WM_USER As Long = &H400
Private Const PBM_SETPOS As Long = WM_USER + 2
Private Const PROGRESS_CLASS_NAME As String = _
"msctls_progress32"

Sub test()

Const lngLeft As Long = 1
Const lngTop As Long = 5
Const Width As Long = 190
Const Height As Long = 10
Const SmoothBackground As Boolean = False

Dim m_hProgress As Long

' Get hWnd for Excel's statusbar
Dim hwnd As Long ' Get hWnd for Excel's statusbar
hwnd = FindWindow(vbNullString, Application.Caption)
hwnd = FindWindowEx(hwnd, 0, "EXCEL4", vbNullString)

' Progressbar's style
Dim lngStyleProgress As Long
lngStyleProgress = WS_VISIBLE Or WS_CHILD
If SmoothBackground Then
lngStyleProgress = lngStyleProgress Or PBS_SMOOTH
Else
lngStyleProgress = lngStyleProgress Or PBS_MARQUEE
End If

' Create progressbar window
m_hProgress = _
CreateWindowEx(0, PROGRESS_CLASS_NAME, _
vbNullString, lngStyleProgress, lngLeft, _
lngTop, Width, Height, hwnd, 0, 0, 0)

' Show progress
SendMessage _
m_hProgress, PBM_SETPOS, _
66, 0
DoEvents

' Pause 2 seconds
Sleep 2000

' Destroy progressbar window
DestroyWindow m_hProgress

End Sub


Jamie.

--

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 wrote:
The marquee is a blue thing than bounces back and forth along the
frame.

Ah, then I've misunderstood what marquee means. I knew that when you
copy an Excel cell you get a marquee effect but thought it referred to
the separated border. Now I realize it refers to the movement. Thanks
for the epiphany!

Looking at the progress bar section of the SDK again, I note you must
specify Comctl32.dll version 6 in a manifest. Hmm, I think I'll leave
that one to you <g!

Jamie.

--

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

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


"Michel Pierron" wrote in message
...
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