Changing multiple cells when target updated

[IrishDave2137]

Hi,

Hoping this isn't a stupid question but here goes

When I run the following code I want a change in column A (Target range) to trigger changes in columns C & D.
When I add a value to column A, the Date will be inserted into column C and Username to column D.
When I clear the value from column A, C & D should also clear.

However I can only get one column to populate using the code below.
If I run the code as provided only column C will change its value.
If I comment out references to 'Target.Offset(0.2)' then column D will populate with a value.
If I change the order in the code I can get column D to populate but not column C.
So in essence it will only change the first target offset and not subsequent offsets.

Does anyone know why this is happening and how I can get both columns C & D to populate when A is changes? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WS As Worksheet
Dim UserNameStr As String

Set WS = Sheets("Scan Sheet")
UserNameStr = Application.UserName

WS.Unprotect Password:="########"

If Not Intersect(Target, Range("A:A")) Is Nothing Then

    On Error Resume Next

    If Target.Value = "" Then
        Target.Offset(0, 2) = ""
        Target.Offset(0, 3) = ""
   Else
        Target.Offset(0, 2).Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
        Target.Offset(0, 3).Value = UserNameStr
    End If

End If

WS.Protect Password:="#######", AllowSorting:=True, AllowFiltering:=True

End Sub

 

[IrishDave2137]

Well, if disabling the events resolves the issue, I say go ahead and do that.
It is never a bad idea to do that in "Worksheet_Change" event procedure code anyway, when the code is making changes to the sheet.

Absolutely . Thanks again for the help Joe (& Fluff). Blown away by how quickly you guys responded and helped me out.

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Fluff]

Another option would be

    If Target.Value = "" Then
        Target.Offset(0, 2) = ""
        Target.Offset(0, 3) = ""
   Else
        Target.Offset(0, 2).Resize(, 2).Value = Array(Format(Now, "mm/dd/yyyy HH:mm:ss"), UserNameStr)
    End If

That way the code is not triggered multiple times, which is what's causing the problem.

 

[IrishDave2137]

Another option would be

    If Target.Value = "" Then
        Target.Offset(0, 2) = ""
        Target.Offset(0, 3) = ""
   Else
        Target.Offset(0, 2).Resize(, 2).Value = Array(Format(Now, "mm/dd/yyyy HH:mm:ss"), UserNameStr)
    End If

That way the code is not triggered multiple times, which is what's causing the problem.

Thanks Fluff, makes sense. That's a very useful option, thank you.

 

[Fluff]

You're welcome.

 

[KAELondon]

Hey,

I was using something very similar in order to enter timestamps into a table; the last column of the table works out the duration between each timestamp but, for some reason, this script stops the formula in the last column from copying down as new rows are added to the table. It's the most bizare thing. That column isn't even unlocked for editing and the ranges don't crossover into that column at all. Has anyone experienced this before? Any ideas how to fix?

The taret range is the "NAME" column and my script posts the timestamp into the "TIME ON" column next to the name entered and also the "TIME OFF" column in the row above with the "DUR." column being the duration. I've tested this out and the formula remains fine with no VBA on the sheet but stops working I enter this sub:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'The ranges that we are checking is where the names are
If Not Intersect(Target, Range("B10:B100")) Is Nothing Then

On Error Resume Next

'If the value is empty
If Target.Value = "" Then

'Make sure that the relevant cells are empty
Target.Offset(0, 3) = ""
Target.Offset(-1, 4) = ""

Else
'Otherwise put the timestamp in the "TIME ON" & "TIME OFF" cols
Target.Offset(0, 3).Value = Format(Now, "mm/dd/yy HH:mm")
Target.Offset(-1, 4).Value = Format(Now, "mm/dd/yy HH:mm")

End If

End If

End Sub

Cell "B10" is where the "NAME" column starts.

 

[Joe4]

Hey,

I was using something very similar in order to enter timestamps into a table; the last column of the table works out the duration between each timestamp but, for some reason, this script stops the formula in the last column from copying down as new rows are added to the table. It's the most bizare thing. That column isn't even unlocked for editing and the ranges don't crossover into that column at all. Has anyone experienced this before? Any ideas how to fix?

The taret range is the "NAME" column and my script posts the timestamp into the "TIME ON" column next to the name entered and also the "TIME OFF" column in the row above with the "DUR." column being the duration. I've tested this out and the formula remains fine with no VBA on the sheet but stops working I enter this sub:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'The ranges that we are checking is where the names are
If Not Intersect(Target, Range("B10:B100")) Is Nothing Then

On Error Resume Next

'If the value is empty
If Target.Value = "" Then

'Make sure that the relevant cells are empty
Target.Offset(0, 3) = ""
Target.Offset(-1, 4) = ""

Else
'Otherwise put the timestamp in the "TIME ON" & "TIME OFF" cols
Target.Offset(0, 3).Value = Format(Now, "mm/dd/yy HH:mm")
Target.Offset(-1, 4).Value = Format(Now, "mm/dd/yy HH:mm")

End If

End If

End Sub

Cell "B10" is where the "NAME" column starts.

Welcome to the Board!

Since your question is not about the original question, but your own new question, it would be much better to post it to a new thread instead of posting it at the end of an old, existing thread. That way, it will also appear as a new, unanswered question on the "Unanswered threads" list that many people use to look for new, unanswered questions.

 

[KAELondon]

Welcome to the Board!

Since your question is not about the original question, but your own new question, it would be much better to post it to a new thread instead of posting it at the end of an old, existing thread. That way, it will also appear as a new, unanswered question on the "Unanswered threads" list that many people use to look for new, unanswered questions.

Yeah, good shout... I'll do that now. Just thought since it's almost he exact same sub that people in this thread might have some idea.