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

 

[Fluff]

What happens if you remove this line

On Error Resume Next

 

[Joe4]

Welcome to the Board!

Do you have any other event procedure code interfering with this?
Are are of the cells merged or protected?

Get rid of that error statement, as it would be suppressing any errors that may be happening (and possibly causing issues).
And let's disable event procedure code while we are making updates.

Try this:

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:="########"

'Exit code if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub

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

'This should not be necessary
'On Error Resume Next

'   Disable events while updates are happening
    Application.EnableEvents = False

    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
   
'   Re-enable events
    Application.EnableEvents = True

End If

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

End Sub

 

[IrishDave2137]

What happens if you remove this line

On Error Resume Next

I get a Run-Time error '1004': Application-defined or object-definition error

If I Debug is highlights 'Target.Offset(0, 3).Value = UserNameStr' as the issue

 

[IrishDave2137]

Welcome to the Board!

Do you have any other event procedure code interfering with this?
Are are of the cells merged or protected?

Get rid of that error statement, as it would be suppressing any errors that may be happening (and possibly causing issues).
And let's disable event procedure code while we are making updates.

Try this:

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:="########"

'Exit code if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub

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

'This should not be necessary
'On Error Resume Next

'   Disable events while updates are happening
    Application.EnableEvents = False

    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
  
'   Re-enable events
    Application.EnableEvents = True

End If

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

End Sub

Hi Joe, thanks so much. Those changes seem to have done the trick.
I'm guessing disabling events made the difference. Do you know why that's necessary?
I do have protection on the columns I'm writing to but I remove it at the start of the code and protect again at the end.
Thanks again

 

[Joe4]

Hi Joe, thanks so much. Those changes seem to have done the trick.
I'm guessing disabling events made the difference. Do you know why that's necessary?
I do have protection on the columns I'm writing to but I remove it at the start of the code and protect again at the end.
Thanks again

Do you have other event procedure code that may be interfering with this?

 

[IrishDave2137]

Do you have other event procedure code that may be interfering with this?

I enable iterations when the file is opened. No longer required though so I can remove it.

Private Sub Workbook_Open()

With Application

.Iteration = True
.MaxIterations = 100
.MaxChange = 0.0001

End With

End Sub

Is that what caused the problem?

 

[Joe4]

Is that what caused the problem?

Not sure. But it would be easy enough to test and find out.
Just comment out the lines I added to the code disabling events, and then remove the iterations code, and see if you still get the error (and if the code works properly).
I am curious to hear back what happens.

 

[IrishDave2137]

Not sure. But it would be easy enough to test and find out.
Just comment out the lines I added to the code disabling events, and then remove the iterations code, and see if you still get the error (and if the code works properly).
I am curious to hear back what happens.

Tried that
I commented out the iteration enabling code and then manually disabled iteration in Excel. I then removed the code disabling events but the problem came back.

Now that I'm no longer bypassing errors it keeps throwing up an error when it gets to the line 'Target.Offset(0, 3).Value = UserNameStr'.
As per my reply to Fluff, I get a Run-Time error '1004': Application-defined or object-definition error.

The curious thing is though, if I swap the order of the code and update column D first the same run time error pops up when it tries to populate column C. It can't seem to get past the first offset update.

I have no other code running in the file #ScratchingMyHead

 

[Joe4]

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.