View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
Dave O Dave O is offline
external usenet poster
 
Posts: 427
Default VBA code to affect value of a named range

I wrote code intended to evaluate and reassign the value of a named
range based on an "on change" worksheet event. The code changes the
named range value when run as a separate routine in a module, but
doesn't run from the worksheet trigger. Can anyone correct this code,
or tell me what I've done wrong? Thanks. Code follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Response As String
If Evaluate(Names("DatesUpdated").Value) = False Then
Application.Undo
Response = MsgBox("Have you updated the dates?", vbYesNo)
If Response = vbYes Then
Names("DatesUpdated").Value = True
End If
End If
End Sub