Results 1 to 7 of 7

Thread: VBA - date, time and author for each worktab

  1. #1

    VBA - date, time and author for each worktab



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    i have a spread sheet with over 20 worktabs and needs to show the date, time and author every time each worktab is modified in anyway.

    I am currently using the code shown below in each worktab but this is only showing date, time etc on one tab at a time even if multiple tabs have been modified.

    Code:
    Private Sub Workbook_Open()
    Dim last_auth As String
    Dim last_save As String
    last_auth = ThisWorkbook.BuiltinDocumentProperties("Last Author")
    last_save = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
    ThisWorkbook.BuiltinDocumentProperties("Last Author") = last_auth
    ThisWorkbook.BuiltinDocumentProperties("Last Save Time") = last_save
    Range("E2") = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
    Range("E3") = ThisWorkbook.BuiltinDocumentProperties("Last Author")
    End Sub
    can you advise on this please?
    Last edited by Bob Phillips; 2012-08-06 at 11:54 AM. Reason: Added code tags

  2. #2
    Put this in ThisWorkbook code module

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
        Application.EnableEvents = False
        
        With Sh
        
            .Range("E2").Value = Now
            .Range("E2").NumberFormat = "dd mmm yyyy hh:mm:ss"
            .Range("E3").Value = Environ("Username")
        End With
    
    
        Application.EnableEvents = True
    End Sub

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Put this in ThisWorkbook code module

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
        Application.EnableEvents = False
        
        With Sh
        
            .Range("E2").Value = Now
            .Range("E2").NumberFormat = "dd mmm yyyy hh:mm:ss"
            .Range("E3").Value = Environ("Username")
        End With
    
    
        Application.EnableEvents = True
    End Sub
    Hi Bob - many thanks for the quick reply - is there anyway of using "Last Author" instead of "username" so it shows my name "Stephen Jones" instead of my actual login joness66?

  4. #4
    Use

    Application.Username

  5. #5
    Hi Bob, not sure if I have this correct but this does not work, no author name is showing?


    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False

    With Sh

    .Range("E2").Value = Now
    .Range("E2").NumberFormat = "dd mmm yyyy hh:mm:ss"
    .Range("E3").Value = Environ("Application.Username")
    End With

    Application.EnableEvents = True
    End Sub

  6. #6
    No, just

    .Range("E3").Value = Application.Username

  7. #7
    Hi Bob, worked like a dream! many thanks for your help, much appreciated!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •