4

What event is triggered when user selects value from drop down ComboBox (Active X). How it can be defined in VBA. I would like to trigger macro when value is selected from drop down.

Community
  • 1
  • 1
Madhan
  • 81
  • 1
  • 2
  • 7
  • `ComboBox_Change` event is triggered when a user selects a value. `ActiveX` have their own events you can use to run routines. I see you have previous questions related to this as well. Might as well add illustration or screen shot of your issue or what you're trying to achieve. That will add clarity and will attract more answers. – L42 Mar 31 '15 at 07:35
  • Combobox_change event triggered as soon as i started to type. (Auto search drop down). So cannot use this property to trigger event. I am using ActiveX combo box – Madhan Mar 31 '15 at 08:23
  • It has to be like when user selects value from drop down then move focus to next drop down else show msg:"Select value from drop down" – Madhan Mar 31 '15 at 08:26

2 Answers2

3

ComboBox_Click() is the event you are looking for.

kolcinx
  • 2,183
  • 1
  • 15
  • 38
  • This event triggers as soon as you click on the object. I think not applicable to what the OP wants. – L42 Mar 31 '15 at 09:12
  • From my testing, it works only after selecting item from dropdown (working in Excel 2010). – kolcinx Mar 31 '15 at 09:13
  • Ok my bad. You are correct. But again, it will not work if OP decides to type the value in the Combo box to use the auto complete feature of the control. – L42 Mar 31 '15 at 09:20
  • I agree. In that case some combination of events + conditions will be needed, but we are beyond the question scope. – kolcinx Mar 31 '15 at 09:23
  • I think the problem is when ever the excel sheet is updated to any value then last used drop down automatically pops up! – Madhan Mar 31 '15 at 10:05
  • I'm sorry, can you explain that in other words? Maybe update the original question with current state. – kolcinx Mar 31 '15 at 10:12
  • Is there a wayto disable dropdown(Only Drop down not combobox) and enable dropdown on event. Like Disable when lost focus enable when got focus – Madhan Mar 31 '15 at 10:17
  • @Branislav Kollár Is there a way i can send the file itself? – Madhan Mar 31 '15 at 10:18
  • There is a property of combobox exactly for what you are asking about. You lucky ... :D `ComboBox.ShowDropButtonWhen = fmShowDropButtonWhenFocus` – kolcinx Mar 31 '15 at 10:23
  • I'm a bit confused now, we have a communication delay. Is it working as intedned now, or do we have some work to do? – kolcinx Mar 31 '15 at 10:44
  • Still problem persists. "ComboBox.ShowDropButtonWhen = fmShowDropButtonWhenFocus" is just making the dropdown symbol to go away. Its not making dropdown go away. – Madhan Mar 31 '15 at 10:47
  • We are talking about the Countrydd and Divisiondd comboboxes, right? I can see, that their dropdown lists persist, even when changing sheets. Is this the issue you are trying to amend? – kolcinx Mar 31 '15 at 11:43
  • Yes. Drop down persist when ever we change any value in the cell. But can able to change value in cell when we open the excel and try to edit cell(With out using dropdown). I am missing something when we first use the drop down. – Madhan Mar 31 '15 at 11:51
  • I think we need a **new question**, with **specific definition of problem, misbehaviour and desired behaviour**. I don't think I understand what is going on in there. Someone else maybe can help. I will give it a shot, though. – kolcinx Mar 31 '15 at 12:03
  • Thanks Branislav Kollár – Madhan Mar 31 '15 at 12:04
0

If you're using a ComboBox control in a UserForm, it usually have an AfterUpdate Event.
If you're using an ActiveX Control ComboBox in a Sheet, you can try LostFocus Event.

Private Sub ComboBox1_LostFocus()

End Sub

This way, you can type in values and then run the routine after you select another object.

L42
  • 19,427
  • 11
  • 44
  • 68
  • I tried that. Here is the scenario. I have two drop down activex combo box the first drop down parameters are Change (1) gotFocus Dropdown button click LostFocus the second drop down parameters are Change (1) gotFocus Dropdown button click LostFocus Assigned these values to the specified event trigger. After using both the combo box. Still change event is trigerring(Even lost focus) – Madhan Mar 31 '15 at 09:47