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

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

as far as I can see you've got to subclass the window calls to the
entire commandbar, and wait for the creation of a window with the class
"OfficeDropDown"

This is going to be extremely difficult.. if it can be done at all.

I'll give you the easy part:

lesson 1: finding the commandbar's windowhandle:

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

Function CbarHwnd(sCaption$) As Long
Dim h&(2)
'find Excel's main window
h(0) = FindWindowEx(0, 0, "XLMAIN", Application.Caption)
Do While h(0)
'loop all EXCEL2 children
h(1) = FindWindowEx(h(0), h(1), "EXCEL2", vbNullString)
Do
'search for the bar's caption
h(2) = FindWindowEx(h(1), h(2), "MsoCommandBar", sCaption)
If h(2) Then GoTo theEnd
Loop Until h(2) = 0
Loop
theEnd:
CbarHwnd = h(2)
End Function

lesson 2: setting up the subclassing..

start reading Chapter6 of this online book.. <g>
http://vb.mvps.org/hardcore/index.html

it's not much, but I hope it gives you a start..





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


SZ wrote :

> 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