View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dave O Dave O is offline
external usenet poster
 
Posts: 427
Default Simple message box question

Here's a stab at it. Create a named range in your workbook called
DatesUpdated, then use this code to reset that value to False when the
workbook opens:

Private Sub Workbook_Open()
'reset named range to False upon opening of workbook
Names("DatesUpdated").Value = False
End Sub

This *should* work, but for some reason does not when I enter it as an
on-change event for a worksheet. I can't get the named range to
evaluate or assign a value to it from the "on worksheet change" event.
As separate code in a module (i.e. not associated with a trigger event)
it does work and does change the value of the named range. I'm stuck.

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