ExcelBanter - How too set last edited date in excel and which user was it?

Couple of thoughts.
The good news is...the fact that you are not getting a '#NAME?' error
message means that Excel is finding and recognizing the function. The bad
news is that I don't know why the function is not working.

Question 1) Do you have Macros disabled? If so, the function won't run.
Question 2) Are you using a language other than English? If so, see below.

I'm giving you 3 macros.
Sub ListWorkbookProperties() - creates a new worksheet with all the workbook
properties listed out
Sub GetWorkbookProperties() - creates a message box with all the workbook
properties listed out
Sub LastModified_LastSavedBy() - puts the last modified date/time in the
current cell and who last saved the file in next cell down

Run one of the first two macros ['ListWorkbookProperties()' or
'GetWorkbookProperties()']. This will give you a list of your worksheet
properties.

In reference to Question 2...
Look down the list. Is there an item that says 'Last save time'? If not,
is there another term that means the same thing? If so, THAT is the term you
should put in the function 'LastSaved()'.

By-the-way,
Original Author of workbook:
ActiveWorkbook.BuiltinDocumentProperties("Author") .value
Last person to save workbook:
ActiveWorkbook.BuiltinDocumentProperties("Last Author").value

'========= MACROS START HERE =================
'/===========================================/
Sub ListWorkbookProperties()
'List Workbook Properites to new worksheet
' both Built-in and Custom
Dim iRow As Integer, iWorksheets As Integer
Dim i As Integer
Dim x As Integer, y As Integer
Dim objProperty As Object
Dim strResultsTableName As String
Dim strOrigCalcStatus As String

On Error Resume Next

'* Variables * * * * * * * * * * * *
strResultsTableName = "Workbook_Properties"
iRow = 1
'* * * * * * * * * * * * * * * * * *

'save calculation setting
Select Case Application.Calculation
Case xlCalculationAutomatic
strOrigCalcStatus = "Automatic"
Case xlCalculationManual
strOrigCalcStatus = "Manual"
Case xlCalculationSemiautomatic
strOrigCalcStatus = "SemiAutomatic"
Case Else
strOrigCalcStatus = "Automatic"
End Select

'set workbook to manual
Application.Calculation = xlManual

'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count

'redim array
ReDim aryHiddensheets(1 To iWorksheets)

'put hidden sheets in an array, then unhide the sheets
For x = 1 To iWorksheets
If Worksheets(x).Visible = False Then
aryHiddensheets(x) = Worksheets(x).Name
Worksheets(x).Visible = True
End If
Next

'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If UCase(Worksheets(x).Name) = _
UCase(strResultsTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Exit For
End If
Next

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move _
After:=Worksheets(Worksheets.Count)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.Name = strResultsTableName
ActiveWorkbook.ActiveSheet.Range("A1").Value = "Type"
ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name"
ActiveWorkbook.ActiveSheet.Range("C1").Value = _
"Value"
Range("A1:C1").Font.Bold = True

iRow = iRow + 1

'List Builtin Document Properties
For Each objProperty In _
ActiveWorkbook.BuiltinDocumentProperties
With objProperty
Cells(iRow, 1) = "Builtin"
Cells(iRow, 2) = .Name
Cells(iRow, 3) = .Value
End With
iRow = iRow + 1
Next

'List Custom Document Properties
For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
Cells(iRow, 1) = "Custom"
Cells(iRow, 2) = .Name
Cells(iRow, 3) = .Value
End With
iRow = iRow + 1
Next

'formatting
ActiveWindow.Zoom = 75
Columns("A:C").EntireColumn.AutoFit
Columns("C:C").Select
If Selection.ColumnWidth 80 Then
Selection.ColumnWidth = 80
End If

With Selection
.WrapText = True
.HorizontalAlignment = xlLeft
End With

Range("A2").Select
ActiveWindow.FreezePanes = True

're-hide previously hidden sheets
On Error Resume Next
y = UBound(aryHiddensheets)
For x = 1 To y
Worksheets(aryHiddensheets(x)).Visible = False
Next

Range("A2").Select

're-set to original calculation method
Select Case strOrigCalcStatus
Case "Automatic"
Application.Calculation = xlCalculationAutomatic
Case "Manual"
Application.Calculation = xlCalculationManual
Case "SemiAutomatic"
Application.Calculation = _
xlCalculationSemiautomatic
Case Else
Application.Calculation = xlCalculationAutomatic
End Select

Application.Dialogs(xlDialogWorkbookName).Show

End Sub
'/===========================================/
Sub GetWorkbookProperties()
'List Workbook Properites to Msgbox
' both Built-in and Custom
Dim objProperty As Object
Dim strAnswer As String

On Error Resume Next

'List Workbook name and size
strAnswer = "Workbook: " & vbCr & _
Excel.ActiveWorkbook.FullName & vbCr & _
" - Workbook File Size: " & _
Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
"#,##0") & " kb" & vbCr

'List Builtin Document Properties
For Each objProperty In _
ActiveWorkbook.BuiltinDocumentProperties
With objProperty
strAnswer = strAnswer & vbCr & "Builtin - " & _
.Name & " : " & .Value
End With
Next

'List Custom Document Properties
For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
strAnswer = strAnswer & vbCr & "Custom - " & _
.Name & " : " & .Value
End With
Next

MsgBox strAnswer

End Sub
'/===========================================/
Sub LastModified_LastSavedBy()
'put last modified date/time in current cell
'put who last saved the file in next cell down
On Error GoTo err_Sub

ActiveCell.Value = "Last Modified: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
ActiveCell.Offset(1, 0).Value = "Last Saved by: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last author").Value

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:

Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: LastModified_LastSavedBy - " & _
"Module: Module2 - " & Now()
GoTo exit_Sub
End Sub
'/===========================================/
'========= MACROS END HERE =================

Good Luck and Hope This Helps,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

"jinlarse" wrote:

I have made a udf and saved in global.xls and returned to excel. I worksheet
I type =LastSaved()

But nothing happened?

And I also wan't to now which user was it who saved last time? Is it possible?

Thanks
jinlarse

"Gary L Brown" wrote:

Create a user-defined-function.....

'/==========================================/
Public Function LastSaved()
On Error Resume Next
LastSaved = _
activeworkbook.BuiltinDocumentProperties("Last Save Time").value
End Function
'/==========================================/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

"jinlarse" wrote:

I can't find a function too set a formula in top of a document in a excel
document. It should work like the log book in excel.

Thanks, I have tried this one now and it's working.

The macro "LastModified_LastSavedBy()" do exatly what I want, but I have to
run the macro every time to get this activited and it put the result in that
cell where I am, not in that cell I want it to be (same cell every time). It
is possibly to let this run automatic on every save? And there is many users
who use this workbook and I cant get them to run macros everytime, so can
this be used of every one. The worksheet is saved in a common filearea for
the users.

I dont now if this is possibly, but I'm very pleased of your info about this
and your answers you already have produced to me, thanks.

jinlarse

"Gary L Brown" wrote:

Couple of thoughts.
The good news is...the fact that you are not getting a '#NAME?' error
message means that Excel is finding and recognizing the function. The bad
news is that I don't know why the function is not working.

Question 1) Do you have Macros disabled? If so, the function won't run.
Question 2) Are you using a language other than English? If so, see below.

I'm giving you 3 macros.
Sub ListWorkbookProperties() - creates a new worksheet with all the workbook
properties listed out
Sub GetWorkbookProperties() - creates a message box with all the workbook
properties listed out
Sub LastModified_LastSavedBy() - puts the last modified date/time in the
current cell and who last saved the file in next cell down

Run one of the first two macros ['ListWorkbookProperties()' or
'GetWorkbookProperties()']. This will give you a list of your worksheet
properties.

In reference to Question 2...
Look down the list. Is there an item that says 'Last save time'? If not,
is there another term that means the same thing? If so, THAT is the term you
should put in the function 'LastSaved()'.

By-the-way,
Original Author of workbook:
ActiveWorkbook.BuiltinDocumentProperties("Author") .value
Last person to save workbook:
ActiveWorkbook.BuiltinDocumentProperties("Last Author").value

'========= MACROS START HERE =================
'/===========================================/
Sub ListWorkbookProperties()
'List Workbook Properites to new worksheet
' both Built-in and Custom
Dim iRow As Integer, iWorksheets As Integer
Dim i As Integer
Dim x As Integer, y As Integer
Dim objProperty As Object
Dim strResultsTableName As String
Dim strOrigCalcStatus As String

On Error Resume Next

'* Variables * * * * * * * * * * * *
strResultsTableName = "Workbook_Properties"
iRow = 1
'* * * * * * * * * * * * * * * * * *

'save calculation setting
Select Case Application.Calculation
Case xlCalculationAutomatic
strOrigCalcStatus = "Automatic"
Case xlCalculationManual
strOrigCalcStatus = "Manual"
Case xlCalculationSemiautomatic
strOrigCalcStatus = "SemiAutomatic"
Case Else
strOrigCalcStatus = "Automatic"
End Select

'set workbook to manual
Application.Calculation = xlManual

'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count

'redim array
ReDim aryHiddensheets(1 To iWorksheets)

'put hidden sheets in an array, then unhide the sheets
For x = 1 To iWorksheets
If Worksheets(x).Visible = False Then
aryHiddensheets(x) = Worksheets(x).Name
Worksheets(x).Visible = True
End If
Next

'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If UCase(Worksheets(x).Name) = _
UCase(strResultsTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Exit For
End If
Next

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move _
After:=Worksheets(Worksheets.Count)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.Name = strResultsTableName
ActiveWorkbook.ActiveSheet.Range("A1").Value = "Type"
ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name"
ActiveWorkbook.ActiveSheet.Range("C1").Value = _
"Value"
Range("A1:C1").Font.Bold = True

iRow = iRow + 1

'List Builtin Document Properties
For Each objProperty In _
ActiveWorkbook.BuiltinDocumentProperties
With objProperty
Cells(iRow, 1) = "Builtin"
Cells(iRow, 2) = .Name
Cells(iRow, 3) = .Value
End With
iRow = iRow + 1
Next

'List Custom Document Properties
For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
Cells(iRow, 1) = "Custom"
Cells(iRow, 2) = .Name
Cells(iRow, 3) = .Value
End With
iRow = iRow + 1
Next

'formatting
ActiveWindow.Zoom = 75
Columns("A:C").EntireColumn.AutoFit
Columns("C:C").Select
If Selection.ColumnWidth 80 Then
Selection.ColumnWidth = 80
End If

With Selection
.WrapText = True
.HorizontalAlignment = xlLeft
End With

Range("A2").Select
ActiveWindow.FreezePanes = True

're-hide previously hidden sheets
On Error Resume Next
y = UBound(aryHiddensheets)
For x = 1 To y
Worksheets(aryHiddensheets(x)).Visible = False
Next

Range("A2").Select

're-set to original calculation method
Select Case strOrigCalcStatus
Case "Automatic"
Application.Calculation = xlCalculationAutomatic
Case "Manual"
Application.Calculation = xlCalculationManual
Case "SemiAutomatic"
Application.Calculation = _
xlCalculationSemiautomatic
Case Else
Application.Calculation = xlCalculationAutomatic
End Select

Application.Dialogs(xlDialogWorkbookName).Show

End Sub
'/===========================================/
Sub GetWorkbookProperties()
'List Workbook Properites to Msgbox
' both Built-in and Custom
Dim objProperty As Object
Dim strAnswer As String

On Error Resume Next

'List Workbook name and size
strAnswer = "Workbook: " & vbCr & _
Excel.ActiveWorkbook.FullName & vbCr & _
" - Workbook File Size: " & _
Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
"#,##0") & " kb" & vbCr

'List Builtin Document Properties
For Each objProperty In _
ActiveWorkbook.BuiltinDocumentProperties
With objProperty
strAnswer = strAnswer & vbCr & "Builtin - " & _
.Name & " : " & .Value
End With
Next

'List Custom Document Properties
For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
strAnswer = strAnswer & vbCr & "Custom - " & _
.Name & " : " & .Value
End With
Next

MsgBox strAnswer

End Sub
'/===========================================/
Sub LastModified_LastSavedBy()
'put last modified date/time in current cell
'put who last saved the file in next cell down
On Error GoTo err_Sub

ActiveCell.Value = "Last Modified: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
ActiveCell.Offset(1, 0).Value = "Last Saved by: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last author").Value

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:

Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: LastModified_LastSavedBy - " & _
"Module: Module2 - " & Now()
GoTo exit_Sub
End Sub
'/===========================================/
'========= MACROS END HERE =================

Good Luck and Hope This Helps,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

"jinlarse" wrote:

I have made a udf and saved in global.xls and returned to excel. I worksheet
I type =LastSaved()

But nothing happened?

And I also wan't to now which user was it who saved last time? Is it possible?

Thanks
jinlarse

"Gary L Brown" wrote:

Create a user-defined-function.....

'/==========================================/
Public Function LastSaved()
On Error Resume Next
LastSaved = _
activeworkbook.BuiltinDocumentProperties("Last Save Time").value
End Function
'/==========================================/

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

"jinlarse" wrote:

I can't find a function too set a formula in top of a document in a excel
document. It should work like the log book in excel.