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

 

[Peter_SSs]

c.Offset(, 2).Value = Environ("Username") & Format(Date, ": d-mmm-yy")

 

[cidfidou]

Hi Peter,

If I incorporate the above modification the change will be applied to the 2 ranges (column A and and F)

My question was not clear... I have 2 columns with validation List (column A and F). How should I modify the code to have the date/ username added to a different offset depending of the column when the validation is used (ie when modify column A offset 1 and modify column F offset 2)

Thanks again...

 

[Peter_SSs]

OK, so now we are getting where the code is more specific to the actual circumstance described and no so "general" but for the ranges and offsets being referred to here, try this.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim oSet As Long
  
  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
      oSet = IIf(Intersect(c, Range(myRange).Areas(1)) Is Nothing, 2, 1)
      c.Offset(, oSet).Value = Environ("Username") & Format(Date, ": d-mmm-yy")
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[Peter_SSs]

This is a bit longer, but might be simpler to follow/modify if required.

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

 

[cidfidou]

Hi Peter,

This is working perfectly. You are my new god

Thanks