VBA - Get Selected Combobox Value

Question

I have thousands of cells in an Excel worksheet that are ComboBoxes. The user will select one at random and fill it.

How to get the selected ComboBox value? Is there a way to call a function (i.e. an Event Handler) when ComboxBoxes is selected?

Answer 1

If you are dealing with data validation lists, you can use the Worksheet_Change event. Right-click on the data verification sheet and select "View Code." Then enter here:

Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Value End Sub 

If you are dealing with ActiveX lists, this is a little trickier. You need to create a custom class module for hooking events. First create a class module named CComboEvent and put this code in it.

 Public WithEvents Cbx As MSForms.ComboBox Private Sub Cbx_Change() MsgBox Cbx.Value End Sub 

Then create another class called CComboEvents. This will contain all our instances of CComboEvent and keep them in its scope. Put this code in CComboEvents.

 Private mcolComboEvents As Collection Private Sub Class_Initialize() Set mcolComboEvents = New Collection End Sub Private Sub Class_Terminate() Set mcolComboEvents = Nothing End Sub Public Sub Add(clsComboEvent As CComboEvent) mcolComboEvents.Add clsComboEvent, clsComboEvent.Cbx.Name End Sub 

Finally, create a standard module (not a class module). You will need code to put all your comboboxes into class modules. You can put this in the Auto_Open procedure so that it happens when the workbook is open, but it is up to you.

You will need the Public variable to store the CComboEvents instance. The publication will be dedicated to this and all of his children. You need them in scope for events to fire. In this procedure, skip all the drop-down lists by creating a new CComboEvent instance for each of them and adding it to CComboEvents.

 Public gclsComboEvents As CComboEvents Public Sub AddCombox() Dim oleo As OLEObject Dim clsComboEvent As CComboEvent Set gclsComboEvents = New CComboEvents For Each oleo In Sheet1.OLEObjects If TypeName(oleo.Object) = "ComboBox" Then Set clsComboEvent = New CComboEvent Set clsComboEvent.Cbx = oleo.Object gclsComboEvents.Add clsComboEvent End If Next oleo End Sub 

Now, whenever the combo box changes, the event fires and the message box is displayed in this example.

You can see an example at https://www.dropbox.com/s/sfj4kyzolfy03qe/ComboboxEvents.xlsm

Answer 2

You can use the change event below, which will fire when the combobox value changes.

 Private Sub ComboBox1_Change() 'your code here End Sub 

You can also get the selected value using below

 ComboBox1.Value 

Answer 3

You might be able to programmatically configure event handlers using something like (pseudo-code)

 sub myhandler(eventsource) process(eventsource.value) end sub for each cell cell.setEventHandler(myHandler) 

But I do not know the syntax to achieve this in VB / VBA, or if possible.