Is there a "DateSaved" facility in Excel 2000 ? 3

[Tonyjstone]

I use Excel 2000. I would like to place a formula in a cell that records the date & time on the most recent occasion when the sheet/workbook is saved. I know the facility is available in Word, but is it possible to do this in Excel?

Thankyou in advance


Tonyjstone
"Sometimes I sits and thinks, and sometimes I just sits"

 

[xlbo]

Use the worksheet_Beforesave event
and this code
(requires a named range called "LastSave&quot

Sheets("Sheetname&quot.range("LastSave&quot.value = now()

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Tonyjstone]

Thanks xlbo

I am a complete duffer when it comes to VB. Could you describe how I use your advice, or refer me to a thread that may help.
Sorry to be a pain, and thanks again

Tonyjstone
"Sometimes I sits and thinks, and sometimes I just sits"

 

[Loomah]

I don't want to step on xlbo's toes but I've just noticed a typo!

To access the code window the easieat way is use ALT+F11
This opens the Visual Basic Editor (VBE)
On the left you should see a 'Project Explorer'
Under Excel objects dbl click ThisWorkbook
On the right you should see basically a blank page, at the top of which there are two dropdowns.
Click the left one and choose Workbook
Click the right one and choose BeforeSave
You should now see something like this

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

End Sub

Put xlbo's code in the gap!
Delete the other 'Sub' on the page

the typo I mentioned is "workSHEET_Beforesave" understandable when typed directly into the reply!

xlbo
I have an email that might make you laff a bit if you want me to forward??

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Loomah - go for it (email that is Geoff.Barraclough@Punchpubs.co.uk)

I never get the event syntax right - bit lazy I'm afraid - 'cos it provides the start sub / end sub when you select from the dropdown, I rarely bother writing it out in responses but cheers for pointing it out to Tony

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Tonyjstone]

Thankyou both, I am feeling smug as I managed to follow your instructions and now have a working version.
Thanks
Tonyjstone
"Sometimes I sits and thinks, and sometimes I just sits"

 

[Loomah]

Tony
I'm happy for you but tell me one thing. I now have this mental picture of a bloke sitting in the corner of the office staring at his PC with a daft grin on his face. He then tells anyone who'll listen "come and see this that I've done"

Anywhere near the mark???????

Or is that just me that would do things like that??!!!!

Cheers!
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Nope - I've done that plenty of times. Just about manage to get something cool working - 1st thing I do is show it off to anyone that might be the slightest bit impressed
It's a great feeling when you finally crack a problem so everyone should share the joy ;-)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[RITec]

I know this is probably easy but, (Just not for me) I was wondering how would you go about keeping a log of who and when changes were made. I added a range called user and I am using the environ("username&quot to track the user who is saving but, I am having trouble getting it to find the next empty cell and fill it in.

David

 

[Loomah]

RITec
Firstly, PLEASE start a new thread , with a good subject heading, for you questions. It makes it easier for others looking for similar solutions. Your question doesn't even tie in really with the original thread.

Secondly an answer!!!
assuming you are creating a list on a sheet, have a look at using
range("a1&quot.end(xldown).offset(1,0)
and write the data there.

;-)


If a man says something and there are no women there to hear him, is he still wrong?

 

[RITec]

Thank you
Loomah


I think I will start that thread because, like the guy above I am not real familiar with VB but, I am learning. I am having trouble with what you wrote above I will also put down what I have so far.