ComboBox Change event triggered incorrectly?

[Vindaloo]

Hi,

I have a ComboBox with the Change event set up to modify a few thing
according to the value selected.

On a separate sheet in the same workbook, I am changing the value of
cell, and for some reason this is executing the ComboBox Change code.
The reason I know this is because I am getting a runtime error in th
ComboBox Change code.

Is this to be expected, and I just haven't noticed before because ther
was no runtime error?

Thanks,
Vindalo

 

[MattShoreson]

this is not default behaviour for a combo.

What other change events have you got in your workbook

 

[Vindaloo]

The only other Change events that I have are for 2 checkboxes. I've now
commented these out but the problem remains.

I am in the process of trying to remove everything bit by bit until the
problem disappears, then hopefully the last thing that I removed will be
the cause. This is quite time-consuming, so any help in the meantime
will be greatly appreciated!

Vindaloo

 

[Vindaloo]

Ok, I have found the problem (a lot sooner than I expected).

The ComboBox has a linked cell.
This linked cell is used as an argument in an INDEX worksheet
function.
One of the cells in the range used in the INDEX function contains a
VLOOKUP worksheet function.
The VLOOKUP function refers to a range which contains the cell I am
changing which triggers the problem. Because this cell is the leftmost
cell of the range, my guess is that Excel is determining that the linked
cell of the Combobox is now potentially wrong and is triggering the
Change event?

Interestingly, if I remove the original error in the Change event so
that it runs through, Excel then falls over and asks if I want to send
an error report. I would have thought that Excel should be able to
cope with what I'm doing.

If anyone is interested, I can reproduce a simple version to
demonstrate what's happening and then maybe you can tell me why it all
falls over?

Thanks,
Vindaloo