Changing multiple cells when target updated

IrishDave2137

New Member
Joined
Jun 24, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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 :)

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

New Member
Joined
Jun 24, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,127
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help!
:)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
Another option would be
VBA Code:
    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

New Member
Joined
Jun 24, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Another option would be
VBA Code:
    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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Forum statistics

Threads
1,148,157
Messages
5,745,107
Members
423,924
Latest member
Gazzat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top