Add a combobox to an Excel spreadsheet and reference value in VBA

[Guest]

I have the combox in my spreadsheet and have the VBA code running when there
is a change to the value, but I don't know how to refer to the combobox to
get the currently selected value

 

[Tom Ogilvy]

worksheets("Sheet1").OleObjects("Combobox1").Object.Value

or

if you are in the code module for sheet1 (the sheet where the combobox is
located)

combobox1.Value

 

[Guest]

My code is in the Modules section tied to the spreadsheet where I have the
function
ComboBox1_Change()

Neither of the syntaxes you listed work in this context, nor does
ActiveSheet.OLEObjects("ComboBox1").Object.Value

Any more ideas?

 

[Tom Ogilvy]

Private Sub ComboBox1_Click()
val1 = _
Worksheets("Sheet1").OLEObjects( _
"Combobox1").Object.Value
val2 = ComboBox1.Value
MsgBox val1 & " - " & val2
End Sub

worked fine for me.

Not sure what you are talking about. From your description, you may have
your code in wrong place, but it is too ambiguous to tell. If you right
click on the sheet tab with the combobox and select view code, that is where
the code should be.

 

[Guest]

If it matters, I'm using Office 2003

In the Visual Basic Project Window, I've placed the code in
VBAProject.Microsoft Excel Objects.Sheet1
VBAProject.Microsoft Excel Objects.ThisWorkbook
VBAProject.Modules.Module1
and am not able to get the code to work in any of these contexts.

The only thing I can think of is that what I'm typing in the Name Box for
the combobox is not exactly the object's name.
Is there any way in the Visual Basic watch window to see a list of the
objects on the Sheet, or what is the way to ensure the product's name is set
to what you specify?

Also, if you are willing to look at the spreadsheet, please email me at
(e-mail address removed)

Thanks,
Joe