bug: Worksheet_Change clashes with dropdown list validation.

[Alfista71]

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.

 

[OssieMac]

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.

 

[Alfista71]

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.

 

[Alfista71]

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

 

[Alfista]

(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