Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug: Worksheet_Change clashes with dropdown list validation.
This code:
Private Sub Worksheet_Change(ByVal rngTarget As Range) Debug.Print "Worksheet_Change" & "(" & rngTarget.Address & ")" & Application.Ready Application.EnableEvents = True Application.Calculation = xlCalculationManual rngTarget.Font.Bold = True Debug.Print "rngTarget.Font.Bold " & rngTarget.Font.Bold Application.Calculation = xlCalculationAutomatic End Sub Generates this output when editing a cell: Worksheet_Change($BA$30)True Got Here rngTarget.Font.Bold True But generates this output when picking from a data validation list: Worksheet_Change($BA$30)True Got Here rngTarget.Font.Bold False Ergo the assignment bold does not work and does not generate an error. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug: Worksheet_Change clashes with dropdown list validation.
What version of xl are you using? I tested the code with xl2002 with Windows
XP and with xl2007 with Windows Vista and it works fine. -- Regards, OssieMac " wrote: This code: Private Sub Worksheet_Change(ByVal rngTarget As Range) Debug.Print "Worksheet_Change" & "(" & rngTarget.Address & ")" & Application.Ready Application.EnableEvents = True Application.Calculation = xlCalculationManual rngTarget.Font.Bold = True Debug.Print "rngTarget.Font.Bold " & rngTarget.Font.Bold Application.Calculation = xlCalculationAutomatic End Sub Generates this output when editing a cell: Worksheet_Change($BA$30)True Got Here rngTarget.Font.Bold True But generates this output when picking from a data validation list: Worksheet_Change($BA$30)True Got Here rngTarget.Font.Bold False Ergo the assignment bold does not work and does not generate an error. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug: Worksheet_Change clashes with dropdown list validation.
The issue is that apparently Excel is in read-only mode of some sort.
NO changes are possible. You cannot even execute OnTime successfully. What was your final resolution of this issue? In my case, I've narrowed it down to the presence of a user-defined function in another worksheet (no worksheet dependencies) which is being provided a parameter which is a cell which has this formula "=today()". If instead that cell is "5/18/2009", the worksheet_change event in the other sheet executes correctly. Note: Today is a VOLATILE function. Note: I got interrupted and was doing some debugging in a third sheet: while stepping thru some code, the debugger quits at the "Exit Function" line within my user defined function. (Fired due to unnecessary recalc.) I am having the same problem when a different other sheet contains a call to a different custom function, and one of its parameters is a volatile function (indirect). These circumstances are very similar to the first three described in http://support.microsoft.com/kb/248179. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug: Worksheet_Change clashes with dropdown list validation.
The cause is that Excel is doing a recalc of the cell (because of the
volatile parameter), and fools itself into thinking that ALL code being executed falls under the rules of a UDF. A UDF cannot, under any circumstances, change the structure of the workbook or worksheet. This seems to work. The downside to this workaround is that you have to remember to apply it wherever you have worksheet_change functionality that you want to execute for cells which use dropdown list validation. It does not prevent your volatile function parameter from messing up any other worksheets that you may have open. It merely protects the current one. sheet1: Public Sub Timed_Worksheet_Change(rngTarget As Range) Call Timed_Worksheet_Change(rngTarget) exit sub modTimedWorksheetChange_Sheet1: Option Explicit Private Declare Function SetTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long, _ ByVal uElapse As Long, _ ByVal lpTimerFunc As Long _ ) As Long Private Declare Function KillTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long _ ) As Long Private mcolTargets As Collection Private mWindowsTimerID As Long Public Sub Timed_Worksheet_Change(rngTarget As Range) If mcolTargets Is Nothing Then Set mcolTargets = New Collection mcolTargets.Add (rngTarget.Address) 'If mWindowsTimerID < 0 Then KillTimer 0&, mWindowsTimerID mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf Delayed_Worksheet_Change) End Sub Public Sub Delayed_Worksheet_Change() On Error Resume Next KillTimer 0&, mWindowsTimerID On Error GoTo 0 mWindowsTimerID = 0 Dim rngTarget As Range Do While mcolTargets.Count 0 Set rngTarget = Range(mcolTargets(1)) mcolTargets.Remove 1 ‘Regular code goes here. Debug.Print rngTarget.Address rngTarget.Font.Bold=True Loop End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug: Worksheet_Change clashes with dropdown list validation.
(Reposted with typo removed).
The cause is that Excel is doing a recalc of the cell (because of the volatile parameter), and fools itself into thinking that ALL code being executed falls under the rules of a UDF. A UDF cannot, under any circumstances, change the structure of the workbook or worksheet. Thie following workaround seems to work. The downside to this workaround is that you have to remember to apply it wherever you have worksheet_change functionality that you want to execute for cells which use dropdown list validation. It does not prevent your volatile function parameter from messing up any other worksheets that you may have open. It merely protects the current one. sheet1: Public Sub Worksheet_Change(rngTarget As Range) Call Timed_Worksheet_Change(rngTarget) exit sub modTimedWorksheetChange: Option Explicit Private Declare Function SetTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long, _ ByVal uElapse As Long, _ ByVal lpTimerFunc As Long _ ) As Long Private Declare Function KillTimer Lib "user32" ( _ ByVal HWnd As Long, _ ByVal nIDEvent As Long _ ) As Long Private mcolTargets As Collection Private mWindowsTimerID As Long Public Sub Timed_Worksheet_Change(rngTarget As Range) If mcolTargets Is Nothing Then Set mcolTargets = New Collection mcolTargets.Add (rngTarget.Address) 'If mWindowsTimerID < 0 Then KillTimer 0&, mWindowsTimerID mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf Delayed_Worksheet_Change) End Sub Public Sub Delayed_Worksheet_Change() On Error Resume Next KillTimer 0&, mWindowsTimerID On Error GoTo 0 mWindowsTimerID = 0 Dim rngTarget As Range Do While mcolTargets.Count 0 Set rngTarget = Range(mcolTargets(1)) mcolTargets.Remove 1 ‘Regular code goes here. Debug.Print rngTarget.Address rngTarget.Font.Bold=True Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Dropdown list starts at b ottom of list | Excel Discussion (Misc queries) | |||
Create validation list with Worksheet_change | Excel Programming | |||
data validation list does not trigger worksheet_change event | Excel Programming | |||
Problem with Data Validation Dropdown List / Worksheet_Change Event | Excel Programming | |||
MS Bug? Data validation list dropdown with Worksheet_Change event | Excel Programming |