Worksheet Revision Date update only once in the same day

[mikeburg]

I put a revision date in a cell (today's date) when I change a
worksheet. If I merely print, view or something without actually
changing the worksheet, I do not update the revision date even if I
happen to save it again.

I need the worksheet to automatically update the worksheet revision
date only once for that day and only when the worksheet is changed (or
only once after it's opened & changed will work just as well).

I am trying to avoid changing the date every time the worksheet is
changed since it's not necessary to change the date to the same date
every time a change is made. More importantly, changing the revision
date every time the worksheet is changed causes data entry to be
slower.

I prefer not to use the today function in a cell that changes every
time the worksheet is opened because the worksheet is not updated every
time it's opened. Also, doing so causes Excel to prompt to save on exit
of the worksheet when all I've done is printed or viewed the data.

I need to hear everyone's ideas how to do this even if there
differences of opinions. Please?

mikeburg

 

[Roy Wagner]

How about this? The workbook decides as usual whether or not it needs to ask
you if it should be saved. If you elect to save, the BeforeSave event is
triggered. It checks the existing revision level against the value calculated
for that day. It is automatically updated only if it does not match today's
calculated value. In my example, I located the Revision stamp on sheet1 in
cell A1. You can put it wherever you want it. You can also alter the text and
date formatting to suit your situation. It doesn't slow things down
noticeably, since the event only occurs when you save your work.

To install it, use ALT-F11 to bring up the VB Editor.
Assuming there is no code in the workbook now, paste the code below into the
code window. If you already have some code, make sure this gets put into the
workbook beforesave event.

Give it a shot.

Roy

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Sheet1.Range("A1").Value <> "Revision " & Format(Date, "mm-dd-yyyy") Then
Sheet1.Range("A1").Value = "Revision " & Format(Date, "mm-dd-yyyy")
End If

End Sub

Of course, a time stamp could be added as well, but his would increase
revision changes depending on the way you formatted the time, i.e., down to
the hour, minute or second.

 

[mikeburg]

Great idea except sometimes we print before we save.

Any more ideas?

Thanks for all your help.

mikebur

 

[Roy Wagner]

We could simply add the same procedure to the BeforePrint event, however it
is better form to put the duplicate procedure into a separate sub and then
call it from the 2 events as needed, or more if you think of other events.
This also makes it easier to upgrade, such as when you decide to password
protect the sheet and protect that cell. In that case, we would have to wrap
the procedure with additional code to see if the sheet is locked, unlock it,
and restore it when done. Or let's say the sheet is unlocked for development,
but locked for distribution. Then we could use the locked status as a toggle
for the procedure. Any way, remove the procedure you added before and replace
it with those below.

Roy


Private Sub Workbook_BeforePrint(Cancel As Boolean)

RevisionUpdate

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

RevisionUpdate

End Sub

Sub RevisionUpdate()

If Sheet1.Range("A1").Value <> "Revision " & Format(Date, "mm-dd-yyyy") Then
Sheet1.Range("A1").Value = "Revision " & Format(Date, "mm-dd-yyyy")
End If

End Sub

 

[mikeburg]

Good ideas. That will probably suffice.

However, what about using an ONENTRY macro? Can it or any other event
code terminate itself once the revision date cell is changed to the
current system date?

I just prefer code not be running once the revision date is the same as
the system date.

Will the following code stop the code once the date in A1 equals the
system date?

Application.OnEntry= "UpdateRevision"
If Range("A1") = Date then
Application.OnEntry=""
End If

Sub UpdateRevision()
Sheet1.Range("A1").Value = "Revision" & Format(Date, "mm-dd-yy")
End Sub

I am so new at this you may have to refine my code if you think it will
work.

mikeburg

 

[Roy Wagner]

OnEntry appears to be a Pre xl97 term, it's replacement in xl97 and later is
the Change event, although there is backward compatibility. It seems to me
that code in there, would be be running anytime you made a change on the
sheet. I don't think that would help.

Roy

 

[mikeburg]

It appears your are correct. Using both the before save and the before
print events concurrently is the way to go to update the revision date
only once.

However, I need help with the code when the worksheet has not been
changed, I want it to keep the same revision date of the last change.
In other words,

(1) I may accidently save again when there have been no changes
(2) I may want to merely print the worksheet without making any
changes
In either case, I need the worksheet to retain the date of the last
change.

Can you help? MikeBurg

 

[Roy Wagner]

Here is a revised sub to replace the one you have. The calling subs are OK as
is. The fix is to check the Activeworkbook.Saved property. It will be TRUE
when no changes have been made and FALSE when changes have been made. This is
the deciding property whether Excel asks you to save before closing or not.
It can be used in code to close a workbook without prompting the user to save
changes (when there are in fact changes) by setting the property to TRUE.


Roy

Sub RevisionUpdate()

If Not ActiveWorkbook.Saved Then
If Sheet1.Range("A1").Value <> "Revision " & Format(Date, "mm-dd-yyyy")
Then
Sheet1.Range("A1").Value = "Revision " & Format(Date, "mm-dd-yyyy")
End If
End If

End Sub

 

[mikeburg]

Great! Thanks so much for your help. It's so hard to find these kin
of things on your own when you are so new to VBA.

mikebur