Returning User Initials

[Kelly]

Hi all, I am using the Office 2000 package

In Word VB you can return user initials
with "Application.UserInitials"

Is there any way to do this in Excel?

I know you can return user name
with "Application.UserName" but no luck with initials so
far.

Also Where does the user name info come from anyway?
In "Word" it is entered in tools/Options/UserInformation
but I have no idea for Excel.


Thanks as always!

Kelly

 

[John Wilson]

Kelly,

Here's some code to get the initials.
They'll be stored as a Public variable named "UNInit"

Option Explicit
Public UNInit As String
Sub GetInit()
' This procedure will get the initials of the user from
' the Application.UserName
' 1st test is to see if there is an Application.UserName
' This is probably unnecessary as I don't think that Excel
' will let you install Excel without some kind of a User Name
If Application.UserName = "" Then
UNInit = "unk"
Exit Sub
End If
' Find out where the space is
Dim SPacePosit As Integer
SPacePosit = InStr(1, Application.UserName, " ", 1)
If SPacePosit = 0 Then
' There are no spaces. Just get the left character of the first name

UNInit = Left(Application.UserName, 1)
Else
' There's at least one space. Get the left character of the first
name
' along with the first character after the space
UNInit = Left(Application.UserName, 1) & _
Mid(Application.UserName, SPacePosit + 1, 1)
End If
End Sub

In "Word" it is entered in tools/Options/UserInformation

In Excel it's under Tools/Options/General "User Name"

John

 

[Richard Choate]

Some people make Excel utilities which will include new functions that you
can use just like the built-in Excel functions. One of these Utlilities is
the Power Utility Pack by John Walkenbach. Here is the link
http://www.j-walk.com/ss/pup/pup2000/index.htm This includes a function
called User(). This function returns the username described in my other
posted response. You can then nest the function into another function which
will give you the initials.

You can also create your own custom function for this and then nest that.
I'll help you if you want to go that route.

HTH
Richard Choate

Hi all, I am using the Office 2000 package

In Word VB you can return user initials
with "Application.UserInitials"

Is there any way to do this in Excel?

I know you can return user name
with "Application.UserName" but no luck with initials so
far.

Also Where does the user name info come from anyway?
In "Word" it is entered in tools/Options/UserInformation
but I have no idea for Excel.


Thanks as always!

Kelly

 

[Kelly]

Thanks to you both. Your advice did help me to understand
a little better but unfortunaly (or fortunaltley depending
how you look at it) I also now relize the code I build is
not as secure or as reliable as I firt thought.

First of all good advice Jhon but I need all three
initials and it appears company standards were to only
enter the first and last name of the employee when
installing Office.

Secondly I found a way to break into my spreadsheet to
read information that was not meant for me.

See the way I had set it up is that when you opened the
template it would automaticly find the Application .user
and based on this prefor a bunch of V-look ups on a
verryhidden conrtol sheet. Now I realize that the user
only has to change the info in his General options to look
at other users info.

I know that it is not real secure as a hidden sheet but I
think more so then this. Also I did a little testing and
if there is even an extra space in the info under general
options then the v-lookup crashes.

As far as the initals, I just added anonther column to my
hidden sheet and added another v-lookup to access the
initials but I am a little worried about the other issues
now.

Any other suggestions as to how to return PC user
information? I would like to be able to referance the
users Windows log-on ID but have no idea to do that?

Thanks for all your help.

Kelly

 

[Richard Choate]

this will get you the windows login:


Option Compare Database
' Access the GetUserNameA function in advapi32.dll and
' call the function GetUserName.

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

' Main routine to Dimension variables, retrieve user name
' and display answer.

Sub GetUser()

Dim lpBuff As String * 25
Dim ret As Long, UserName As String

' Get the user name minus any trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)


More code blah blah blah

end sub



--
HTH
Richard Choate, CPA
Thanks to you both. Your advice did help me to understand
a little better but unfortunaly (or fortunaltley depending
how you look at it) I also now relize the code I build is
not as secure or as reliable as I firt thought.

First of all good advice Jhon but I need all three
initials and it appears company standards were to only
enter the first and last name of the employee when
installing Office.

Secondly I found a way to break into my spreadsheet to
read information that was not meant for me.

See the way I had set it up is that when you opened the
template it would automaticly find the Application .user
and based on this prefor a bunch of V-look ups on a
verryhidden conrtol sheet. Now I realize that the user
only has to change the info in his General options to look
at other users info.

I know that it is not real secure as a hidden sheet but I
think more so then this. Also I did a little testing and
if there is even an extra space in the info under general
options then the v-lookup crashes.

As far as the initals, I just added anonther column to my
hidden sheet and added another v-lookup to access the
initials but I am a little worried about the other issues
now.

Any other suggestions as to how to return PC user
information? I would like to be able to referance the
users Windows log-on ID but have no idea to do that?

Thanks for all your help.

Kelly

 

[Dave Peterson]

If you trust Words initials, you could just retrieve them from there:

Option Explicit
Sub testme()

Dim myInits As String
Dim wdObject As Object
Dim wordWasRunning As Boolean

wordWasRunning = True
On Error Resume Next
Set wdObject = GetObject(, "word.application")
If Err.Number <> 0 Then
Err.Clear
Set wdObject = CreateObject("word.application")
wordWasRunning = False
End If

myInits = wdObject.UserInitials

If wordWasRunning Then
'do nothing
Else
wdObject.Quit
End If

Set wdObject = Nothing

MsgBox myInits

End Sub

 

[John Wilson]

Kelly,

Excel is by no means secure and any protection scheme that
you try to implement can be circumvented by an experienced user.

Your original question was to find the UserName which I think
has been answered (as for the initials with a middle initial, that can
be done also) but now I get the impression that you're looking at this
from a security issue??

One of the best ways (though still not secure) would be to test
against the Hard Disk Drive Serial Number.

If you'd like, I can supply some code to do this.

John

 

[Kelly]

Hi all, thanks again for some great advice.

Dave I think if I can make the Log In Id solution work
then that will be the way to go, But thanks I now know how
to return Word info.

John, Thanks, I do relize that Excel is not real secure
but I think if I can at least make this sheet secure
against a simple change of the tools/options/general Name
I'll feel a little better. Also If i can get this to work
then users can fill out their expense cards on any PC they
log on to.

Richard..

Thanks for the great code but I am still having a little
trouble, hope you don't mind more questions?

Any way I set up the code to run in and on activate event
and it works fine to bring up MY windows log in id on MY
PC. But when somone eles signs onto my pc and opens the
spreadsheet it appears to not even run? Well at least it
does not return MY Login id but it won't return any log in
id though??

Any futher ideas? Also when I place the template on a
common user network and other users access it on there own
PC it works for them but still only if they are logged
into there own PC.

????

 

[Richard Choate]

keep in mind that the code I sent is in 2 parts. The first part is the
declaration part for the API call. The next part is the subroutine for
grabbing the users windows login name. What sort of trouble did you have?
--
HTH
Richard Choate, CPA

Hi all, thanks again for some great advice.

Dave I think if I can make the Log In Id solution work
then that will be the way to go, But thanks I now know how
to return Word info.

John, Thanks, I do relize that Excel is not real secure
but I think if I can at least make this sheet secure
against a simple change of the tools/options/general Name
I'll feel a little better. Also If i can get this to work
then users can fill out their expense cards on any PC they
log on to.

Richard..

Thanks for the great code but I am still having a little
trouble, hope you don't mind more questions?

Any way I set up the code to run in and on activate event
and it works fine to bring up MY windows log in id on MY
PC. But when somone eles signs onto my pc and opens the
spreadsheet it appears to not even run? Well at least it
does not return MY Login id but it won't return any log in
id though??

Any futher ideas? Also when I place the template on a
common user network and other users access it on there own
PC it works for them but still only if they are logged
into there own PC.

????

 

[Kelly]

Well, It seemed to work fine when the user opened the
template on their own PC logged in as themselves. So
that's fine but If another user logged in ...(i.e. if I
log in on another users PC) then it's like the code does
not even run? There are no errors but the code does not
return any initials at all.(we use initials as log in
ID) . I will include the code as I used it and how I set
it up to run. Maybe you can spot my problem....And thanks
again I really appreciate your help..




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



Private Sub Workbook_Activate()
If ThisWorkbook.Path = "" Then
' code to run when template is opened

Dim lpBuff As String * 25
Dim ret As Long, UserName As String

' Get the user name minus any trailing spaces found in the
name.
ret = GetUserName(lpBuff, 25)
UserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)

Sheets("Control").Unprotect Password:="test"

Worksheets("Control").Range("L1").Value = UserName

Worksheets("Control").Range("A19").Value = Worksheets
("Control").Range("A17").Value

Sheets("Control").Protect Password:="test"


End If

End Sub

 

[Kelly]

Richard, I thionk I found the answer to my own question.

When a user loggs in for the first time to a different PC
the Macro secut=rity setting automaticly default to "HIGH"
so the Auto Macros never ran, So now I'm going to try and
figure out how to make the security DEFAULT settings
change to medium or low.

Is ther