Username The Sequel

[Guest]

Onedaywhen's answer to Lawlera's query about restricting changes to the Username box under Options was:

"That's why you should always check the username (or full name) they
used to log on."

I am trying to prevent impersonation in a shared workbook with tracked changes. What does the above reply mean (How to check? How can it be recorded?) and how could I use it to stop abuse of the Username box?

Reply in very straightforward layman's terms would be very much appreciated. Thanks.

 

[Bob Phillips]

What he means is that the Username under Options is just a string which can
be changed and cannot be relied upon. Better to rely upon the username that
the user logs into the machine with, and use this value. This simple UDF
returns the logged on user name

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long) As Long

Public Function UserName() As String
Dim sName As String * 256
Dim cChars As Long
cChars = 256
If GetUserName(sName, cChars) Then
UserName = Left$(sName, cChars - 1)
End If
End Function

So where you use Application.Username, add a call to this function instead.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Onedaywhen's answer to Lawlera's query about restricting changes to the

Username box under Options was:

"That's why you should always check the username (or full name) they
used to log on."

I am trying to prevent impersonation in a shared workbook with tracked

changes. What does the above reply mean (How to check? How can it be
recorded?) and how could I use it to stop abuse of the Username box?

 

[Guest]

Bob, thank you very much for your prompt help

Unfortunately I don't know what to do with the code you so kindly wrote. (No politically incorrect suggestions, please!
Tried to put it into a cell with the function CALL but couldn't work out which bits went where
I have made some usable workbooks using a very wide range of Excel capabilities (learning from Help) but have never done Visual Basic; presumably that's my problem
However, if there is a simple hint you can give me, I'd be very grateful.
Otherwise I guess I'll have to look for a programmer who is willing to do this one thing..

In any case, thanks again. Oh — your email reply address bounced..

teabag

 

[Chip Pearson]

If you want to call the function from a worksheet cell, place the
code in a standard code module (not the ThisWorkbook code module
and not one of the sheet modules), and call it directly with a
formula like

=UserName()

If you want to call the function from within VBA, use code like

Dim UName As String
UName = UserName()


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Bob, thank you very much for your prompt help.

Unfortunately I don't know what to do with the code you so

kindly wrote. (No politically incorrect suggestions, please!)

Tried to put it into a cell with the function CALL but couldn't

work out which bits went where.

I have made some usable workbooks using a very wide range of

Excel capabilities (learning from Help) but have never done
Visual Basic; presumably that's my problem!

However, if there is a simple hint you can give me, I'd be very grateful.
Otherwise I guess I'll have to look for a programmer who is

willing to do this one thing...

 

[Bob Phillips]

I'll give it a try

Go to the VB IDE (Alt-F11)
Click on the workbook name in the project explorer window (top left of
window)
Click Insert>Module
Paste the code I supplied previously in there in the code module that opens
up
In the project explorer, ensure that the 'Microsoft Excel Objects' section
is expanded (the + sign beside it changes to a -)
Double-click the ThisWorkbook entry
Add this code to that code module

Private Sub Workbook_Open()

Application.UserName = UserName()

End Sub

Go back to Excel and any actions logged on the shared workbook should now
record the logged on user. I am not sure this will work as I never use
shared workbooks, but the principle seems right and the app username does
get updated.

By the way, my email is anti-spammed. My signature tells how to overcome it
so that it doesn't get bounced.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob, thank you very much for your prompt help.

Unfortunately I don't know what to do with the code you so kindly wrote.

(No politically incorrect suggestions, please!)

Tried to put it into a cell with the function CALL but couldn't work out which bits went where.
I have made some usable workbooks using a very wide range of Excel

capabilities (learning from Help) but have never done Visual Basic;
presumably that's my problem!