VBA macro to tell who last saved a file and when

Vera

Banned
Joined
Sep 22, 2002
Messages
33
Please I need help with a macro (saved on the workbook not in the Personal macro file) which should give the following info (lets say on sheet 1, starting A1, A2) who last saved that file and the date. Thank you so much.

Vera
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

You can use this code. You need to place it in the workbook code module. To do this, right click the lower of the 2 Excel icons on the left hand of your screen and choose View Code.

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1") = Application.UserName
Sheets("Sheet1").Range("A2") = Now
End Sub

This will only give the correct name if the user name has been set up in Tools, Options, General. If you want some code which puts the Windows username into A1 then please post back (code is more complicated).
 

pmdown

Board Regular
Joined
May 6, 2002
Messages
71
No.

I have better solution:

Use The BuiltinDocumentProperties of Workbook Object. See VBA Help for other details, particularly see "LastAuthor" and "LastSaveTime" items.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Better?! It's just a different way of doing it, and both methods will produce identical results. The method I've used is slightly simpler and I actually posted a usable procedure.

Regards,
Dan
 

pmdown

Board Regular
Joined
May 6, 2002
Messages
71

ADVERTISEMENT

Hi dk.

I am still confident, that my solution is much better.

For example: if You want register "who last saved that file and the date" by Your way:
You must add Your macro in every new Workbook and in all existing workbooks (!?!)

If You have 500 Workbooks You must in each add Your macro. Inconceivable.

My solution only read the properties of arbitrary Workbook...without macro.

Best Regards
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
On 2002-10-11 06:27, pmdown wrote:
Hi dk.

I am still confident, that my solution is much better.

For example: if You want register "who last saved that file and the date" by Your way:
You must add Your macro in every new Workbook and in all existing workbooks (!?!)

If You have 500 Workbooks You must in each add Your macro. Inconceivable.

My solution only read the properties of arbitrary Workbook...without macro.

Best Regards

Are you trying to be deliberately argumentative?

1. You haven't answered the question. They wanted the last saved information displayed in cell A1 and A2. What you've offered is Yes, this information is freely available by choosing File, Properties, let's forget what you actually asked for...

2. Your initial post was to use the BuiltInDocumentProperties object, and that had some validity. However, how would you cause the values in A1 and A2 to be updated without using the BeforeSave event?

3.

You must add Your macro in every new Workbook and in all existing workbooks (!?!)

If You have 500 Workbooks You must in each add Your macro. Inconceivable.

Utter boll**ks! There are hundreds of people on this board who could easily achieve this without adding the macro to 500 workbooks.

4.

My solution only read the properties of arbitrary Workbook...without macro.

See point 1 above.

Regards,
Dan
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339

ADVERTISEMENT

To add to Dan's comments, I did post a solution using the BuiltInDocumentProperties, but then hastily deleted it when I realised that, if you use it in the BeforeSave event, you will only return the previous user and save time, not the current user and save time.

So where would you place this code- in the Open or another event or as a standalone procedure? In any case you won't get an up-to-date record of who last saved it.
 

pmdown

Board Regular
Joined
May 6, 2002
Messages
71
Hi dk.

I'am sorry. You are right.

I don't trying to be deliberately argumentative, I only wrong have been reading The original question. I didn't see the text in parenthesis: "...lets say on sheet 1, starting A1, A2", and i suppose, that Vera wanted to get Name and Time from another other external Workbook.

Forget my posts.

Best regards.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
I use this in one of my applications, as a share file on the network, the file is transferred to and updated on the users PC then saved back to the network. Each person who saves the file is added to the ongoing log on Sheet1(UserLog). I cleaned up the code here to work on a simple file. JSW

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Builds an on going list of who saved file when.
'Code must go into the "ThisWorkbook" module!
'All data is placed in column "A" adjust width and formats.

With Worksheets("Sheet1")
.Range("A1") = "This file last saved ""By"" who ""On"", list!"
Range("A65536").End(xlUp).Offset(1, 0) = _
"By: " & Application.UserName & ", On: " & Now & " "
End With
End Sub
 

Vera

Banned
Joined
Sep 22, 2002
Messages
33
Hello everybody. I am back to this problem after a while. I was using the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("List1").Range("A1111") = Application.UserName
Sheets("List1").Range("A1112") = Now
End Sub


with no problem, because I turned to all my users the macro security level to low. But unfortunately now we are required by IT guys to have it at Higt. I tried the SelfCert certificate but is not an option.

My question is if there is a way to get the info that I need (who saved last the file and when) or using Excel's functions (I wonder why the damned Cell or Info don't have such capabilities [using Excel 2002 Windows]) or VBA even when the macro security is set at High level. Thank you so much


Vera
 

Forum statistics

Threads
1,144,329
Messages
5,723,736
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top