Excel 2007 Combo Box - Developer Ribbon vs. VBA Module

Question

Could someone please explain to me the difference between the combo box that's available via the Developer Ribbon in Excel 2007 vs. the Combo Box control that's in the VBA editor? I cannot get this simple line of code to work using the Developer combo box:

MsgBox Combo1.Value

I've tied it to the change event and it seems to be syntactically correct (I'm not a VBA coder by any stretch).

Is the Developer Ribbon version some bastardized craptastic Microsoft shortcut?

What I'm trying to do is populate a second combo box based on the selection of the first combo box. I'd rather not build a case statement for every possible selection. Is this possible using the Developer ribbon version?

Answer 1

You are talking about the Insert button on the Developer tab correct? From that button you can add an ActiveX control or Form control. You're better off using the form controls if your new to programming as they will behave more in line with any Excel VBA reading you've done and the help file. With the Form controls you can right click and choose 'View Code' and/or 'Rename Control and Code'. Renaming the control allows you to address it in VBA however you like. e.g. - Combo1.value or myFavoriteCombo.value

That being said, to answer your question directly, be sure you know the controls full name. If you used a form control and it was the first one you put on the sheet it will be named Combo**Box**1. To get to the combobox's properties you have to walk through it's 'parent' sheet.

i.e.
MsgBox Sheet1.ComboBox1.value (using the sheet's code name)
or
MsgBox Worksheets("SheetName").ComboBox1.value (using the sheets name as it appears on the Excel tab)