Generating username

[essjee]

Hi all,

I'm very new to Excel VBA, and this is my first post - please be gentle :D

What I'm trying to do is probably quite simple to you lot, but I can't seem to find the right combination of VBA and formula to get what I need done! I've been searching for hours.

ex. I enter data in A1, and I want my username to be inserted into C1. I'd like to send the file on to a colleague - User2 - have him enter data in A2, and have his username inserted into C2. He will send to User3, who will enter data in A3, whose name will populate in C3, and so on.

Using the code and formula below, my name populates in C1 perfectly. Problem: User2 opens the file, and C1 now shows his username. Filling in A2 thus inserts his username a second time into C2. You get the idea; obviously, I'd like all our usernames to stay put.

So this is what I'm using:

As a Module:

Public Function UserName()
    UserName = Environ$("username")
End Function

In the formulas:

[C1] =IF(A1>0,username(),"")
[C2] =IF(A2>0,username(),"")
[C3] =IF(A3>0,username(),"")
etc...

Wondering if there's a way to have the formula convert the formula into its value automatically, without having to press F9+enter? Of course, there's probably a better way, so I'll just wait for it...

 

[Rick Rothstein]

I think this event code will do what you ultimately want (neither the UserName function nor any formulas in Column C will be needed as the code will handle filling the cells in)...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 And Target.Column = 1 Then
    If Target.Value > 0 Then
      Target.Offset(, 2).Value = Environ$("username")
    Else
      Target.Offset(, 2).Value = ""
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[essjee]

Thanks so much for the quick reply, Rick!

Doesn't quite do what I want, but that's due to the inaccuracy of my original explanation. In actuality, we're not entering this data in an entire column, and it's not in column A. I tried to simplify my explanation by using A1:C3, but I see that didn't help my case! ("Welcome to the world of coding," he said to himself.)

1. To be specific, I have cells in H29,34,40,46, and 52 which require data entry by different people. On filling these cells, I'd like their username to be plugged in offset by two columns. Based on what you gave me, this code took good care of H29 cell entry:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 8 And Target.Row = 29 Then
    If Target.Value > 0 Then
      Target.Offset(, 2).Value = Environ$("username")
    Else
      Target.Offset(, 2).Value = ""
    End If
  End If
End Sub

...but I can't figure out how to include those other rows as part of a range?

2. Also, when I delete the entry from H29, I get "Run-time error '13': Type mismatch." And my username remains instead of vanishing.

3. Finally, I also happen to have another Worksheet_Change event already in place for this worksheet. Having two doesn't appear to be possible (i.e. "Ambiguous name detected: Worksheet_Change")... is there a way around this?

Thanks again!!!

 

[essjee]

Just bumping this up - suspect it got buried over the weekend. Thanks for any help!

 

[Rick Rothstein]

I tried to simplify my explanation by using A1:C3

That is almost always a mistake to do... doing so will ususally get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout.

Just bumping this up - suspect it got buried over the weekend. Thanks for any help!

See if this code works for you...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("H29,H34,H40,H46,H52"), Target) Is Nothing And Target.CountLarge = 1 Then
    If Target.Value > 0 Then
      Target.Offset(, 2).Value = Environ$("username")
    Else
      Target.Offset(, 2).Value = ""
    End If
  End If
End Sub[/td]
[/tr]
[/table]

 

[essjee]

That is almost always a mistake to do... doing so will ususally get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout.

Yup, I realized that immediately after your first rescue call

I see that didn't help my case! ("Welcome to the world of coding," he said to himself.)

That said, this code worked beautifully Rick. Thanks so much, and sorry for making you answer my question twice unnecessarily! Will know better next time