G
Guest
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.
document. It should work like the log book in excel.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Create a user-defined-function.....
'/==========================================/
Public Function LastSaved()
On Error Resume Next
LastSaved = _
activeworkbook.BuiltinDocumentProperties("Last Save Time").value
End Function
'/==========================================/
HTH,
'/==========================================/
Public Function LastSaved()
On Error Resume Next
LastSaved = _
activeworkbook.BuiltinDocumentProperties("Last Save Time").value
End Function
'/==========================================/
HTH,
G
Guest
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
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
G
Guest
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,
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,
G
Guest
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
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 said: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
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
jinlarse said: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
G
Guest
Here are 2 separate user-defined-functions that will do what you want..
Put them in your workbook.
The syntax is...
=LastSaved()
and
=LastAuthor()
'/=====================================/
' Function Purpose: Last time current workbook was saved
'
Public Function LastSaved()
On Error GoTo err_Function
Application.Volatile
LastSaved = "Last Saved: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
exit_Function:
On Error Resume Next
Exit Function
err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & ") - Function: LastSaved - " & Now()
GoTo exit_Function
End Function
'/=====================================/
' Function Purpose: Last Person to save the current workbook
'
Public Function LastAuthor()
On Error GoTo err_Function
Application.Volatile
LastAuthor = "Last Saved by: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last author").Value
exit_Function:
On Error Resume Next
Exit Function
err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: LastAuthor - " & Now()
GoTo exit_Function
End Function
'/=====================================/
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
Put them in your workbook.
The syntax is...
=LastSaved()
and
=LastAuthor()
'/=====================================/
' Function Purpose: Last time current workbook was saved
'
Public Function LastSaved()
On Error GoTo err_Function
Application.Volatile
LastSaved = "Last Saved: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last save time").Value
exit_Function:
On Error Resume Next
Exit Function
err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & ") - Function: LastSaved - " & Now()
GoTo exit_Function
End Function
'/=====================================/
' Function Purpose: Last Person to save the current workbook
'
Public Function LastAuthor()
On Error GoTo err_Function
Application.Volatile
LastAuthor = "Last Saved by: " & _
ActiveWorkbook.BuiltinDocumentProperties("Last author").Value
exit_Function:
On Error Resume Next
Exit Function
err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: LastAuthor - " & Now()
GoTo exit_Function
End Function
'/=====================================/
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
jinlarse said: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 said: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
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
jinlarse said: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
:
Create a user-defined-function.....
'/==========================================/
Public Function LastSaved()
On Error Resume Next
LastSaved = _
activeworkbook.BuiltinDocumentProperties("Last Save Time").value
End Function
'/==========================================/
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
:
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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.