Username entered in a cell when validation list is used

[cidfidou]

Hi guys,

Is there a way to have a formula or a VBA code associated to the choice of any value in a Validation list.

For example is there a way to have lets say in A1 a validation list and to have B2 returning the username of the person of who will select a value in A1?

As ususal thanks in adavnce

 

[jamot1]

Hello cidfidou,

Just a quick clarification on your question...are you asking whether B2 can automatically be updated with the username of the person who updated A1 or are you asking if B2 can automatically be updated based on the value selected in A1?

Thanks,
Thomas

 

[Peter_SSs]

If the DV is in A1, do you mean to put the user name in B1, not B2? Anyway, a minor change if the suggestion below is on the right track.
Try this in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test by choosing a value from the A1 Data validation list

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = Environ("Username")
    Application.EnableEvents = True
  End If
End Sub

 

[cidfidou]

Hi Peter,

Thank you so much for your input.
How Can I update your code to make it work to let say to 100 lines (A1 to A100) and to have the username in B1 to B100.
Also, would it be possible to add the date to the username?
Thanks in advance

 

[Peter_SSs]

Try this. It should also cope with the case where the user changes multiple cells in the range at once.
You can set the range yourself in the code and you may also want to experiment with how the date is presented etc.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Const myRange As String = "A1:A100" '<- Change to suit
  
  Set Changed = Intersect(Target, Range(myRange))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      c.Offset(, 1).Value = Environ("Username") & Format(Date, ": d-mmm-yy")
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[cidfidou]

Hi Peter,

Thank you so much for your answer as it is working like a charm.

I am always amazed that the users of this great web site are willing to help strangers (if you were living closer to Dublin I would defo offer u a pint,, haha).

If it is not too much to ask, could you please lead to the right direction for the following :

- I have tried in vain to change your code to be bale to do the following : At the moment when you change any cell in A, B will be updated... How can I have for example B with the date / username when A is changed AND G updated with date and Username when F is changed...

Thanks in advance

 

[Peter_SSs]

(if you were living closer to Dublin I would defo offer u a pint,, haha).

I'll look you up.

If it is not too much to ask, could you please lead to the right direction for the following :

- I have tried in vain to change your code to be bale to do the following : At the moment when you change any cell in A, B will be updated... How can I have for example B with the date / username when A is changed AND G updated with date and Username when F is changed...

Assuming you are still talking about rows 1:100

Const myRange As String = "A1:A100, F1:F100" '<- Change to suit

 

[cidfidou]

Hi Peter,

Wouahh!!! it is exactly what I was looking for... Thank you so much!!!! ( As I am my new to VBA I was trying to duplicate your code instead of changing the range....)

I really wish I could you help in return... Have a good day

 

[Peter_SSs]

I really wish I could you help in return... Have a good day

You already have. I do this for enjoyment and having an appreciative "customer" certainly adds to that enjoyment.

 

[cidfidou]

Hi Peter,

It is me again. Hopefully you are not sick of me yet

Last question I promise. How can I change the offset for the second range if I want the username/range offset by 2 columns and not 1.

Thanks AGAIN


Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range, c As Range Const myRange As String = "A1:A100,F1:F100 " '<- Change to suit Set Changed = Intersect(Target, Range(myRange)) If Not Changed Is Nothing Then Application.EnableEvents = False For Each c In Changed c.Offset(, 1).Value = Environ("Username") & Format(Date, ": d-mmm-yy") Next c Application.EnableEvents = True End If End Sub</pre>