Get Selected value of a Combobox

Question

I have a thousands of cells in an Excel worksheet which are ComboBoxes  The user will select one at random and populate it   How do I get the selected ComboBox value  Is there a way to trigger a function  i e  an event handler  when the ComboxBoxes has been selected

User · Answer

A simpler way to get the selected value from a ComboBox control is   Private Sub myComboBox Change     msgbox  You selected      myComboBox SelText End Sub

User · Answer

If you re dealing with Data Validation lists  you can use the Worksheet Change event   Right click on the sheet with the data validation and choose View Code   Then type in this   Private Sub Worksheet Change ByVal Target As Range       MsgBox Target Value  End Sub   If you re dealing with ActiveX comboboxes  it s a little more complicated   You need to create a custom class module to hook up the 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   Next  create another class module named CComboEvents   This will hold all of our CComboEvent instances and keep them in 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 ll need code to put all of your comboboxes into the class modules   You might put this in an Auto Open procedure so it happens whenever the workbook is opened  but that s up to you   You ll need a Public variable to hold an instance of CComboEvents  Making it Public will kepp it  and all of its children  in scope  You need them in scope so that the events are triggered  In the procedure  loop through all of the comboboxes  creating a new CComboEvent instance for each one  and adding that 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 a combobox is changed  the event will fire and  in this example  a message box will show   You can see an example at https   www dropbox com s sfj4kyzolfy03qe ComboboxEvents xlsm

User · Answer

Maybe you ll be able to set the event handlers programmatically  using something like  pseudocode   sub myhandler eventsource    process eventsource value  end sub  for each cell   cell setEventHandler myHandler    But i dont know the syntax for achieving this in VB VBA  or if is even possible

User · Answer

You can use the below change event to which will trigger when the combobox value will change   Private Sub ComboBox1 Change    your code here End Sub   Also you can get the selected value using below  ComboBox1 Value

[excel] Get Selected value of a Combobox

Examples related to excel

Examples related to vba

Examples related to combobox