Target.Offset(0, 36).Value Help

[Matt]

In the below, I use this to force the persons USERNAME to populate 36 rows to
the right in a column I hide. This shows me who was in a spreadsheet and
updating ONE specific column... Column AD. My question is this... Can I
poulate a named range with their USERNAME. I want to insert a named range,
then force the USERNAME there. (INSERT>NAME>DEFINE>)



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const myRange As String = "ad7:AD90"
'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, 36).Value = Environ("USERNAME")
Else
If Intersect(Target, Me.Range(myRange)).Value = "" Then
Target.Offset(0, 36).Value = ""
End If
End If
stoppit:
Application.EnableEvents = True
End Sub

 

[Matt]

Awesome! THanks

in a simple way:

VBA Code:

Private Sub Worksheet_Change1(ByVal Target As Range)
Set x = Intersect(Range("E7:AD90"), Target)
If Not x Is Nothing Then
Cells(Target.Row, "AF").Value = Environ("USERNAME")
End If
End Sub

--------------------



will put the username in column AF (I didn't use Z because that's
included in the range E7:AD90 (though you could, but reintroduce the
Application.EnableEvents lines and the error handler)).

This won't handle where someone may have selected a range covering
several rows/columns and deleted or ctrl-entered a value.
This, however does handle that, and handles if the user selects a
non-contiguous range too:

VBA Code: