VBA code For Combobox (ActiveX) In Sheet

[glerwell]

Hi

I have a ActiveX combobox in my sheet (combobox1). Instead of using the linked cell property can I use a VBA code to display a number instead of the actual value in the combobox.

The combobox consists of 3 options: "OVERALL","PLACED" and "BANK BARRIER". This is populated via a named range which is set in the properties.

So when:

"OVERALL" is selected then 1 is returned in AN24

"PLACED" is selected then 2 is returned in AN24

"BANK BARRIER" is selected then 3 is returned in AN24

Thanks

 

[Jonmo1]

Sure, on the sheet holding the combobox, put

Private Sub ComboBox1_Change()
Range("AN24").Value = ComboBox1.Value
End Sub

Change both instances of ComboBox1 to the actual name of the combobox

 

[kpark91]

In your combobox change event

If combobox.Value = "OVERALL" Then
    Range("AN24").Value = 1
ElseIf combobox.Value = "PLACED" Then
    Range("AN24").Value = 2
ElseIf combobox.Value = "BANK BARRIER"  Then
    Range("AN24").Value = 3
Else
    Range("AN24").Value = "kpark :)"
End If

 

[Jonmo1]

Wait, that puts the combobox value in the cell..

You wanted the number...

That would be

Private Sub ComboBox1_Change()
Range("AN24").Value = ComboBox1.ListIndex + 1
End Sub

 

[glerwell]

kpark got your working, I has to add 1 to combobox in the code.

jonmo I'm having a syntax error with yours?

 

[glerwell]

jonmo yours works too sorry, I copied the ________________ by mistake fromn your post!