Input from user

[jasgot]

I've searched and searched to no avail

I have this sheet that many people use. I currently have an in cell drop down with a list of their names. I want them to select their own name so when the sheet is printed we know who filled it out.

The problem is that I have no way to force the issue.

I thought about a window that opens when the file is opened that asks for their name,(using a predefined list) but I cannot figure out how.

Then I thought, what id Don selects Nancy's name?

How can I fill a cell with a user's name in a fool proof manner?

 

[Ricky Morris]

Excel stores user names. Click Tools > Options > General and you'll see the username at the bottom of the form. Ask your users to Enter their names there. Then you'll be able to use this to find out who's using your spreadhseet. To assure that a user doesn't change his/her username to another user before opening the file you can give each user a specific (different password) and check the username against the password when the file is opened.

to access the user name is VBA do:

msgbox application.username

 

[jasgot]

This is nice, how can I drop it right into a cell when the sheet opens.

 

[NateO]

Howdy Jasgot,

You'd want either a workbook_open or auto_open procedure. I'll use auto_open here.

Put the following code in a normal module and change a1 to the appropriate range:

<pre>
Private Sub auto_open()
[a1] = Application.UserName
End Sub</pre>

Note though that this is the Excel username, quite easily changed. It probably is more difficult to change your network name, the following will drop the windows login name in to the cell like above:

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

Private Sub auto_open()
Dim sUser As String, lpBuff As String * 1024
GetUserName lpBuff, Len(lpBuff)
sUser = Application.Proper(Left$(lpBuff, _
(InStr(1, lpBuff, vbNullChar)) - 1))
[a1] = sUser
End Sub</pre>

The above goes in a normal module as well. Place the function at the very top of the module.

A few ways...Hope this helps.