Click event not firing when clicking on already selected item in VBA Excel ActiveX Combobox

1
votes

I need som expertise help!

I am not new to VBA or Excel, but I've come accross this weird thing I can't find a way around. I've been googling for a long time, so I thought I'd ask here.

Background: I have a excel-sheet with a lot of VBA I am developing for my job, with an activeX combobox being filled with items from an access database on startup. The user can pick an item, which automatically (on lost focus/on change) populates some textboxes with data from the database. Then the user can edit the textbox-values loaded from the database, which in turn adjusts some calculated results.

Now, the question: If the user adjusts a parameter, but wants to go back to the default values - i.e. reload the database values - selecting the same combobox entry again does not fire any event that I've found - since it is already selected. I would think that the click-event should fire - but no. The change-event obviously does not fire...

Any way to work around this? Any event I haven't thought of?

Note: Current workaround solution would be that the user has to select another item, then back to the one he or she wants, or adding a reset button. I don't like either of those workarounds, so if there's anything I can do...

Thanks!

1
vbaexcelcomboboxactivex
Yes, there's no other workaround. As event suggests, it will get trigger on combobox value change only. – Paresh J
Try the MouseUp event. – Rory
try what @Rory has suggested, failing that create a command button that gets the selected item and passes it as an argument to the other sub. – SierraOscar
The mouse up looked promising, but the value of the combobox on mouse up is ALWAYS the one previously selected. So this actually works only for the case when one wants to re-select. However, if there is no better way, and the changed-event fires correctly after the mouse-up on selecting a new item, I might have this as a very ugly back-up-solution, where I can run the update on both mouse-up and change... not ideal though. Database-queries take a tiny bit of time :) Thanks for the help! – Tjomsen

1 Answers

0
votes

I have been working on a problem exactly like the one you are stating for days and may have the ideal solution.

The idea is to reset 'ComboBox1.Value' to the default value so that you can reselect the same option. This eliminates the need to click on a random item and then reselect the previous item in order to repopulate linked cells and requires no seperate reset button.

Code:
Sub cbReset()
 'Reset ComboBox1 Values
      Sheets("Sheet1").ComboBox1.Value = "Select Option..." <-- 'Default value of Combo Box
      Sheets("Sheet1").ComboBox2.Value = "Select Option..." <-- 'Default value of Combo Box
      Sheets("Sheet2").ComboBox1.Value = "Select Option..." <-- 'Default value of Combo Box
End Sub

You can adapt code however you see fit. This can be called or entered in the same worksheet. This code goes at the end of the statement after everything else has been executed. I entered extra lines for use on multiple sheets or combo boxes just in case there is a need.

You also change the sheet name or combobox name.

Hope it wasn't too late to help.

Cheers!