Unexpected triggering of combobox change?

[richard.ca]

Hi All,

I've written a class module to handle combobox events in a multiple-worksheet workbook
to avoid duplicating the code on every worksheet. I'm using a single combobox to display
a drop-down list of names when I double click in a specific zone of my worksheet. The
combobox which is normally hidden becomes visible when a cell in the target zone
is doubleclicked and is then positionned over the target cell.

Everything seems to be working fine except for one strange thing: my combobox_change
code seems to be triggered when *any* cell on my worksheet changes, not just the
combobox object. The code in my class module CboxEvtHandler looks like this:

Option Explicit

Private WithEvents mcombobox As MSForms.ComboBox

Public Property Set Control(obtNew As MSForms.ComboBox)
    Set mcombobox = obtNew
End Property

'
' Handle combobox changes
'
Private Sub mcombobox_Change()
    
    ' Workaround to stop crash when we're triggered from a cell outside the
    ' customer names range and LinkedCell is a null string. The proper solution
    ' will be eventually to understand why changes outside the target zone still
    ' fire this combobox change event and to stop coming here...
    If ActiveSheet.ComboBox1.LinkedCell = vbNullString Then Exit Sub

        
    ' write new combobox value to active cell (assume linked to combobox)
    Range(ActiveSheet.ComboBox1.LinkedCell) = ActiveSheet.ComboBox1.Value
End Sub

Then in my workbook module I call the following from the Workbook_Open function:

Dim clsEvents As CboxEvtHandler


'
' Hook up a handler for "ComboBox1" using the CboxEvtHandler class
'
Sub InitializeEvents()
    
    Dim cboTemp As OLEObject
    
    ' Get a handle to the "ComboBox1" object on this week's sheet.   
    Set cboTemp = ActiveSheet.OLEObjects("ComboBox1")
    
    ' Create a new instance of the event handler class
    Set clsEvents = New CboxEvtHandler
        
    ' Tell it to handle the events for the combobox
    Set clsEvents.Control = cboTemp.Object
End Sub

The test in the combobox_change code which filters out unwanted triggering (LinkedCell
is set only when doubleclicked cell is in target zone) should not really be necessary.
As far as I can see I'm not affecting the combobox when changing cells which are not
in the target zone so why is the combobox_change event being fired?

I'm sure I must be missing something obvious. Any ideas?

Many thanks.

 

[RoryA]

Firstly, your code should really be:

Private Sub mcombobox_Change()
    
    ' Workaround to stop crash when we're triggered from a cell outside the
    ' customer names range and LinkedCell is a null string. The proper solution
    ' will be eventually to understand why changes outside the target zone still
    ' fire this combobox change event and to stop coming here...
    If mcombobox.LinkedCell = vbNullString Then Exit Sub

        
    ' write new combobox value to active cell (assume linked to combobox)
    Range(mcombobox.LinkedCell) = mcombobox.Value
End Sub

but I would not use the LinkedCell (which I suspect is causing your issue) - just write the value to whichever cell is required.

 

[richard.ca]

Sorry Rory, been out of the office for a few days. Thanks for the suggestion, I tried your
code and I also tried eliminating the LinkedCell but my combobox change code still insists
on firing when I change any cell in the worksheet. Very odd, I still can't imagine why
this should be. I wondered if it could be something in the way I setup the combobox
but I couldn't see anything in the properties that looked suspicious.

An other ideas?

Thanks.