MS Bug? Data validation list dropdown with Worksheet_Change event

[Dan Frederick]

I think I've found an MS bug. Can anyone see if I'm doing
something wrong or find a workaround. Keep in mind that
I'd rather keep the Function if at all possible. Here's
the reproducable scenario:

1. In a new sheet, cell A1 set Data Validation on List and
0,1,2,3 as the options. Cell A2 set to =test(A1).

2. In the VB editor, Insert a new module and put the
following code in it:

Function test(a as Integer) as Integer
test = a + 1
End Function

3. On the Sheet1 code page, insert the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("B1") = Not Range("B1")
Application.EnableEvents = True
End Sub

4. When you change Cell A1 with the dropdown, B1 doesn't
change. When you type a value in A1, B1 changes.

I think it's a bug. Any other ideas? Can anyone suggest
a way to make this work (without removing the Function
test)?

Thanks,

Dan

 

[Dan Frederick]

One more thing,

You need a line in Function test that reads:
Application.EnableEvents = True

Otherwise it seems to get stuck after using the dropdown.

Thanks for the help!

Dan

 

[Frank Kabel]

Hi
You used the worksheet change event (which is triggered by manual
inputs). In your case you may use the selection_change or the Calculate
event of your worksheet module

 

[Frank Kabel]

Hi
as an addition: which Excel version are you using? Excel 97?

 

[Dan Frederick]

Well done. That did it. It doesn't work when a
calculation isn't triggered, but then again, I don't
really want it to work when a calculation isn't triggered.

Thanks!

Dan

 

[Dan Frederick]

Sorry Frank.

I just tried testing it in my application and it still
doesn't behave the way I think it should. If you add a
msgbox to the text in the Worksheet_change event, you'll
see that it is triggered by the validation dropdown. The
worksheet just doesn't get recalculated like it should.

I'm reverting back to my original idea that I think it's a
bug and not a feature.

Thanks,

Dan

 

[Frank Kabel]

Hi
maybe i'm a little bit lost. what is exactly not working ??

 

[Dan Frederick]

An update on this problem for anyone who stumbles across
it in a search.

After some discussions with MS, it appears that there is a
timing problem with Excel if you use Data Validation, a VB
function and a Worksheet_Change event that modifies a
range (any range) on the worksheet. If the VB function is
called using a value from the DV dropdown list (or any
data validation that fails), the Worksheet_Change event
will produce an error when trying to modify the range.

Randy Smith from MS came up with the workaround of putting
an Application.Calculation = xlCalculationManual at the
beginning of the Worksheet_Change event and then an
Application.Calculate at the end.

Unfortunately, because you need to turn calculation to
manual, you'll have to create a WS_Change event (that just
does a Calculate) in every worksheet in your app.

I hope that helps! Thanks to Frank Kabel and Randy Smith
for their efforts.

Regards,

Dan