I'm struggling to clear a dropDown selection via VBA code in Excel 2010. The reason I want to do this is because the onAction event doesn't fire if you click the same item consecutively without changing to another item first.
I did some digging and read that the InvalidateControl method clears the cached value of a control. So, I implemented it, and...
Sub getUIHandle(ribbon As Office.IRibbonUI) Set MyRibbon = ribbon End Sub ---- Sub LabelCallback(control As IRibbonControl, id As String, index As Integer) Select Case id Case "CH1" ' take some action Case "CH2" ' take some action Case "CH3" ' take some action Case "CH4" ' take some action End Select MyRibbon.InvalidateControl (control.id) End Sub
I've traced the calls and know that my getUIHandle sub is being called, and that the InvalidateControl call is being called. It simply doesn't invalidate the control : )
Is there a method that clears the currently selected value of a dropDown RibbonX control?
I refactored the code and changed the LabelCallback subroutine to set a state variable, and have a separate button in the Ribbon actually trigger the 'take some action' routines, to work around this. Inelegant in my opinion. (Dare I say clunky?) It makes much more sense to take the action when the dropBox item is selected, and would work beautifully if the event would fire regardless of which item is selected.