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

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Mar 21, 2006.

  1. Guest

    Guest 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.
     
    Guest, Mar 21, 2006
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    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?''.


    "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.
     
    Guest, Mar 21, 2006
    #2
    1. Advertisements

  3. Guest

    Guest 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


    "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
    > gary_brown@ge_NOSPAM.com
    > 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.
     
    Guest, Mar 22, 2006
    #3
  4. Guest

    Guest 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,
    --
    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" 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
    > > gary_brown@ge_NOSPAM.com
    > > 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.
     
    Guest, Mar 22, 2006
    #4
  5. Guest

    Guest 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

    "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
    > gary_brown@ge_NOSPAM.com
    > 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
    > > > gary_brown@ge_NOSPAM.com
    > > > 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.
     
    Guest, Mar 23, 2006
    #5
  6. Guest

    Guest 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?''.


    "jinlarse" wrote:

    > 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
    > > gary_brown@ge_NOSPAM.com
    > > 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
    > > > > gary_brown@ge_NOSPAM.com
    > > > > 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.
     
    Guest, Mar 23, 2006
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Auto-Insert Last Date Edited

    Guest, May 26, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    380
    Ron de Bruin
    May 26, 2005
  2. Guest

    Excel 2003: can I insert a "last edited" date function?

    Guest, Jun 27, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    170
    Bob Phillips
    Jun 27, 2005
  3. Manju

    Go to Last edited cell

    Manju, Aug 30, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    188
    shail
    Aug 30, 2006
  4. Guest

    Recording the date another cell is edited or modified.

    Guest, Dec 2, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    145
    Gord Dibben
    Dec 2, 2006
  5. Author
    Replies:
    5
    Views:
    182
    Ron Rosenfeld
    Apr 2, 2009
Loading...

Share This Page