Combobox Change event (2000)

Topic

New Reply

I have a combobox (from Control Toolbox) with the following code attached. For some reason the sub is being triggered on every Worksheet_Change event rather than just on the cbo change.

Then when I do make a change to the cbo it cycles thru once correctly, followed by a 2nd cycle that hangs on line4.

Where is the error?

Note: the cbo LinkedCell is [NewAlert]

Private Sub cboAddToAlertList_Change()

Dim NextResID As Range
Application.EnableEvents = False
‘Determine where to insert new data
Set NextResID = Range(“b65000”).End(xlUp).Offset(1, 0)
Line4:
[NewAlert].Copy NextResID
NextResID.Offset(0, 1) = Application.Index([ResID], [NewAlert]).Offset(0, 1)
Set NextResID = Nothing
Application.EnableEvents = True

End Sub

Reply | Quote

Replies

In a test workbook I created, the Worksheet_Change event does not trigger the cboAddToAlertList_Change event, so the latter code is executed just once when I select something in the combo box. (I assumed that ResID is another named range.)

What other code do you have behind the worksheet?

Reply | Quote

Hans,
There is NO OTHER event code ; neither in ThisWorkbook nor any of the sheets.
I tried using the code in a new workbook, as you have and it seems to work.
I tried deleting the cbo and making a new one with the same properties – still doesn’t work
There are no hidden sheets.

there are only 2 reg module subs, neither connected to this event.

???????????????

Reply | Quote

If it works as expected in a new workbook and Hans confirms that it works as expected, but it works “unexpectantly” in the workbook, that it is in, I suspect that there must be something in this workbook that is causing it to behave this way.

Steve

Reply | Quote

If it works as expected in a new workbook and Hans confirms that it works as expected, but it works “unexpectantly” in the workbook, that it is in, I suspect that there must be something in this workbook that is causing it to behave this way.

Steve

Reply | Quote

Hans,
There is NO OTHER event code ; neither in ThisWorkbook nor any of the sheets.
I tried using the code in a new workbook, as you have and it seems to work.
I tried deleting the cbo and making a new one with the same properties – still doesn’t work
There are no hidden sheets.

there are only 2 reg module subs, neither connected to this event.

???????????????

Reply | Quote

I also tried placing the code in a reg module, exporting the new module, deleting the old code, them importing it back. the problem still exists:
every change in the sheet (data entry as well as deleting a cell) will trigger the cbo_change_event code.

Have I somehow corrupted the entire file?
I am able to make it work in a new workbook.

Reply | Quote

Does any of the other code change the “linked cell” to the combo box? This will trigger the cboChange event since the cbo is being changed.

Steve

Reply | Quote

Does any of the other code change the “linked cell” to the combo box? This will trigger the cboChange event since the cbo is being changed.

Steve

Reply | Quote

I also tried placing the code in a reg module, exporting the new module, deleting the old code, them importing it back. the problem still exists:
every change in the sheet (data entry as well as deleting a cell) will trigger the cbo_change_event code.

Have I somehow corrupted the entire file?
I am able to make it work in a new workbook.

Reply | Quote

In a test workbook I created, the Worksheet_Change event does not trigger the cboAddToAlertList_Change event, so the latter code is executed just once when I select something in the combo box. (I assumed that ResID is another named range.)

What other code do you have behind the worksheet?

Reply | Quote