How can I create a last modified macro for each worksheet?

[jburrell]

Hi All,

I want to create a macro so that I can see the last modified date for each worksheet not the entire workbook*. How do I go about this? I tried to modify the last modified formula so that it is specific to a sheet and then I would create a new one for each sheet that I want to see as per below and then i did =LastModifiedk() in a cell. However, I get a "VALUE!" error. Thanks!

[#2]

@jburrell 

Document Properties only apply to the workbook as a whole, not to individual worksheets.

It would be possible to write VBA to register when the user last changed the value of any cell in a worksheet. Where would you like this information to be stored? In a cell on the worksheet itself, or on a summary sheet, or ...?

[#3]

Hi Hans,

Ah ok, that sounds more of what I am looking for. Yes, I would like to show if a user has amended any cell in a worksheet. I want to store the information on a summary sheet.

Thanks,
Jamie

[#4]

@jburrell 

See the attached sample workbook. The code is in the ThisWorkbook module (under Microsoft Excel Objects in the Project Explorer pane of the Visual Basic Editor).

You'll have to allow macros when you open it, obviously.

[#5]

@Hans Vogelaar 

Thanks for sending that over. I am very new to VBA. Would I need to change where it says "Summary" to the sheet name where I want to display it? And how do I pull the data in for each sheet? 

Thanks for your help!

[#6]

@jburrell 

Yes. Change the name Summary in the following line to the name you want to use.

    Set wsh = Worksheets("Summary")

The code will run automatically whenever you change a cell on a worksheet.

[#7]

Thanks Hans. And when I copy the code to my own workshop, how do I get the code to run? Do I need to a formula into the cell I want it to appear? Thanks again (sorry for my lack of knowledge)

[#8]

@jburrell 

Copy the code from the ThisWorkbook module in the sample workbook to the ThisWorkbook module of your own workbook.

Save your workbook as a macro-enabled workbook (*.xlsm), if it isn't a macro-enabled workbook already.

Make sure that you allow macros when you open it.

 

[#9]

Got it. I have put it on the workbook code. How do I get it to then list the sheet modified dates for the sheets I want to see in a specific cell? Thanks

[#10]

@jburrell 

Have you tested editing some cells in various sheets? The code should take care of everything automatically.

Let me know if it doesn't do what you want.

[#11]

Ah yes, I can see it populating now. How can I specify which sheets I want to see? (I do not want it to generate for every sheet clicked on only specific ones). Is it also possible to choose which cells it populates in? I can see it appear but I need it to be in another place and when I move it to another cell and edit again it reappears in the original space. Thanks!

[#12]

@jburrell 

If you want a timestamp for the majority of sheets, specify the ones you don't want to have a timestamp.

If you want a timestamp for a minority of sheets, specify the ones you do want to have a timestamp.

Please explain in detail where you want the timestamps to appear.