Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Bug? Data validation list dropdown with Worksheet_Change event
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 -----Original Message----- 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 -----Original Message----- 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 -- Regards Frank Kabel Frankfurt, Germany "Dan Frederick" schrieb im Newsbeitrag news:e23e01c40b84$f9b90f60 ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation with dropdown list | Excel Discussion (Misc queries) | |||
Data Validation Dropdown List Not Working | Excel Discussion (Misc queries) | |||
blanks in data validation list dropdown | Excel Worksheet Functions | |||
size of data validation dropdown list | Excel Worksheet Functions | |||
Data Validation - Dropdown List Not Appearing | Excel Discussion (Misc queries) |