MS Bug? Data validation list dropdown with Worksheet_Change event

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dan Frederick (anonymous_at_discussions.microsoft.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 10:38:58 -0800

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
>-----Original Message-----
>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 ***, 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
>
>.
>

Quantcast