VBA to log times workbook has been opened into rows ?

[JumboCactuar]

Hi,
i want to be able to monitor what users have used the workbook and log the info into rows.
Date + Time is enough but is it also possible to get current windows user in excel ?


example - Sheet("Log")
27/12/2016 07:14 Bob
27/12/2016 09:47 Tim
27/12/2016 12:41 James

thanks

 

[JumboCactuar]

done this, it works fine but maybe theres a better solution

Sub Macro3()
    Dim strUser As String
    strUser = Environ("UserName")
    
    Range("A1").FormulaR1C1 = "=NOW()"
    Range("B1").FormulaR1C1 = strUser
    Range("A1").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

 

[JumboCactuar]

and better:

Private Sub Workbook_Open()
    Dim strUser As String
    strUser = Environ("UserName")
   Sheets("Log").Range("A1").FormulaR1C1 = "=NOW()"
    Sheets("Log").Range("B1").FormulaR1C1 = strUser
    Sheets("Log").Range("A1").Copy
    Sheets("Log").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Log").Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

 

[JackDanIce]

Hi Jumbo, you can reduce your code further to:

Private Sub Workbook_Open()
    
    With Sheets("Log").Cells(1, 1)
        .Value = Format(Now, "DD/MM/YYYY HH:MM")
        .Offset(, 1).Value = Environ("UserName")
        .EntireRow.Insert
    End With
        
End Sub

However, your example is suggesting a reversed order to what your code is doing provides (Last opened at bottom of list, but your code places in row 1 before shifting down), if so alternative to consider:

Private Sub Workbook_Open()
    
    With Sheets("Log").Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Value = Format(Now, "DD/MM/YYYY HH:MM")
        .Offset(, 1).Value = Environ("UserName")
    End With
        
End Sub

 

[JumboCactuar]

ADVERTISEMENT

Thats much better
Thanks

 

[JackDanIce]

No problem and you're welcome

 

[JumboCactuar]

No problem and you're welcome

i just added ActiveWorkbook.Save after the End With to ensure the logged info stays