(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