User name

M

Matt

Is it possible to populate a cell automatically (with a formula) with the
name of the user that is in the windows user name?
Thanks?
 
R

Rick Rothstein \(MVP - VB\)

I'm not sure if there is a direct (spreadsheet) way or not, but a simple UDF
can do it. Go into the VBA editor (Alt+F11), click Insert/Module from its
menu bar, copy/paste the following into the Module's code window that came
up...

Function UserName()
UserName = Environ$("USERNAME")
End Function

Rick
 
R

Rick Rothstein \(MVP - VB\)

I probably should have mentioned how to use it (in case you are not familiar
with UDFs); after you have created the UDF, just enter this...

=UserName()

into your cell.

Rick
 
M

Matt

So...I performed the operation and it worked when I typed in the =Username()
and my login name came up...perfect. How will this work when I ask other
people to update templates with data. I have a template that asks for 2
pieces of data, column A and column K. A has a drop down from a list to
select a part number. From this, the particulars (name, cost, budget etc)
populates in Columns B:J. Then in column K is open for data entry...or what
we are asking for (current period activity). Then in column L, I want to
know who made the entry, hence the =UserName() . How do I get this function
to perform or update upon entry in cell A?
 
G

Gord Dibben

In that case use event code which will negate the need for Rick's UDF

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
Target.Offset(0, 11).Value = Environ("USERNAME")
End If
stoppit:
Application.EnableEvents = True
End Sub

This is sheet event code.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.

Alt + q to return to Excel

As written works only a value is selected fro A1 dropdown.


Gord Dibben MS Excel MVP
 
M

Matt

Thanks Gord- I did this for the worksheet, however nohthing popluated in the
target field.
I chaged the worksheet a bit, Column A is still data entry (drop down list),
the persons name is to be populated in column C aka target cell. this is how
I changed the formula...what did I do wrong? Also the first cell to evaluate
will be cell a3.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$3" And Target.Value <> "" Then
Target.Offset(0, 2).Value = Environ("USERNAME")
End If
stoppit:
Application.EnableEvents = True
End Sub
 
G

Gord Dibben

Your revised code works fine for me. BTW.......is not a "formula" but event
code.

Username gets placed in C3 when a choice is made from DV dropdown in A3

A couple of things I can think of.

1. Somehow events got disabled but not re-enabled.

Alt + F11 to open VBE. View>Immediate Window. Type in or copy into that Window

Application.EnableEvents = True and hit ENTER key

2. Code was copied to wrong worksheet

In addition..........You say "the first cell to evaluate will be A3"

Are there more dropdowns in column A to pick from?

In that case the hard-coded $A$3 must be changed to a range.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const myRange As String = "A3:A10"
'Const myrange As String = "A3,A6,A8,A11,A16"
On Error GoTo stoppit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then
Target.Offset(0, 2).Value = Environ("USERNAME")
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord
 
M

Matt

Okay - the below is working great. I have found where even if a person
enters data into the target column (any cell in col A) then deletes that
entry, it still reocrds their username. Is there anyway to enter code in
there that only adds the username ONLY when something has been selected?
Thanks for your help.
 
G

Gord Dibben

Deleting is a change so the event is triggered upon that deletion.

We just have to change and add a few things to trap the deletion event and get
it to blank the cells in Column C

This revision works in my testing.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const myRange As String = "A3:A10"
'Const myrange As String = "A3,A6,A8,A11,A16"
On Error GoTo stoppit
Application.EnableEvents = False
If Intersect(Target, Me.Range(myRange)).Value <> "" Then
Target.Offset(0, 2).Value = Environ("USERNAME")
Else
If Intersect(Target, Me.Range(myRange)).Value = "" Then
Target.Offset(0, 2).Value = ""
End If
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord
 
M

Matt

Gord - You are an Excel Rockstar! Thanks for sticking with me on this, it
works pefrectly!
 
Top