Simple message box question

Z

zeyneddine

I am trying to create a simple message box which will display after I open
the Excel file and if I try to make any change to a document is being made.
If the selection is "Yes" in the messsage box, the change I am making in the
document will be allowed. If the selection is "No" in the message box, the
change will not be allowed. The message box needs to be repeated if I select
"No" and again try to make a change. The message box requirement is really
simple, it just has to say: "Have you updated the dates?" If "Yes", the
change is allowed. If "No", change is not allowed. If after "No", change is
again tried, message box pops up again.

Thanks in advance for your help
 
D

Dave O

This can be done with some above-average programming skills- how
familiar are you with this? It would involve an "on change" event
triggered by making a change to the spreadsheet, then modifying a named
range or hidden cell value to TRUE or FALSE depending on the answer to
your message box, then allowing or disallowing subsequent changes based
on the value of that named range or hidden cell value.
 
Z

zeyneddine

If you can give me a snippet, I can complete it. Right now I do not have a
direction.
 
Z

zeyneddine

If you can give me a snippet, I can complete it. Right now I dont have a
direction.
 
D

Dave O

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
 
Top