software

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.

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

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

HTH,
--
Gary Brown

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

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

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
jinlarsequot;Gary L Brownquot; wrote:

gt; Create a user-defined-function.....
gt;
gt; '/==========================================/
gt; Public Function LastSaved()
gt; On Error Resume Next
gt; LastSaved = _
gt; activeworkbook.BuiltinDocumentProperties(quot;Last Save Timequot;).value
gt; End Function
gt; '/==========================================/
gt;
gt; HTH,
gt; --
gt; Gary Brown
gt;
gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; Post Helpfull to you?''.
gt;
gt;
gt; quot;jinlarsequot; wrote:
gt;
gt; gt; I can't find a function too set a formula in top of a document in a excel
gt; gt; document. It should work like the log book in excel.

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(quot;Authorquot;) .value
Last person to save workbook:
ActiveWorkbook.BuiltinDocumentProperties(quot;Last Authorquot;).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 = quot;Workbook_Propertiesquot;
iRow = 1
'* * * * * * * * * * * * * * * * * *

'save calculation setting
Select Case Application.Calculation
Case xlCalculationAutomatic
strOrigCalcStatus = quot;Automaticquot;
Case xlCalculationManual
strOrigCalcStatus = quot;Manualquot;
Case xlCalculationSemiautomatic
strOrigCalcStatus = quot;SemiAutomaticquot;
Case Else
strOrigCalcStatus = quot;Automaticquot;
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(quot;A1quot;).Value = quot;Typequot;
ActiveWorkbook.ActiveSheet.Range(quot;B1quot;).Value = quot;Namequot;
ActiveWorkbook.ActiveSheet.Range(quot;C1quot;).Value = _
quot;Valuequot;
Range(quot;A1:C1quot;).Font.Bold = True

iRow = iRow 1

'List Builtin Document Properties
For Each objProperty In _
ActiveWorkbook.BuiltinDocumentProperties
With objProperty
Cells(iRow, 1) = quot;Builtinquot;
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) = quot;Customquot;
Cells(iRow, 2) = .Name
Cells(iRow, 3) = .Value
End With
iRow = iRow 1
Next

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

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

Range(quot;A2quot;).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(quot;A2quot;).Select

're-set to original calculation method
Select Case strOrigCalcStatus
Case quot;Automaticquot;
Application.Calculation = xlCalculationAutomatic
Case quot;Manualquot;
Application.Calculation = xlCalculationManual
Case quot;SemiAutomaticquot;
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 = quot;Workbook: quot; amp; vbCr amp; _
Excel.ActiveWorkbook.FullName amp; vbCr amp; _
quot; - Workbook File Size: quot; amp; _
Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
quot;#,##0quot;) amp; quot; kbquot; amp; vbCr

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

'List Custom Document Properties
For Each objProperty In _
ActiveWorkbook.CustomDocumentProperties
With objProperty
strAnswer = strAnswer amp; vbCr amp; quot;Custom - quot; amp; _
.Name amp; quot; : quot; amp; .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 = quot;Last Modified: quot; amp; _
ActiveWorkbook.BuiltinDocumentProperties(quot;Last save timequot;).Value
ActiveCell.Offset(1, 0).Value = quot;Last Saved by: quot; amp; _
ActiveWorkbook.BuiltinDocumentProperties(quot;Last authorquot;).Value

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:

Debug.Print quot;Error: quot; amp; Err.Number amp; quot; - (quot; amp; _
Err.Description amp; _
quot;) - Sub: LastModified_LastSavedBy - quot; amp; _
quot;Module: Module2 - quot; amp; 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?''.quot;jinlarsequot; wrote:

gt; I have made a udf and saved in global.xls and returned to excel. I worksheet
gt; I type =LastSaved()
gt;
gt; But nothing happened?
gt;
gt; And I also wan't to now which user was it who saved last time? Is it possible?
gt;
gt; Thanks
gt; jinlarse
gt;
gt;
gt; quot;Gary L Brownquot; wrote:
gt;
gt; gt; Create a user-defined-function.....
gt; gt;
gt; gt; '/==========================================/
gt; gt; Public Function LastSaved()
gt; gt; On Error Resume Next
gt; gt; LastSaved = _
gt; gt; activeworkbook.BuiltinDocumentProperties(quot;Last Save Timequot;).value
gt; gt; End Function
gt; gt; '/==========================================/
gt; gt;
gt; gt; HTH,
gt; gt; --
gt; gt; Gary Brown
gt; gt;
gt; gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; gt; Post Helpfull to you?''.
gt; gt;
gt; gt;
gt; gt; quot;jinlarsequot; wrote:
gt; gt;
gt; gt; gt; I can't find a function too set a formula in top of a document in a excel
gt; gt; gt; document. It should work like the log book in excel.

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

The macro quot;LastModified_LastSavedBy()quot; 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

quot;Gary L Brownquot; wrote:

gt; Couple of thoughts.
gt; The good news is...the fact that you are not getting a '#NAME?' error
gt; message means that Excel is finding and recognizing the function. The bad
gt; news is that I don't know why the function is not working.
gt;
gt; Question 1) Do you have Macros disabled? If so, the function won't run.
gt; Question 2) Are you using a language other than English? If so, see below.
gt;
gt; I'm giving you 3 macros.
gt; Sub ListWorkbookProperties() - creates a new worksheet with all the workbook
gt; properties listed out
gt; Sub GetWorkbookProperties() - creates a message box with all the workbook
gt; properties listed out
gt; Sub LastModified_LastSavedBy() - puts the last modified date/time in the
gt; current cell and who last saved the file in next cell down
gt;
gt; Run one of the first two macros ['ListWorkbookProperties()' or
gt; 'GetWorkbookProperties()']. This will give you a list of your worksheet
gt; properties.
gt;
gt; In reference to Question 2...
gt; Look down the list. Is there an item that says 'Last save time'? If not,
gt; is there another term that means the same thing? If so, THAT is the term you
gt; should put in the function 'LastSaved()'.
gt;
gt; By-the-way,
gt; Original Author of workbook:
gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Authorquot;) .value
gt; Last person to save workbook:
gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Last Authorquot;).value
gt;
gt;
gt; '========= MACROS START HERE =================
gt; '/===========================================/
gt; Sub ListWorkbookProperties()
gt; 'List Workbook Properites to new worksheet
gt; ' both Built-in and Custom
gt; Dim iRow As Integer, iWorksheets As Integer
gt; Dim i As Integer
gt; Dim x As Integer, y As Integer
gt; Dim objProperty As Object
gt; Dim strResultsTableName As String
gt; Dim strOrigCalcStatus As String
gt;
gt; On Error Resume Next
gt;
gt; '* Variables * * * * * * * * * * * *
gt; strResultsTableName = quot;Workbook_Propertiesquot;
gt; iRow = 1
gt; '* * * * * * * * * * * * * * * * * *
gt;
gt; 'save calculation setting
gt; Select Case Application.Calculation
gt; Case xlCalculationAutomatic
gt; strOrigCalcStatus = quot;Automaticquot;
gt; Case xlCalculationManual
gt; strOrigCalcStatus = quot;Manualquot;
gt; Case xlCalculationSemiautomatic
gt; strOrigCalcStatus = quot;SemiAutomaticquot;
gt; Case Else
gt; strOrigCalcStatus = quot;Automaticquot;
gt; End Select
gt;
gt; 'set workbook to manual
gt; Application.Calculation = xlManual
gt;
gt; 'Count number of worksheets in workbook
gt; iWorksheets = ActiveWorkbook.Sheets.Count
gt;
gt; 'redim array
gt; ReDim aryHiddensheets(1 To iWorksheets)
gt;
gt; 'put hidden sheets in an array, then unhide the sheets
gt; For x = 1 To iWorksheets
gt; If Worksheets(x).Visible = False Then
gt; aryHiddensheets(x) = Worksheets(x).Name
gt; Worksheets(x).Visible = True
gt; End If
gt; Next
gt;
gt; 'Check for duplicate Worksheet name
gt; i = ActiveWorkbook.Sheets.Count
gt; For x = 1 To i
gt; If UCase(Worksheets(x).Name) = _
gt; UCase(strResultsTableName) Then
gt; Worksheets(x).Activate
gt; If Err.Number = 9 Then
gt; Exit For
gt; End If
gt; Application.DisplayAlerts = False
gt; ActiveWindow.SelectedSheets.Delete
gt; Application.DisplayAlerts = True
gt; Exit For
gt; End If
gt; Next
gt;
gt; 'Add new worksheet at end of workbook
gt; ' where results will be located
gt; Worksheets.Add.Move _
gt; After:=Worksheets(Worksheets.Count)
gt;
gt; 'Name the new worksheet and set up Titles
gt; ActiveWorkbook.ActiveSheet.Name = strResultsTableName
gt; ActiveWorkbook.ActiveSheet.Range(quot;A1quot;).Value = quot;Typequot;
gt; ActiveWorkbook.ActiveSheet.Range(quot;B1quot;).Value = quot;Namequot;
gt; ActiveWorkbook.ActiveSheet.Range(quot;C1quot;).Value = _
gt; quot;Valuequot;
gt; Range(quot;A1:C1quot;).Font.Bold = True
gt;
gt; iRow = iRow 1
gt;
gt; 'List Builtin Document Properties
gt; For Each objProperty In _
gt; ActiveWorkbook.BuiltinDocumentProperties
gt; With objProperty
gt; Cells(iRow, 1) = quot;Builtinquot;
gt; Cells(iRow, 2) = .Name
gt; Cells(iRow, 3) = .Value
gt; End With
gt; iRow = iRow 1
gt; Next
gt;
gt; 'List Custom Document Properties
gt; For Each objProperty In _
gt; ActiveWorkbook.CustomDocumentProperties
gt; With objProperty
gt; Cells(iRow, 1) = quot;Customquot;
gt; Cells(iRow, 2) = .Name
gt; Cells(iRow, 3) = .Value
gt; End With
gt; iRow = iRow 1
gt; Next
gt;
gt; 'formatting
gt; ActiveWindow.Zoom = 75
gt; Columns(quot;A:Cquot;).EntireColumn.AutoFit
gt; Columns(quot;C:Cquot;).Select
gt; If Selection.ColumnWidth gt; 80 Then
gt; Selection.ColumnWidth = 80
gt; End If
gt;
gt; With Selection
gt; .WrapText = True
gt; .HorizontalAlignment = xlLeft
gt; End With
gt;
gt; Range(quot;A2quot;).Select
gt; ActiveWindow.FreezePanes = True
gt;
gt; 're-hide previously hidden sheets
gt; On Error Resume Next
gt; y = UBound(aryHiddensheets)
gt; For x = 1 To y
gt; Worksheets(aryHiddensheets(x)).Visible = False
gt; Next
gt;
gt; Range(quot;A2quot;).Select
gt;
gt; 're-set to original calculation method
gt; Select Case strOrigCalcStatus
gt; Case quot;Automaticquot;
gt; Application.Calculation = xlCalculationAutomatic
gt; Case quot;Manualquot;
gt; Application.Calculation = xlCalculationManual
gt; Case quot;SemiAutomaticquot;
gt; Application.Calculation = _
gt; xlCalculationSemiautomatic
gt; Case Else
gt; Application.Calculation = xlCalculationAutomatic
gt; End Select
gt;
gt; Application.Dialogs(xlDialogWorkbookName).Show
gt;
gt; End Sub
gt; '/===========================================/
gt; Sub GetWorkbookProperties()
gt; 'List Workbook Properites to Msgbox
gt; ' both Built-in and Custom
gt; Dim objProperty As Object
gt; Dim strAnswer As String
gt;
gt; On Error Resume Next
gt;
gt; 'List Workbook name and size
gt; strAnswer = quot;Workbook: quot; amp; vbCr amp; _
gt; Excel.ActiveWorkbook.FullName amp; vbCr amp; _
gt; quot; - Workbook File Size: quot; amp; _
gt; Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
gt; quot;#,##0quot;) amp; quot; kbquot; amp; vbCr
gt;
gt; 'List Builtin Document Properties
gt; For Each objProperty In _
gt; ActiveWorkbook.BuiltinDocumentProperties
gt; With objProperty
gt; strAnswer = strAnswer amp; vbCr amp; quot;Builtin - quot; amp; _
gt; .Name amp; quot; : quot; amp; .Value
gt; End With
gt; Next
gt;
gt; 'List Custom Document Properties
gt; For Each objProperty In _
gt; ActiveWorkbook.CustomDocumentProperties
gt; With objProperty
gt; strAnswer = strAnswer amp; vbCr amp; quot;Custom - quot; amp; _
gt; .Name amp; quot; : quot; amp; .Value
gt; End With
gt; Next
gt;
gt; MsgBox strAnswer
gt;
gt; End Sub
gt; '/===========================================/
gt; Sub LastModified_LastSavedBy()
gt; 'put last modified date/time in current cell
gt; 'put who last saved the file in next cell down
gt; On Error GoTo err_Sub
gt;
gt; ActiveCell.Value = quot;Last Modified: quot; amp; _
gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Last save timequot;).Value
gt; ActiveCell.Offset(1, 0).Value = quot;Last Saved by: quot; amp; _
gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Last authorquot;).Value
gt;
gt; exit_Sub:
gt; On Error Resume Next
gt; Exit Sub
gt;
gt; err_Sub:
gt;
gt; Debug.Print quot;Error: quot; amp; Err.Number amp; quot; - (quot; amp; _
gt; Err.Description amp; _
gt; quot;) - Sub: LastModified_LastSavedBy - quot; amp; _
gt; quot;Module: Module2 - quot; amp; Now()
gt; GoTo exit_Sub
gt; End Sub
gt; '/===========================================/
gt; '========= MACROS END HERE =================
gt;
gt; Good Luck and Hope This Helps,
gt; --
gt; Gary Brown
gt;
gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; Post Helpfull to you?''.
gt;
gt;
gt; quot;jinlarsequot; wrote:
gt;
gt; gt; I have made a udf and saved in global.xls and returned to excel. I worksheet
gt; gt; I type =LastSaved()
gt; gt;
gt; gt; But nothing happened?
gt; gt;
gt; gt; And I also wan't to now which user was it who saved last time? Is it possible?
gt; gt;
gt; gt; Thanks
gt; gt; jinlarse
gt; gt;
gt; gt;
gt; gt; quot;Gary L Brownquot; wrote:
gt; gt;
gt; gt; gt; Create a user-defined-function.....
gt; gt; gt;
gt; gt; gt; '/==========================================/
gt; gt; gt; Public Function LastSaved()
gt; gt; gt; On Error Resume Next
gt; gt; gt; LastSaved = _
gt; gt; gt; activeworkbook.BuiltinDocumentProperties(quot;Last Save Timequot;).value
gt; gt; gt; End Function
gt; gt; gt; '/==========================================/
gt; gt; gt;
gt; gt; gt; HTH,
gt; gt; gt; --
gt; gt; gt; Gary Brown
gt; gt; gt;
gt; gt; gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; gt; gt; Post Helpfull to you?''.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;jinlarsequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I can't find a function too set a formula in top of a document in a excel
gt; gt; gt; gt; document. It should work like the log book in excel.

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 = quot;Last Saved: quot; amp; _
ActiveWorkbook.BuiltinDocumentProperties(quot;Last save timequot;).Value

exit_Function:
On Error Resume Next
Exit Function

err_Function:

Debug.Print quot;Error: quot; amp; Err.Number amp; quot; - (quot; amp; _
Err.Description amp; quot;) - Function: LastSaved - quot; amp; 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 = quot;Last Saved by: quot; amp; _
ActiveWorkbook.BuiltinDocumentProperties(quot;Last authorquot;).Value

exit_Function:
On Error Resume Next
Exit Function

err_Function:

Debug.Print quot;Error: quot; amp; Err.Number amp; quot; - (quot; amp; _
Err.Description amp; _
quot;) - Function: LastAuthor - quot; amp; Now()
GoTo exit_Function
End Function

'/=====================================/
--
Gary Brown

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

gt; Thanks, I have tried this one now and it's working.
gt;
gt; The macro quot;LastModified_LastSavedBy()quot; do exatly what I want, but I have to
gt; run the macro every time to get this activited and it put the result in that
gt; cell where I am, not in that cell I want it to be (same cell every time). It
gt; is possibly to let this run automatic on every save? And there is many users
gt; who use this workbook and I cant get them to run macros everytime, so can
gt; this be used of every one. The worksheet is saved in a common filearea for
gt; the users.
gt;
gt; I dont now if this is possibly, but I'm very pleased of your info about this
gt; and your answers you already have produced to me, thanks.
gt;
gt; jinlarse
gt;
gt; quot;Gary L Brownquot; wrote:
gt;
gt; gt; Couple of thoughts.
gt; gt; The good news is...the fact that you are not getting a '#NAME?' error
gt; gt; message means that Excel is finding and recognizing the function. The bad
gt; gt; news is that I don't know why the function is not working.
gt; gt;
gt; gt; Question 1) Do you have Macros disabled? If so, the function won't run.
gt; gt; Question 2) Are you using a language other than English? If so, see below.
gt; gt;
gt; gt; I'm giving you 3 macros.
gt; gt; Sub ListWorkbookProperties() - creates a new worksheet with all the workbook
gt; gt; properties listed out
gt; gt; Sub GetWorkbookProperties() - creates a message box with all the workbook
gt; gt; properties listed out
gt; gt; Sub LastModified_LastSavedBy() - puts the last modified date/time in the
gt; gt; current cell and who last saved the file in next cell down
gt; gt;
gt; gt; Run one of the first two macros ['ListWorkbookProperties()' or
gt; gt; 'GetWorkbookProperties()']. This will give you a list of your worksheet
gt; gt; properties.
gt; gt;
gt; gt; In reference to Question 2...
gt; gt; Look down the list. Is there an item that says 'Last save time'? If not,
gt; gt; is there another term that means the same thing? If so, THAT is the term you
gt; gt; should put in the function 'LastSaved()'.
gt; gt;
gt; gt; By-the-way,
gt; gt; Original Author of workbook:
gt; gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Authorquot;) .value
gt; gt; Last person to save workbook:
gt; gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Last Authorquot;).value
gt; gt;
gt; gt;
gt; gt; '========= MACROS START HERE =================
gt; gt; '/===========================================/
gt; gt; Sub ListWorkbookProperties()
gt; gt; 'List Workbook Properites to new worksheet
gt; gt; ' both Built-in and Custom
gt; gt; Dim iRow As Integer, iWorksheets As Integer
gt; gt; Dim i As Integer
gt; gt; Dim x As Integer, y As Integer
gt; gt; Dim objProperty As Object
gt; gt; Dim strResultsTableName As String
gt; gt; Dim strOrigCalcStatus As String
gt; gt;
gt; gt; On Error Resume Next
gt; gt;
gt; gt; '* Variables * * * * * * * * * * * *
gt; gt; strResultsTableName = quot;Workbook_Propertiesquot;
gt; gt; iRow = 1
gt; gt; '* * * * * * * * * * * * * * * * * *
gt; gt;
gt; gt; 'save calculation setting
gt; gt; Select Case Application.Calculation
gt; gt; Case xlCalculationAutomatic
gt; gt; strOrigCalcStatus = quot;Automaticquot;
gt; gt; Case xlCalculationManual
gt; gt; strOrigCalcStatus = quot;Manualquot;
gt; gt; Case xlCalculationSemiautomatic
gt; gt; strOrigCalcStatus = quot;SemiAutomaticquot;
gt; gt; Case Else
gt; gt; strOrigCalcStatus = quot;Automaticquot;
gt; gt; End Select
gt; gt;
gt; gt; 'set workbook to manual
gt; gt; Application.Calculation = xlManual
gt; gt;
gt; gt; 'Count number of worksheets in workbook
gt; gt; iWorksheets = ActiveWorkbook.Sheets.Count
gt; gt;
gt; gt; 'redim array
gt; gt; ReDim aryHiddensheets(1 To iWorksheets)
gt; gt;
gt; gt; 'put hidden sheets in an array, then unhide the sheets
gt; gt; For x = 1 To iWorksheets
gt; gt; If Worksheets(x).Visible = False Then
gt; gt; aryHiddensheets(x) = Worksheets(x).Name
gt; gt; Worksheets(x).Visible = True
gt; gt; End If
gt; gt; Next
gt; gt;
gt; gt; 'Check for duplicate Worksheet name
gt; gt; i = ActiveWorkbook.Sheets.Count
gt; gt; For x = 1 To i
gt; gt; If UCase(Worksheets(x).Name) = _
gt; gt; UCase(strResultsTableName) Then
gt; gt; Worksheets(x).Activate
gt; gt; If Err.Number = 9 Then
gt; gt; Exit For
gt; gt; End If
gt; gt; Application.DisplayAlerts = False
gt; gt; ActiveWindow.SelectedSheets.Delete
gt; gt; Application.DisplayAlerts = True
gt; gt; Exit For
gt; gt; End If
gt; gt; Next
gt; gt;
gt; gt; 'Add new worksheet at end of workbook
gt; gt; ' where results will be located
gt; gt; Worksheets.Add.Move _
gt; gt; After:=Worksheets(Worksheets.Count)
gt; gt;
gt; gt; 'Name the new worksheet and set up Titles
gt; gt; ActiveWorkbook.ActiveSheet.Name = strResultsTableName
gt; gt; ActiveWorkbook.ActiveSheet.Range(quot;A1quot;).Value = quot;Typequot;
gt; gt; ActiveWorkbook.ActiveSheet.Range(quot;B1quot;).Value = quot;Namequot;
gt; gt; ActiveWorkbook.ActiveSheet.Range(quot;C1quot;).Value = _
gt; gt; quot;Valuequot;
gt; gt; Range(quot;A1:C1quot;).Font.Bold = True
gt; gt;
gt; gt; iRow = iRow 1
gt; gt;
gt; gt; 'List Builtin Document Properties
gt; gt; For Each objProperty In _
gt; gt; ActiveWorkbook.BuiltinDocumentProperties
gt; gt; With objProperty
gt; gt; Cells(iRow, 1) = quot;Builtinquot;
gt; gt; Cells(iRow, 2) = .Name
gt; gt; Cells(iRow, 3) = .Value
gt; gt; End With
gt; gt; iRow = iRow 1
gt; gt; Next
gt; gt;
gt; gt; 'List Custom Document Properties
gt; gt; For Each objProperty In _
gt; gt; ActiveWorkbook.CustomDocumentProperties
gt; gt; With objProperty
gt; gt; Cells(iRow, 1) = quot;Customquot;
gt; gt; Cells(iRow, 2) = .Name
gt; gt; Cells(iRow, 3) = .Value
gt; gt; End With
gt; gt; iRow = iRow 1
gt; gt; Next
gt; gt;
gt; gt; 'formatting
gt; gt; ActiveWindow.Zoom = 75
gt; gt; Columns(quot;A:Cquot;).EntireColumn.AutoFit
gt; gt; Columns(quot;C:Cquot;).Select
gt; gt; If Selection.ColumnWidth gt; 80 Then
gt; gt; Selection.ColumnWidth = 80
gt; gt; End If
gt; gt;
gt; gt; With Selection
gt; gt; .WrapText = True
gt; gt; .HorizontalAlignment = xlLeft
gt; gt; End With
gt; gt;
gt; gt; Range(quot;A2quot;).Select
gt; gt; ActiveWindow.FreezePanes = True
gt; gt;
gt; gt; 're-hide previously hidden sheets
gt; gt; On Error Resume Next
gt; gt; y = UBound(aryHiddensheets)
gt; gt; For x = 1 To y
gt; gt; Worksheets(aryHiddensheets(x)).Visible = False
gt; gt; Next
gt; gt;
gt; gt; Range(quot;A2quot;).Select
gt; gt;
gt; gt; 're-set to original calculation method
gt; gt; Select Case strOrigCalcStatus
gt; gt; Case quot;Automaticquot;
gt; gt; Application.Calculation = xlCalculationAutomatic
gt; gt; Case quot;Manualquot;
gt; gt; Application.Calculation = xlCalculationManual
gt; gt; Case quot;SemiAutomaticquot;
gt; gt; Application.Calculation = _
gt; gt; xlCalculationSemiautomatic
gt; gt; Case Else
gt; gt; Application.Calculation = xlCalculationAutomatic
gt; gt; End Select
gt; gt;
gt; gt; Application.Dialogs(xlDialogWorkbookName).Show
gt; gt;
gt; gt; End Sub
gt; gt; '/===========================================/
gt; gt; Sub GetWorkbookProperties()
gt; gt; 'List Workbook Properites to Msgbox
gt; gt; ' both Built-in and Custom
gt; gt; Dim objProperty As Object
gt; gt; Dim strAnswer As String
gt; gt;
gt; gt; On Error Resume Next
gt; gt;
gt; gt; 'List Workbook name and size
gt; gt; strAnswer = quot;Workbook: quot; amp; vbCr amp; _
gt; gt; Excel.ActiveWorkbook.FullName amp; vbCr amp; _
gt; gt; quot; - Workbook File Size: quot; amp; _
gt; gt; Format(FileLen(Excel.ActiveWorkbook.FullName) / 1024, _
gt; gt; quot;#,##0quot;) amp; quot; kbquot; amp; vbCr
gt; gt;
gt; gt; 'List Builtin Document Properties
gt; gt; For Each objProperty In _
gt; gt; ActiveWorkbook.BuiltinDocumentProperties
gt; gt; With objProperty
gt; gt; strAnswer = strAnswer amp; vbCr amp; quot;Builtin - quot; amp; _
gt; gt; .Name amp; quot; : quot; amp; .Value
gt; gt; End With
gt; gt; Next
gt; gt;
gt; gt; 'List Custom Document Properties
gt; gt; For Each objProperty In _
gt; gt; ActiveWorkbook.CustomDocumentProperties
gt; gt; With objProperty
gt; gt; strAnswer = strAnswer amp; vbCr amp; quot;Custom - quot; amp; _
gt; gt; .Name amp; quot; : quot; amp; .Value
gt; gt; End With
gt; gt; Next
gt; gt;
gt; gt; MsgBox strAnswer
gt; gt;
gt; gt; End Sub
gt; gt; '/===========================================/
gt; gt; Sub LastModified_LastSavedBy()
gt; gt; 'put last modified date/time in current cell
gt; gt; 'put who last saved the file in next cell down
gt; gt; On Error GoTo err_Sub
gt; gt;
gt; gt; ActiveCell.Value = quot;Last Modified: quot; amp; _
gt; gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Last save timequot;).Value
gt; gt; ActiveCell.Offset(1, 0).Value = quot;Last Saved by: quot; amp; _
gt; gt; ActiveWorkbook.BuiltinDocumentProperties(quot;Last authorquot;).Value
gt; gt;
gt; gt; exit_Sub:
gt; gt; On Error Resume Next
gt; gt; Exit Sub
gt; gt;
gt; gt; err_Sub:
gt; gt;
gt; gt; Debug.Print quot;Error: quot; amp; Err.Number amp; quot; - (quot; amp; _
gt; gt; Err.Description amp; _
gt; gt; quot;) - Sub: LastModified_LastSavedBy - quot; amp; _
gt; gt; quot;Module: Module2 - quot; amp; Now()
gt; gt; GoTo exit_Sub
gt; gt; End Sub
gt; gt; '/===========================================/
gt; gt; '========= MACROS END HERE =================
gt; gt;
gt; gt; Good Luck and Hope This Helps,
gt; gt; --
gt; gt; Gary Brown
gt; gt;
gt; gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; gt; Post Helpfull to you?''.
gt; gt;
gt; gt;
gt; gt; quot;jinlarsequot; wrote:
gt; gt;
gt; gt; gt; I have made a udf and saved in global.xls and returned to excel. I worksheet
gt; gt; gt; I type =LastSaved()
gt; gt; gt;
gt; gt; gt; But nothing happened?
gt; gt; gt;
gt; gt; gt; And I also wan't to now which user was it who saved last time? Is it possible?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt; jinlarse
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Gary L Brownquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Create a user-defined-function.....
gt; gt; gt; gt;
gt; gt; gt; gt; '/==========================================/
gt; gt; gt; gt; Public Function LastSaved()
gt; gt; gt; gt; On Error Resume Next
gt; gt; gt; gt; LastSaved = _
gt; gt; gt; gt; activeworkbook.BuiltinDocumentProperties(quot;Last Save Timequot;).value
gt; gt; gt; gt; End Function
gt; gt; gt; gt; '/==========================================/
gt; gt; gt; gt;
gt; gt; gt; gt; HTH,
gt; gt; gt; gt; --
gt; gt; gt; gt; Gary Brown
gt; gt; gt; gt;
gt; gt; gt; gt; If this post was helpful, please click the ''Yes'' button next to ''Was this
gt; gt; gt; gt; Post Helpfull to you?''.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;jinlarsequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I can't find a function too set a formula in top of a document in a excel
gt; gt; gt; gt; gt; document. It should work like the log book in excel.