ComboBox ActiveX: Running Simple Code

[Wamhoi]

I created a ComboBox ActiveX directly on Sheet7 and a command button to execute an action based on variable chosen. I already defined the variables based on range in the outskirts of the sheet. I created the following vba code situated in the command button but have no luck.

Private Sub CommandButton10_Click()
Application.ScreenUpdating = False

Dim lItem As Long
For lItem = 0 To sheets7.ComboBox1.ListCount

If sheets7.ComboBox1.Selected(lItem) = True Then MsgBox "Hi"

Next
End Sub


Any Ideas?

 

[xenou]

1)
I'm not seeing that "Selected" as a property of comboboxes. That is a property of listboxes. So you may be using a property that doesn't exist.

2)
sheets7 looks odd. might be Sheet7. You should see intellisense kicking in with your objects if they are correctly referenced.

3)
No need to turn screen updating off

 

[Wamhoi]

I updated my code but still no success

Private Sub CommandButton10_Click()
Dim lItem As Long
Dim Combo As MSForms.ComboBox
Set Combo = ActiveWorkbook.Sheets("Individual Summary").ComboBox1
For lItem = 0 To ComboBox1.ListCount
If Combo.Selected(lItem) = True Then MsgBox "Hi"
Exit For
Next
End Sub

 

[Norie]

Where did you put the code and why are you looping through the combobox?

Can't you just find out which item has been selected by using Combo.Value or similar?

PS Why the Click event? Have you tried the default event Change?

 

[xenou]

I'm not sure comboboxes have a selected property. Use the Value property. You can find out if anything is selected by checking the value when you first open the sheet - that's the value if the user chooses nothing. Probably Null or an empty string. Or perhaps you can check if there is a selection this way:

If Len(ComboBox1.Value & "") = 0 Then
Msgbox "Nothing Selected"
Else
Msgbox ComboBox1.Value
End If