MS Bug? Data validation list dropdown with Worksheet_Change event

  • Thread starter Thread starter Dan Frederick
  • Start date Start date
D

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top