VBA ComboBox

Posted on 2011-10-24
Last Modified: 2012-05-12

I have a Excel comboBox which popualtes a treeview with values from a source depending on what is selected in the comboBox.
I want the comboBox to fire an Event everytime a value is selected even if it is the same value which is already there. ie, it should refreash the treeView values if the same value is selected. What I'm finding is the ComboBox_Click() event is only triggered when the value in the comboBox is changed.
Is there a way to trigger an event each time a selection is made regardless of whether it has changed or not


Question by:victoriaharry
    LVL 41

    Expert Comment

    After the combobox has been selected (and you've done the activity you'd like that to do) you can deselect the selection of the combobox item by setting the combobox to NULL or "".  Since you are discussing making actual selections against the combo box, I worked with the _change trigger as opposed to the _click trigger, though logic could just as easily have been done against that as well.

    As combo boxes don't run as a result of application events, we need to create a global variable "runCombo as boolean" that advises whether its ok to run the combo _change event when a change is made as a result of deselecting the combo box.

    Here's the code for an example:

    In This Workbook (just populates a simple ActiveX combo on the worksheet, when the workbook is opened):

    Private Sub Workbook_Open()
        Call populateCombo
    End Sub

    Open in new window

    In a Public Module: Note, this sets the global variable "runCombo" to true, setting up the first _change routine
    Public runCombo As Boolean
    Sub populateCombo()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim myCombo As ComboBox
    Dim wk As Worksheet
        Set wkb = ThisWorkbook
        Set wks = wkb.ActiveSheet
        Set myCombo = Sheet1.ComboBox1
        For Each wk In wkb.Worksheets
            myCombo.AddItem wk.Name
        Next wk
        runCombo = True
    End Sub

    Open in new window

    In the Sheet1 codepage:
    Private Sub ComboBox1_Change()
        If runCombo Then
            MsgBox "combobox value selected was: " & ComboBox1.Value
            runCombo = False
            ComboBox1.Text = ""
            runCombo = True
        End If
    End Sub

    Open in new window

    Note, the code checks to see if runCombo is true - if it is, action can be taken against the selected combo box value.  Once that action is taken, runCombo is set to FALSE, then the combo box is deselected by setting the value to "", then runCombo is set again to TRUE.

    In this fashion, you can continually select the same item, over and over again, taking action against the same item, etc., but the combo box value will always display as deselected as a result of this logic.

    See attached example to play with.

    LVL 41

    Expert Comment

    To be complete, I've moved the code to the combobox_click trigger, so you can see it works here, as well.

    See attached,


    Author Comment


    Thanks for the quick reply. I can see that your method works however my preference would not be to set the value in the comboBox to "" as it is also required for a later database call.
    I'm guessing there is no way to keep the original selection in the comboBox as well as have it trigger an event if it is reselected.
    I could possibly move the value to another location before it is removed and access it from there but a cleaner way would be to have the original selection remain in the comboBox to avoid confusion with the users
    LVL 41

    Accepted Solution

    Your guess is correct.  The only way to allow the same selection be selected again AND have it trigger code, is to deselect, or select another option.  You could, however, show what was most recently selected, in a cell above the combo box (in the case of an embedded AcitveX control on the worksheet) or in a label above the combo box (in the case of a user form).

    Either approach should give you what you need to advertise to the user what they last selected.

    Make sense?

    LVL 11

    Expert Comment

    Unless you can edit the options another good way to handle this is to edit the dropdownarrow.  I've found most people use that whenever they want to see the list and so even before changing anything or displaying it updates the list for them.

    LVL 11

    Expert Comment

    This is the drop down button hook

    Private Sub ComboBox1_DropButtonClick()
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now