VBA - Worksheet Change

[jack109]

Hi

I want to adjust the following code so that it only happens when B4 is changed and not all cells in the worksheet.

VBNA

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Worksheets("BREAKDOWN").Range("B4").Copy Worksheets("DATA").Range("B2")
Application.ScreenUpdating = True
End Sub

Many thanks

 

[Joe4]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) is Nothing Then
    Application.ScreenUpdating = False
    Worksheets("BREAKDOWN").Range("B4").Copy Worksheets("DATA").Range("B2")
    Application.ScreenUpdating = True
End If
End Sub

 

[saboh12617]

Hello,
You can add a condition like so

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False, xlA1)<> "B4" Then Exit Sub
Application.ScreenUpdating = False
Worksheets("BREAKDOWN").Range("B4").Copy Worksheets("DATA").Range("B2")
Application.ScreenUpdating = True
End Sub

 

[Joe4]

Hello,
You can add a condition like so

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False, xlA1)<> "B4" Then Exit Sub
Application.ScreenUpdating = False
Worksheets("BREAKDOWN").Range("B4").Copy Worksheets("DATA").Range("B2")
Application.ScreenUpdating = True
End Sub

One major difference between your code and mine - your code will only work if cell B4 is the ONLY cell updated.
Mine will work if cell B4 is ANY of the cells updated.

For example, if you copy cell B1 to B2:B10 (so 9 cells are updated at once, cell B4 being one of them), my code will fire, and yours will not.

 

[saboh12617]

Yes indeed i wrote it with this exact idea in mind, as author wrote "it only happens when B4 is changed". But i'm not native speaker, i might have misunderstood.
Anyway, better like this, with your precision OP knows what to choose.

 

[Joe4]

Yeah, the question is how/when "B4 is changed".
If only one cell is ever manually updated at a time, your code would work fine.
However, if they are using copy/paste, where they could be updating many cells at once (including "B4"), your code would not run.
Mine takes into account that possibility and will work in both instances.

That is the thing to remember about "Worksheet_Change" event procedures - when they are fired, it does not always mean just one cell was updated; a whole range of cells may have been updated at once.

 

[saboh12617]

I agree with you 100%, and i understand the limitations that the solution i wrote brings. Indeed i wrote it with this very specific case (copy paste of a bigger range) in mind.

In my experience, there are also many cases in which you'd prefer not to fire the VBA event when you copy/paste a bunch of data. I think it's good we proposed the two options and that you took the time to describe very clearly the difference between them, so that OP can choose wisely.

 

[Joe4]

In my experience, there are also many cases in which you'd prefer not to fire the VBA event when you copy/paste a bunch of data. I think it's good we proposed the two options and that you took the time to describe very clearly the difference between them, so that OP can choose wisely.

Yes, a common way way to do that (to not fire the code if a multiple cells are changed at once), is to add this line to the very top of your code:

'Exit code (don't run) if multiple cells are updated at once
If Target.CountLarge > 1 Then Exit Sub

 

[jack109]

Thanks both, I was indeed looking for the code to ONLY run if/when B4 was updated.

 

[Joe4]

Thanks both, I was indeed looking for the code to ONLY run if/when B4 was updated.

Then either solution would work for that (as long as B4 would never be updated with other cells at the same time).