views:

940

answers:

1

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?

+2  A: 

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)

GollyJer
Yes, I used that button to insert my combo box. Apparently I chose the Form Control version. Why are there limits between the two? Is one better than the other? And is there a way for me to link two form-based combo boxes so that the selection I make in the first one defaults the selection in the other?
jhc
The code I posted should fix the error you were having. If so, please click the green checkbox to mark this as the answer. Here's a decent explanation of the differences between forms and ActiveX controls in Excel. http://peltiertech.com/WordPress/forms-controls-and-activex-controls-in-excel/ As for your other questions, Google is your friend. If you can't find answers using search, post another to StackOverflow.
GollyJer
Thanks GollyJer. appreciate the help.
jhc