ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Now() - LastSAvedBy?? (https://www.excelbanter.com/excel-discussion-misc-queries/40769-now-lastsavedby.html)

SirSimons

Now() - LastSAvedBy??
 
I need a funktion in my excel sheet, that indicate the person who have saved
a file the last time - Like the NOW() formula, I look for a formula that link
to LastSavedBy

Bob Phillips

'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocument*Properties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


and enter in a cell such as
=DocProps ("last author")
or
=DocProps ("last save time")


This will not update when you save within Excel. If you need this, add

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
ActiveWorkbook.Save
Application.CalculateFull
wb_exit:
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)


"SirSimons" wrote in message
...
I need a funktion in my excel sheet, that indicate the person who have

saved
a file the last time - Like the NOW() formula, I look for a formula that

link
to LastSavedBy




FSt1

hi,
add this to your workbook
Add a sheet then hide it. in the code below, change sheet2 to your hidden
sheet.
any time someone saves the file, this cod will run.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("sheet2").Range("A1") = Now()
Sheets("sheet2").Range("A1").Copy
Sheets("sheet2").Range("A1").PasteSpecial xlPasteValues
Sheets("sheet2").Range("A2").Value = Application.UserName
End Sub

regards
FSt1


"SirSimons" wrote:

I need a funktion in my excel sheet, that indicate the person who have saved
a file the last time - Like the NOW() formula, I look for a formula that link
to LastSavedBy


SirSimons

Thank you for your answers.

I dosen't seems to be working. It could be because I'm running a danish
version og Excel. Anyone who knows where to find that the last author is
defined as "last author" I have tried typing the danish word in the
preferences instead but i doesn't work.

Simonsen

"FSt1" skrev:

hi,
add this to your workbook
Add a sheet then hide it. in the code below, change sheet2 to your hidden
sheet.
any time someone saves the file, this cod will run.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("sheet2").Range("A1") = Now()
Sheets("sheet2").Range("A1").Copy
Sheets("sheet2").Range("A1").PasteSpecial xlPasteValues
Sheets("sheet2").Range("A2").Value = Application.UserName
End Sub

regards
FSt1


"SirSimons" wrote:

I need a funktion in my excel sheet, that indicate the person who have saved
a file the last time - Like the NOW() formula, I look for a formula that link
to LastSavedBy


Bob Phillips

Simonsen,

My response got a - embedded in it so wouldn't work.

Look at the function and remove the - between Builtin and DocumentProperties
and see if it works.

If not use this version

'-----------------------------------------------------------------
Function DocProps(prop)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

where you can use the index instead of the name (last author = 7, last save
time = 12)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SirSimons" wrote in message
...
Thank you for your answers.

I dosen't seems to be working. It could be because I'm running a danish
version og Excel. Anyone who knows where to find that the last author is
defined as "last author" I have tried typing the danish word in the
preferences instead but i doesn't work.

Simonsen

"FSt1" skrev:

hi,
add this to your workbook
Add a sheet then hide it. in the code below, change sheet2 to your

hidden
sheet.
any time someone saves the file, this cod will run.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As

Boolean)
Sheets("sheet2").Range("A1") = Now()
Sheets("sheet2").Range("A1").Copy
Sheets("sheet2").Range("A1").PasteSpecial xlPasteValues
Sheets("sheet2").Range("A2").Value = Application.UserName
End Sub

regards
FSt1


"SirSimons" wrote:

I need a funktion in my excel sheet, that indicate the person who have

saved
a file the last time - Like the NOW() formula, I look for a formula

that link
to LastSavedBy





All times are GMT +1. The time now is 03:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com