VBA - Adding control objects and the properties window

Question

So here's what I'm trying to do, sounds simple and I've done it before on other computers but there is a hang up on the one I'm using now.

I have added a combo box to an excel sheet.

I have been able to use the 'format object' option (right clicking on the cbo) to add data to the combo box through linking it to cells.

My difficulty comes when I begin coding.

The only way I've been able to associate any code with the combo box is by right clicking on it and selecting "assign macro / New..." 

When I try and reference the object, using the name automatically generated when going through the assign macro process, nothing happens.  It's as if the object doesn't exist.

Further more, I can't get the properties for the combo box to show up in either the excel or vb view.  The only objects the programs let me see properties for are the sheets (1 through 3 initially) and the work book.  No objects other than those just mentioned show up in the project view either.

Any thoughts?

Is there some kind of a child lock type of set up which limits the changes that are able to be made to an object in excel?

Answer 1

Did you add the form control version of the combo box or the activeX version?  Try using the ActiveX combo box.  You'll then be able to see all the properties in the VBE.

HTH,

Eric

Answer 2

It sounds like you have a Forms Control ComboBox on your worksheet. You would get to its properties via the Shapes object. Some properties you can get to directly, for example...

MsgBox "ComboBox width is " & ActiveSheet.Shapes("Drop Down 2").Width

Others require you to go through the OLEFormat.Object property of the Shape to get to them, for example...

MsgBox "Select item in list is " & ActiveSheet.Shapes("Drop Down 2").OLEFormat.Object.Value

or...

MsgBox "List index number for selected item is " & ActiveSheet.Shapes("Drop Down 2").OLEFormat.Object.ListIndex

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.