VBA-Excel ComboBox w/Popup Message-clear selection

[cmkarnes]

Hello, I have an Excel document that contains a Combo Box which contains numerous items. I used the Form Control.

There are two items on the list whereas if the user clicks on it, a message box pops up that this an invalid selection, and that they need to choose from the sub-series contained under that item instead.

My problem is that when the user closes the popup message, the item I don't want them choosing gets populated in the Combo Box then they have to delete it and then choose one of the other items. I would like it that when the user closes the popup message, that the combo box shows nothing and the user can choose from the list.

I need help with this item and just can't sort it out. I'm not the best at VBA. The current VBA looks like this:

Private Sub ComboBox8_Change()

If ComboBox8.Value = "23.0 - XXXXXXXX" Then MsgBox "Not a valid selection - please choose from 23 sub-series below"

If ComboBox8.Value = "25.0 - XXXXXXXXXXXXXXXX" Then MsgBox "Not a valid selection - please choose from 25 sub-series below"

End Sub

Thank you- CMK

 

[Luke M]

You said Forms, but based on the macro name, looks like this is actuall an ActiveX control. Either way, macro would be like:

Private Sub ComboBox8_Change()
Dim boolClear As Boolean

boolClear = False

With ComboBox8
    If .Value = "23.0 - XXXXXXXX" Then
        MsgBox "Not a valid selection - please choose from 23 sub-series below"
        boolClear = True
    ElseIf .Value = "25.0 - XXXXXXXXXXXXXXXX" Then
        MsgBox "Not a valid selection - please choose from 25 sub-series below"
        boolClear = True
    End If
   
    If boolClear Then
        'Turn off events, to prevent a recursive macro call
        'Then clear the combobox       
        Application.EnableEvents = False
        .Value = ""
        Application.EnableEvents = True
    End If
End With
End Sub

 

[cmkarnes]

Hi Luke, I can't thank you enough. I incorporated your changes, and this now works perfectly!! I would have never have come up with the boolClear on my own to make this thing work. Take care - thanks again - cmk