Need User Name

[Jan Nademlejnsky]

Is it possible to get "User Name" variable to see who used the Excel sheet
last time?

I have many sheets which are shared be many people, but somebody is messing
up and I cannot figure out where the problem is coming from.

I would write macro to record User Name and time before Save, but I need
that variable.

Thank you for your help

Jan

 

[Gary''s Student]

Try this event macro in the workbook code area:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet3").Activate
n = Application.WorksheetFunction.Max(2, Cells(Rows.Count, 1).End(xlUp).Row
+ 1)
Cells(n, 1).Value = Environ("Username")
Cells(n, 2).Value = Now
End Sub

 

[Chip Pearson]

You can get the "Last Author" built in document property. This is the
User Name as entered in the "User name" item on the General tab of the
Options dialog (Tools menu, Options item). This can be changed by the
user, so while it may well suffice for most purposes, it shouldn't be
relied upon for security and audit purposes.

You could add a Custom Document Property containing the user's logon
ID. In the ThisWorkbook module, paste the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
On Error Resume Next
Dim CustomDocProps As Office.DocumentProperties
Set CustomDocProps = ThisWorkbook.CustomDocumentProperties
CustomDocProps.Add _
Name:="Last User ID", _
LinkToContent:=False, _
Type:=msoPropertyTypeString, _
Value:=Environ("UserName")
End Sub


This will create a custom property named "Last User ID" and give it a
value of the user's logon name. This property will be visible and
changeable in the Properties dialog (File menu, Properties), so the
dishonest user could circumvent it with a bit of effort.

Another way is to create a hidden defined name with the user's logon
ID.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ThisWorkbook.Names.Add Name:="LastLogonID", _
RefersTo:=Environ("UserName"), _
Visible:=False
End Sub

This, too, could be circumvented by a skilled user. There really isn't
any fool-proof way to ensure that the UserName value can't be spoofed
by a skilled user. That said, any of these methods may well be "good
enough" for general use.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)