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

 

[p45cal]

In the below, I use this to force the persons USERNAME to populate 3
rows to

the right in a column I hide. This shows me who was in a spreadshee and
updating ONE specific column... Column AD. My question is this... Ca I
poulate a named range with their USERNAME. I want to insert a name range,
then force the USERNAME there. (INSERT>NAME>DEFINE>)

VBA Code:
--------------------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 --------------------

Let's say you already have a defined name 'blah' defined in th
workbook.
It can be referred to by using:


VBA Code:
--------------------


range("blah")
--------------------



so


VBA Code:

 

[p45cal]

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'
included in the range E7:AD90 (though you could, but reintroduce th
Application.EnableEvents lines and the error handler)).

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


VBA Code:
--------------------


Private Sub Worksheet_Change(ByVal Target As Range)
Set x = Intersect(Range("E7:AD90"), Target)
If Not x Is Nothing Then
For Each ar In x.Areas 'to handle non-contiguous ranges
For Each rw In ar.Rows
Cells(rw.Row, "AF").Value = Environ("USERNAME")
Next rw
Next ar
End If
End Sub

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



It doesn't start trying to discern if someone deleted a value or no
and erase the username if that's the case, because they may have added
values in some columns and deleted values in others.. all showing th
name in the same column. So I've just got it to add the name for *any
change: value added/changed or deleted.

 

[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: