[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA ComboBox

Posted on 2011-10-24
6
Medium Priority
?
469 Views
Last Modified: 2012-05-12
Hi,

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

Thanks

Gavin
0
Comment
Question by:victoriaharry
  • 3
  • 2
6 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37022039
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.

Dave
comboFun-r1.xls
0
 
LVL 42

Expert Comment

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

See attached,

Dave
comboFun-r2.xls
0
 

Author Comment

by:victoriaharry
ID: 37022088
Hi,

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
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37022094
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?

Dave
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37031992
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.

0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37032009
This is the drop down button hook

Private Sub ComboBox1_DropButtonClick()

End Sub

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

873 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