Clear ComboBox Selection

I use a Shape Click to stimulate a population of combobox then use the combobox selection to take user to worksheet. Problem: The combobox does not clear after selection so it keeps compounding the same results:

Shape Code:

Sub Dashboard9_Click()
Dim wks As Worksheet
    For Each wks In Worksheets
        If wks.Name <> "HOME" And wks.Name <> "PSM-14-02A" And wks.Name <> "Recommendations" Then
            ActiveSheet.ComboBox1.AddItem wks.Name
        End If
    Next wks
End Sub

ComboBox Code:

Private Sub ComboBox1_Change()
    
    Sheets(ComboBox1.Value).Select

End Sub

Thanks in advance for any help

Hi jared,

I'm not sure if I completely understand your problem, other than the list replicating itself each time you do a 'Dashboard9_Click'. The following clears the combobox immediately after the 'Dashboard9_Click' (and inhibits an Event from being triggered by the Clear).

Try the following (tested and working using Excel 2003).


Shape Code:

Option Explicit
Public iGblInhibitComboBoxEvents As Boolean

Sub Dashboard9_Click()
Dim wks As Worksheet

    'Clear the combo box and do not allow a Combo Box Event
    'until after 'Clear' has completed
    iGblInhibitComboBoxEvents = True
    ActiveSheet.ComboBox1.Clear
    iGblInhibitComboBoxEvents = False
    
    For Each wks In Worksheets
        If wks.Name <> "HOME" And wks.Name <> "PSM-14-02A" And wks.Name <> "Recommendations" Then
            ActiveSheet.ComboBox1.AddItem wks.Name
        End If
    Next wks
End Sub

ComboBox Code:

Private Sub ComboBox1_Change()
    
    If iGblInhibitComboBoxEvents = False Then
      Sheets(ComboBox1.Value).Select
      MsgBox "Combo Box Select with value = '" & ComboBox1.Value & "'"
    End If

End Sub

Please note that the 'iGblInhibit' technique is very useful in preventing unwanted events from specific controls.

Lewis