Hi Guys
I’m working on a spreadsheet for used cars that only two people have amend access to, the others only have read-only access. I have a cell on one of the sheets within the workbook that I need to have automatically update on closing, showing the date and time the workbook was last updated.
I have this basic beforeclose ad-hoc in the ThisWorkbook VBS tab thus:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
’ Selects the first sheet and inputs the date and time
’ so that you know when it was last edited.
Sheets("Used Car Stock").Select
Range("E1").Select
ActiveCell.FormulaR1C1 = "=NOW()" 'Assigns current system date & time to cell E1
Range("B2").Select
ActiveWorkbook.Save
End Sub
This works, but for everyone. I need it to work only if there have been changes made to the workbook, so it does not update the cell E1 if a read-only user has accessed it and just closed it down. Any ideas on this?
I thought of that Greg, but I cannot find any IF function that could tell me if the contents of the workbook had changed, that would create a TRUE or FALSE output that I could point a BeforeClose macro to.
I need an IF statement in the macro that can tell if the workbook contents have changed, if so update the cell E1 if not, just close and don’t update cell E1 but it is eluding me at the mo.
Normally I would do a BeforeClose SaveAs, but this would give a different filename and each user has a shortcut to the original file, so that file name cannot change, just the workbook contents
/edit The other issue is that using NOW() means that the date & time in the cell changes when the workbook opens as the system is putting a formula into the cell, not the time in the form of text at the point it was last saved. I need some text like “Newtime = ThisDate & ThisTime”, where both are defined.
Not thought this through fully - too late. What if you turn auto calc off and force the user to press F9 to recalc. That gets round the NOW() issue and allows only edit users to change it
Unfortunately they need it on for vehicle order sheets, which is another macro based document that has to recalculate automatically and getting them to remember to switch it back on again is more trouble than it’s worth.
Also by using NOW() it seems to automatically update when opened, regardless of who opens it. This is Excel 2000 (I forgot to mention, just in case it matters). I need to create a text only output from the BeforeClose macro that populates cell E1 on close if changes have been made.
I’ve got round the now() issue by placing it off screen or hidden then running a macro to copy/paste>special>values into the main sheet. Curiously if the paste cell is pre-formatted as date, it does not display as a serial value but in date format which strangely, is available for further calculation purposes.
With regard to the read only users how about creating a separate worksheet for them where A1 = sheet2!A1 etc and protect it. Then hide the main sheet 2 to all but edit operators.
A nice workaround Mr B . The paste special works OK and stops the cell updating on opening, but it still updates on closing. Is there a command string that I could use something along the lines of:
“If workbook.contents= not changed, then close with out updating, else carry out the copy paste-special macro.”
Private Sub Workbook_BeforeClose(Cancel As Boolean)
’ Selects the first sheet and inputs the date and time
’ so that you know when it was last edited.
Sheets("Used Car Stock").Select
If ActiveWorkbook.ReadOnly Then
ThisWorkbook.Saved = True 'If opened by a read-only user, reports that changes have already been saved
ThisWorkbook.Close 'then closes the workbook without saving/updating
Else
Range("AT1").Select
Selection.Copy 'Copies the NOW() date from a pre-arranged cell
Range("D1").Select 'Pastes the copied date as text in cell D1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("D: D").ColumnWidth = 29.75
Selection.NumberFormat = "m/d/yyyy h:mm"
ActiveWorkbook.Save
End If
End Sub
If read only it thinks it’s already saved and closes, else it updates the D1 cell, saves changes and then closes the document. Thanx for your help Mr B. :cheers:
I could do, but it allows the “amend” user the opportunity to just click on the close button to shut down the document save the changes and update the “date updated” field all in one go for simplicity. I try to make their lives as simple as possible.
Because using NOW() directly changes the date and time in the cell automatically, regardless of who has accessed the document and I need to restrict that value to users who have write access to the document. Just having an arbitrary cell that holds the current date and time, I can copy it at the point when the document is closed, use the data to create a text based date and time figure that is then pasted special into the D1 cell
I need it to show a static text based date and time that will not automatically update and the VBscript stops this from happening for read-only users, so that you always know that the updated date and time in the cell is as a result of a write-enabled user accessing the workbook and making changes.
Tried it, you’re right it does work OK as long as the ThisWorkbook.Saved & ThisWorkbook.Close statements are in the script it won’t change the date. It certainly simplifies the script and cuts out the need for the NOW() in cell AT1. Cheers Kevin.
. The paste special works OK and stops the cell updating on opening, but it still updates on closing. Is there a command string that I could use something along the lines of: