ComboBox Change Event Triggers on Value change in Worksheet

[abi_learn_vba]

Hi..

I have ComboBox_Change macro which is triggered on the value change in the ComboBox dropdown. But the Same ComboBox_Change macro is triggered if i change an value in the Worksheet. I have do not have any other event macro other than ComboBox_Change macro.

Can any one tell what would be going wrong with this?

Thanks
-Abi

 

[abi_learn_vba]

Any Help...

 

[Firefly2012]

Hi

What do you have in the ListFillRange of the combobox's properties?

 

[Norie]

Is the combobox linked to the worksheet in any way?

Perhaps through it's ControlSource or RowSource properties?

 

[abi_learn_vba]

I have a Dynamic named range in "ListFillRange" property of the ComboBox.

I have used "Mylist" as the named range, where the dynamic range is determined using below formula.

=OFFSET(Lists!$A$2,0,0,COUNTA(Lists!$A:$A)-1,1)

As you see here the Range is taken from the same workbook in the sheet "Lists".

As i said earlier i do not have any event other than ComboBox_Change() event.

Thanks
-Abi

 

[Norie]

Abi

That could be where the problem lies.

What changes on the worksheet trigger the change event of the combobox?

 

[RoryA]

Your DNR is using a volatile formula, so recalculates whenever the workbook does. This causes the combobox to reload and the change event to fire.

 

[abi_learn_vba]

The Changes are like.. Entering a value in a Cell, Renaming a Sheet, Deleting an Value in a cell... Moving an Range etc.. All these Changes will trigger the combobox event. And also when i select an value in Combobox list the event macro is triggered twice.

Thanks
-Abi

 

[abi_learn_vba]

Rory,

Thanks for that info. How can i avoid it from recalculating. I tried using..

Application.Calculation = xlManual
.......
.......
Application.Calculation = xlAutomatic

But this did not resolve the problem..

Thanks
-Abi

 

[RoryA]

Don't link it directly to the worksheet.
If you really insist on doing that, you might try using INDEX rather than OFFSET to create the ranges, since that is only semi-volatile.