Date stamp user

[Stuart WJG]

I would like to date stamp spreadsheet any time it is updated along with user
name of computer doing it
cheers Stuart

 

[Gord Dibben]

Where would like to see this information?

This code placed into Thisworkbook module will put the info in a cell.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = Environ("Username") & " " _
& Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
End Sub


Gord Dibben MS Excel MVP

 

[Stuart WJG]

I have put the below in the worksheet but only get the date. I presume in the
sheet is named ENQUIRY i replace "sheet 1" below with "ENQUIRY" and change
the range to whatever call i want the info

Stuart

 

[Gord Dibben]

Yes.........change sheet1 to your sheet name and adjust the range.

Are you not seeing the username of the logged in user?

A re-read of your original shows you want computer name, not username.

To get computer name..........not logged-in user.

Add these to a General module.

Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _
As Long


Public Function NameOfComputer()
' Returns the name of the computer
Dim ComputerName As String
Dim ComputerNameLen As Long
Dim Result As Long
ComputerNameLen = 256
ComputerName = Space(ComputerNameLen)
Result = GetComputerName(ComputerName, ComputerNameLen)
If Result <> 0 Then
NameOfComputer = Left(ComputerName, ComputerNameLen)
Else
NameOfComputer = "Unknown"
End If
End Function

Then add to Thisworkbook module this new code. Editing sheet1 and range as
you wish.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("A1").Value = NameOfComputer() & " " _
& Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"yyyy-mmm-dd hh:mm:ss")
End Sub


Gord

 

[vaa571]

Hi Gord,

Can you please guide me step by step how to do this or point me to the right
direction?

Thanks

Val

 

[Stuart WJG]

Hi Gord

Still did not work. But this does,As I couls already to date stamp I added
your bit after "environ

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("H1").Value = Environ("Username") & "" & " " _
& Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
"dd-mmm-yy hh:mm")
Application.EnableEvents = True
End Sub
Many thanks
Stuart

 

[Gord Dibben]

I don't know why you have altered to a worksheet change event.

That will update every time any change is on Sheet1 made even if the
workbook is closed without saving.

Could lead to monitoring problems in my estimation, but if you're happy I'll
leave you be.


Gord

 

[Gord Dibben]

One other point with using worksheet code.............only that Sheet1 will
stamp when a user makes changes.

Any other sheet and no stamp.

If the Environ("Username") works in the sheet event code I can't see why you
get no username when running the original Beforesave code from Thisworkbbok
module.


Gord