On cell change issue

[jtodd]

HI I have the following code

Private Sub worksheet_change(ByVal Target As Range)
    ActiveSheet.Unprotect "MS"
    If Not Intersect(Range("A:A,H:H"), Target) Is Nothing Then
        Application.EnableEvents = False
        Cells(Target.Row, Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1).Value = _
        Environ("username") & "-" & Date
    End If
    ActiveSheet.Select
     With ActiveSheet
     .Protect Password:="MS", AllowFiltering:=True, AllowSorting:=True
    End With
    Application.EnableEvents = True
End Sub

This works fine but if the user uses copy and paste for more than one cell at a time it only puts the user name in the first cell (even though the cells have changed)
Is there a way of putting the user name in all cells that have been changed ?
Hope this makes sense

 

[jtodd]

Is this what you ment
1 $H$27 $H$27
B TODDJ03-05/09/2022
1 $H$24 $H$24
B TODDJ03-05/09/2022

 

[Anthony47]

So you tried first modifying cell H27, then H24. In both cases Rng.Offset(0, 1000).End(xlToLeft).Offset(0, 1).Address failed to identify the last used column in the row
At the moment I have no idea which conditions could make it fail

Can you confirm that when a change is made in columns A or H then the Username should be written on the first available (free) column of the modified row/rows?

 

[Anthony47]

Whaiting for any good idea about the error (see previous message), let's use a piece of your initial code, and change the whole For Each Rng /Next Rng code as follows:

    For Each Rng In Target
        If Not Intersect(Range("A:A,H:H"), Rng) Is Nothing Then
        On Error Resume Next
            JJ = JJ + 1
            Debug.Print JJ, Target.Address, Rng.Address
            Debug.Print "A1", Rng.Offset(0, 1000).End(xlToLeft).Offset(0, 1).Address
            Debug.Print "A2", Cells(Rng.Row, Cells(Rng.Row, Columns.Count).End(xlToLeft).Column + 1).Address       '***
            Debug.Print "B", Environ("username") & "-" & Date
        On Error GoTo 0
            Cells(Rng.Row, Cells(Rng.Row, Columns.Count).End(xlToLeft).Column + 1).Value = _
              Environ("username") & "-" & Date
        End If
    Next Rng

In this way we use your initial approach for calculating the destination cell, let's see if that way it works

If it works, the 4 lines with Debug.Print can be removed

I think we should also clarify what to do if the cell is cleared rather then compiled: do we still need to save the UserName?

 

[ouvay]

Hi Thanks for promt reply
It sort of works , if i copy say H3:H5 and paste into H6:H9 it puts the user name in I6 ,I7,I8 .
What i want is for the user name to go into I6,i7,I8 ie next to the cell that has been changed

sorry, I went MIA for a bit, if I am to understand you just want the change to occur in one column to the right? if possible, could you give us some data samples with some clarification