Problem with ComboBox, dynamic range and change event

[Wim SKW]

Hi,

The Change event of the ComboBox is triggered twice when modifying ANOTHER
absolutely non-related workbook.
I'm using Excel 2002 SP3.

Steps to reproduce:
I created a new workbook, filled range A1:A6 with a,b,c,d,e,f.
Then I created a dynamic range named MyList =OFFSET(Sheet1!$A$1,0,0,6,1)
I added a ComboBox from the Control Toolbox (not Forms) and filled the
ListFillRange with "MyList".

The ComboBox has the following code:

Private Sub ComboBox1_Change()
Debug.Print "ComboBox1_Change()"
End Sub

Then I created a second (empty) workbook.
when I enter something in a cell, the ComboBox_Change() event of the first
workbook fires twice (I can see that in the immediate window).

Both workbooks have nothing to do with each other whatsoever. They only
happen to be open at the same time.

Why is this and how can I prevent this?

Thanks.
-=Wim=-

 

[Jim Rech]

Then I created a dynamic range named MyList =OFFSET(Sheet1!$A$1,0,0,6,1)

I'd guess this is happeneing because OFFSET is a volitile function. Every
time a calculation occurs OFFSET recalcs. So your defined name is recalced
and the combo box takes that as a change event. You might have to live with
this if you want to use OFFSET.

--
Jim
| Hi,
|
| The Change event of the ComboBox is triggered twice when modifying ANOTHER
| absolutely non-related workbook.
| I'm using Excel 2002 SP3.
|
| Steps to reproduce:
| I created a new workbook, filled range A1:A6 with a,b,c,d,e,f.
| Then I created a dynamic range named MyList =OFFSET(Sheet1!$A$1,0,0,6,1)
| I added a ComboBox from the Control Toolbox (not Forms) and filled the
| ListFillRange with "MyList".
|
| The ComboBox has the following code:
|
| Private Sub ComboBox1_Change()
| Debug.Print "ComboBox1_Change()"
| End Sub
|
| Then I created a second (empty) workbook.
| when I enter something in a cell, the ComboBox_Change() event of the first
| workbook fires twice (I can see that in the immediate window).
|
| Both workbooks have nothing to do with each other whatsoever. They only
| happen to be open at the same time.
|
| Why is this and how can I prevent this?
|
| Thanks.
| -=Wim=-

 

[Wim SKW]

Thank you Jim.

Then I think I have to use a static variable in the Change event code to
track the previous value of the ComboBox to see whether it is a "real" change
or not, and exit out of the sub in case it isn't.

Thanks
-=Wim=-