Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
Hello. I found code that allows me to display document properties for
a workbook and I am using it to show the last save time and last author as well as other document properties. But I think the code only updates the first worksheet. Would I need to add the code to each worksheet or is there a way to have it update all worksheets simultaneously? Thanks. Here's the code: <code Sub DocumentPropertiesLastModified() Dim a As String Dim b As String Dim c As String Dim d As String 'Worksheets(1).Activate d = ActiveWorkbook.BuiltinDocumentProperties("title") Cells(1, 1).Select ActiveCell.FormulaR1C1 = d a = ActiveWorkbook.BuiltinDocumentProperties("last save time") Cells(2, 5).Select ActiveCell.FormulaR1C1 = "last modified:" & " " & a b = ActiveWorkbook.BuiltinDocumentProperties("Author") Cells(1, 5).Select ActiveCell.FormulaR1C1 = "created by:" & " " & b c = ActiveWorkbook.BuiltinDocumentProperties("Last author") Cells(3, 5).Select ActiveCell.FormulaR1C1 = "last edited by:" & " " & c End Sub </code |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
Try:
Sub DocumentPropertiesLastModified() Dim Ttle As String Dim lstSave As String Dim Athr As String Dim lstAthr As String Dim wks As Worksheet With ActiveWorkbook Ttle = .BuiltinDocumentProperties("title") lstSave = .BuiltinDocumentProperties("last save time") Athr = .BuiltinDocumentProperties("Author") lstAthr = .BuiltinDocumentProperties("Last author") For Each wks In .Worksheets wks.Cells(1, 1).FormulaR1C1 = Ttle wks.Cells(2, 5).FormulaR1C1 = _ "last modified:" & " " & lstSave wks.Cells(1, 5).FormulaR1C1 = "created by:" & " " & Athr wks.Cells(3, 5).FormulaR1C1 = _ "last edited by:" & " " & lstAthr Next wks End With End Sub Hope this helps Rowan the majestic ferny wrote: Hello. I found code that allows me to display document properties for a workbook and I am using it to show the last save time and last author as well as other document properties. But I think the code only updates the first worksheet. Would I need to add the code to each worksheet or is there a way to have it update all worksheets simultaneously? Thanks. Here's the code: <code Sub DocumentPropertiesLastModified() Dim a As String Dim b As String Dim c As String Dim d As String 'Worksheets(1).Activate d = ActiveWorkbook.BuiltinDocumentProperties("title") Cells(1, 1).Select ActiveCell.FormulaR1C1 = d a = ActiveWorkbook.BuiltinDocumentProperties("last save time") Cells(2, 5).Select ActiveCell.FormulaR1C1 = "last modified:" & " " & a b = ActiveWorkbook.BuiltinDocumentProperties("Author") Cells(1, 5).Select ActiveCell.FormulaR1C1 = "created by:" & " " & b c = ActiveWorkbook.BuiltinDocumentProperties("Last author") Cells(3, 5).Select ActiveCell.FormulaR1C1 = "last edited by:" & " " & c End Sub </code |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
That worked! Thanks a lot Rowan :)
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
You're welcome. You may want to include this in the workbook_open event
so that it is kept up to date. the majestic ferny wrote: That worked! Thanks a lot Rowan :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
Aha! That was my next question. I realized it wouldn't update till I
ran the code. Got that working, but is there a way to do it on Save? Or does it really matter? Rowan Drummond wrote: You're welcome. You may want to include this in the workbook_open event so that it is kept up to date. the majestic ferny wrote: That worked! Thanks a lot Rowan :) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
Only you would know if it really matters. I would probably go for the
workbook_open event. Paste the following in the ThisWorkbook module in the VBA project. Private Sub Workbook_Open() Dim Ttle As String Dim lstSave As String Dim Athr As String Dim lstAthr As String Dim wks As Worksheet With Me Ttle = .BuiltinDocumentProperties("title") lstSave = .BuiltinDocumentProperties("last save time") Athr = .BuiltinDocumentProperties("Author") lstAthr = .BuiltinDocumentProperties("Last author") For Each wks In .Worksheets wks.Cells(1, 1).FormulaR1C1 = Ttle wks.Cells(2, 5).FormulaR1C1 = _ "last modified:" & " " & lstSave wks.Cells(1, 5).FormulaR1C1 = "created by:" & " " & Athr wks.Cells(3, 5).FormulaR1C1 = _ "last edited by:" & " " & lstAthr Next wks End With End Sub Regards Rowan the majestic ferny wrote: Aha! That was my next question. I realized it wouldn't update till I ran the code. Got that working, but is there a way to do it on Save? Or does it really matter? Rowan Drummond wrote: You're welcome. You may want to include this in the workbook_open event so that it is kept up to date. the majestic ferny wrote: That worked! Thanks a lot Rowan :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
Hi Rowan. Is there a way to have it work on both Open() and
BeforeSave() events? If I try to just add another sub with BeforeSave() I get a compile error. I guess I would like the cell values to update if a user saves the document but then keeps working on it. Thanks for your great assistance! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to update last save time & last author for each worksheet
Hi
When using events you must match the event signature set out in VBA so the BeforeSave event must be: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) 'code End Sub The problem with this is that it runs BEFORE the save at which point the properties will not yet have been updated. You may be able to play around with it to have it save, update the cells on your sheets and then save again. You can find more info on events from Chip Pearson at: http://www.cpearson.com/excel/events.htm Regards Rowan More on events at: the majestic ferny wrote: Hi Rowan. Is there a way to have it work on both Open() and BeforeSave() events? If I try to just add another sub with BeforeSave() I get a compile error. I guess I would like the cell values to update if a user saves the document but then keeps working on it. Thanks for your great assistance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Date, Time Only Upon Save | Excel Discussion (Misc queries) | |||
workbook update each time a new worksheet is selected | New Users to Excel | |||
Update workbook each time a different worksheet is selected | New Users to Excel | |||
save from template to worksheet without update commands | Excel Programming | |||
Time stamping - automated by worksheet update | Excel Programming |