Change ComboBox properties in VBA?

[sebthirlway]

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

 

[Tushar Mehta]

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/vba/vba-embedded or linked%
20objects.htm


--
Regards,

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

 

[sebthirlway]

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