ComboBox_Click event is firing for any change on any worksheet

[6StringJazzer]

Crossposted at Excel Forum and Stackoverflow but I'm not qualified to post links yet.

I have isolated this problem to the workbook attached. This is from a complex application and I stripped it down to the bare minimum needed to reproduce the problem.


There is one worksheet Labor with a combobox named ComboBoxRatesSelected.


This combobox has a RowSource on sheet Parameters, and it has a LinkedCell on sheet Parameters.


The code module for sheet Labor has an event handler for ComboBoxRatesSelected_Click.


Whenever any change is made to any cell on any worksheet, the event handler is triggered twice. This happens even though the combobox is not clicked, and there is no code that changes the combobox. The only code in the entire workbook is the Click handler for the combobox. The event handler in this version simply puts up a message box.


The only time I have ever seen unintended triggering of an event is if other code changes the value in the control, which will trigger a Click event. However, this stripped-down version rules that out.

[Paul_Hossler]

I think I had to use MouseUp instead of Click or Change to get something working -- still don't know why though

Option Explicit

' Update which rate sheet is displayed
'Private Sub ComboBoxRatesSelected_Click() ' Change()
'    MsgBox "ComboBoxClick occurred"
'End Sub

Private Sub ComboBoxRatesSelected_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    MsgBox "ComboBoxRatesSelected_MouseUp occurred"
End Sub

[6StringJazzer]

Thanks, I had not thought of that. At this point I have developed another workaround but I'm still looking to understand how this event gets triggered without touching the combobox.

[mdmackillop]

I tried this:
Copied RateList to Labor sheet and renamed it MyRates
Set ListFillRange to MyRates
Cut and pasted MyRates to Parameters which triggered Click macro
Changes to Labor do not now trigger Click event

Can't explain the behaviour though.

[Paul_Hossler]

Mac reminded me of something ---

Instead of using a formula for the named range RatesList, I used the 'real' address: =Parameters!$E$4:$E$12, and the event only seems to fire once

So you can 'hard code' the named range or create it on the fly without using the formulas and it should work

[SamT]

I have read where it is thought best to leave Named Ranges for worksheet formulas and code for all Ranges used in VBA.

[Paul_Hossler]

I have read where it is thought best to leave Named Ranges for worksheet formulas and code for all Ranges used in VBA.

Your homework assignment for tonight is to locate that reference

I'd like to read a little more about that

[mdmackillop]

Hi Paul
I recall SNB suggested that here

[Paul_Hossler]

Hi Paul
I recall SNB suggested that here

Interesting

But slightly different. The AtiveX's RowSource is still a Named Range. All I did was replace the dynamic named range RefersTo (which used the standard COUNTA(..) technique) with a static fixed range RefersTo

I'm sure snb's point about avoiding RowSource is valid, but in THIS sitituation I think the formula was recalculating and that caused the _Change event to be re-triggered


JUST A GUESS

[6StringJazzer]

Thank you for that! I almost never say this but it sounds like an Excel bug. Or possibly a file corruption.

[SamT]

Homework. hehehe

I gots more'n a hunnert programmin' sources I reads when I gets board. But, no, it wern't snb.

'Ere, you look it up