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