Hi

I'm trying to set the properties of a ComboBox (from the Control
Toolbox, not the FOrms toolbox) in VBA code. This is because I'm
trying to implement the solution (from contextures.com) to tiny
unreadable data-validation "combos".

Background
- Zooming is not possible - just got too much to fit onto the sheet
- The solution consists of having a combo box shunted out of sight
"elsewhere" on the sheet: when the user clicks in the cell, the combo
box is shifted onto the cell, made visible etc.
- In my adapted solution there are various different combo boxes, each
getting their list data from a different range. The combo box actually
shown depends on the cell the user clicks in.

Trouble is I want to dynamically set various properties of the combo
(e.g. font, Listwidth) in VBA - but in the code example the combo is
referenced as an "OLEObject" (urrghh! I thought we'd got beyond that
"OLEObject" stuff by now!). Naturally this object type doesn't expose
any combobox-specific properties.

Is there a way I can set an object reference to a Combobox object type
in Excel? I've found a ComboBox class in the Object Browser, but this
appears to be the MSForms Combo box. Or is there any reason why I
can't use an MSForms combo?

I'm a very experienced Access programmer, but Excel controls are new to
me.

thanks for any suggestions.


Seb

I thought the Control Toolbox controls were implemented Office wide. So, if
Access doesn't use that type of control, I guess the implementation is not
really across all Office products.

In any case, to get to the combobox I "bootstrapped" my way to
activesheet.shapes(1).oleformat.object.object

For more on this see the incomplete
Case Study =3F Embedded and Linked Objects
http://www.tushar-mehta.com/excel/vb...20or%20linked%
20objects.htm


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hi

I'm trying to set the properties of a ComboBox (from the Control
Toolbox, not the FOrms toolbox) in VBA code. This is because I'm
trying to implement the solution (from contextures.com) to tiny
unreadable data-validation "combos".

Background
- Zooming is not possible - just got too much to fit onto the sheet
- The solution consists of having a combo box shunted out of sight
"elsewhere" on the sheet: when the user clicks in the cell, the combo
box is shifted onto the cell, made visible etc.
- In my adapted solution there are various different combo boxes, each
getting their list data from a different range. The combo box actually
shown depends on the cell the user clicks in.

Trouble is I want to dynamically set various properties of the combo
(e.g. font, Listwidth) in VBA - but in the code example the combo is
referenced as an "OLEObject" (urrghh! I thought we'd got beyond that
"OLEObject" stuff by now!). Naturally this object type doesn't expose
any combobox-specific properties.

Is there a way I can set an object reference to a Combobox object type
in Excel? I've found a ComboBox class in the Object Browser, but this
appears to be the MSForms Combo box. Or is there any reason why I
can't use an MSForms combo?

I'm a very experienced Access programmer, but Excel controls are new to
me.

thanks for any suggestions.


Seb

Hi Tushar

Thanks for your post. As it happens I've worked out that the
OLEObject.Object gives me access to a Combobox object of some type. It
seems to be the one listed in the Object Browser.

Access doesn't implement the same controls - they do look the same, but
have different properties. For example, the Excel one has a
ListFillRange property, which is entirely appropriate to Excel - the
Access one has RowsourceType, Rowsource and ControlSource properties,
which make sense in Access. There are other property differences as
well.

regards


seb