Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Hi All,

I know enough in VBA--thanks to Expert's Exchange--to know that there cannot be 2+ events in the same sheet. ...but I need to... OR....

Can I combine this code?

I have not said it yet lately but hats off, and a bow, to all of you. In this forum you all are literally teaching the masses how to program and your continued patience is greatly appreciated with some of the kinds of questions we (I) ask here.

Thanks!

I know enough in VBA--thanks to Expert's Exchange--to know that there cannot be 2+ events in the same sheet. ...but I need to... OR....

Can I combine this code?

```
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
'only one cell updated
If Target.Column = 2 Then
'update is column B , our monitor target
Target.offset(0, -1).value = Target.offset(0, 1).value
Target.value = 0
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("Z:Z")) Is Nothing Then
Select Case Range("AB" & Target.row).value
Case Is = ""
Range("M" & Target.row) = Range("M" & Target.row).value + 1
Case Is = "1"
Range("N" & Target.row) = ""
End Select
End If
End Sub
```

I have not said it yet lately but hats off, and a bow, to all of you. In this forum you all are literally teaching the masses how to program and your continued patience is greatly appreciated with some of the kinds of questions we (I) ask here.

Thanks!

Sample to run both pieces:

```
Private Sub Worksheet_Change(ByVal Target As Range)
' Check Condition 1
If Target.Count = 1 Then
'only one cell updated
If Target.Column = 2 Then
'update is column B , our monitor target
Target.offset(0, -1).value = Target.offset(0, 1).value
Target.value = 0
End If
End If
' Check Condition 2
If Not Intersect(Target, Me.Range("Z:Z")) Is Nothing Then
Select Case Range("AB" & Target.row).value
Case Is = ""
Range("M" & Target.row) = Range("M" & Target.row).value + 1
Case Is = "1"
Range("N" & Target.row) = ""
End Select
End If
End Sub
```

Sample to run if only first is used (notice the Else If line that only is checked if the first If is false)

```
Private Sub Worksheet_Change(ByVal Target As Range)
' Check Condition 1
If Target.Count = 1 Then
'only one cell updated
If Target.Column = 2 Then
'update is column B , our monitor target
Target.offset(0, -1).value = Target.offset(0, 1).value
Target.value = 0
End If
ElseIf Not Intersect(Target, Me.Range("Z:Z")) Is Nothing Then
' Check Condition 2
Select Case Range("AB" & Target.row).value
Case Is = ""
Range("M" & Target.row) = Range("M" & Target.row).value + 1
Case Is = "1"
Range("N" & Target.row) = ""
End Select
End If
End Sub
```

1. Using ltlbearand3's second version of the code, the first test will prevent the second test from running if only a single cell is changed, even when it is in column Z

2. The code will trigger recursively because events haven't been turned off before you change a cell value

3. Your Select Case is looking for a cell with a text value of 1. This is not the same as the number 1.

=SUM(A1) equals 0 if A1 contains a text value of 1

=SUM(A1) equals 1 if A1 contains the number 1

I resolved these issues in the snippet below:

```
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
' Check Condition 1
If (Target.Count = 1) And (Target.Column = 2) Then
'only one cell updated in column B
'update is column B , our monitor target
Target.Offset(0, -1).Value = Target.Offset(0, 1).Value
Target.Value = 0
ElseIf Not Intersect(Target, Range("Z:Z")) Is Nothing Then
' Check Condition 2
Select Case Range("AB" & Target.Row).Value
Case Is = ""
Range("M" & Target.Row) = Range("M" & Target.Row).Value + 1
Case Is = 1 'You probably are entering the number 1 in the cell, not text
Range("N" & Target.Row) = ""
End Select
End If
Application.EnableEvents = True
End Sub
```

In other words, if the first set of code should run, then the second set of code should also run also.

This is what I have come up with before Byundt's comments.

Private Sub Worksheet_Change(ByVal Target As Range)

' Check Condition 1

If Target.Count = 1 Then

'only one cell updated

If Target.Column = 2 Then

'update is column B , our monitor target

Target.offset(0, -1).value = Target.offset(0, 1).value

Target.value = 0

End If

ElseIf Not Intersect(Target, Me.Range("B:B")) Is Nothing Then

' Check Condition 2

Select Case Range("AA" & Target.row).value

Case Is = ""

Range("AB" & Target.row) = Range("AB" & Target.row).value + 1

Case Is = "1"

Range("AB" & Target.row) = ""

End Select

End If

End Sub

This is what I have after Byundt's comments:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

' Check Condition 1

If (Target.Count = 1) And (Target.Column = 2) Then

'only one cell updated in column B

'update is column B , our monitor target

Target.Offset(0, -1).Value = Target.Offset(0, 1).Value

Target.Value = 0

ElseIf Not Intersect(Target, Range("B:B")) Is Nothing Then

' Check Condition 2

Select Case Range("AA" & Target.Row).Value

Case Is = ""

Range("AB" & Target.Row) = Range("AB" & Target.Row).Value + 1

Case Is = 1 'You probably are entering the number 1 in the cell, not text

Range("AB" & Target.Row) = ""

End Select

End If

Application.EnableEvents = True

End Sub

The first part of the code works fine, the second part is still not quite there.

Thanks everyone!

My problem is, other than being a "new guy" on this, or more currently a "not so new guy" that just isn't catching on as fast as the others I am having difficulty asking the correct questions.

Luckily I people like ltlbearand3 and byundt that can read between the lines and see what I am really after. By playing around with your code I realized that I didn't need the second statement at all and now it works:

'ElseIf Not Intersect(Target, Range("Z:Z")) Is Nothing Then

thanks for sticking in there with me.

Here is the final version that works:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If (Target.Count = 1) And (Target.Column = 2) Then ' Check Condition 1: only one cell is updated in column B

Target.offset(0, -1).value = Target.offset(0, 1).value

Target.value = 0

'ElseIf Not Intersect(Target, Range("b:b")) Is Nothing Then ' Check Condition 2: one or more cells are updated in column Z, and column AA is either blank or 1

Select Case Range("AA" & Target.row).value

Case Is = ""

Range("AB" & Target.row) = Range("AB" & Target.row).value + 1

Case Is = 1 'You probably are entering the number 1 in the cell, not text

Range("AB" & Target.row) = "" 'Set column AB equal to an empty string (looks like a blank)

'Range("AB" & Target.Row).ClearContents 'Clear contents of column AB

End Select

End If

Application.EnableEvents = True

End Sub

The problem is my inability to ask the question correctly the first time so I must endeavor to not waste everybody's time. I have to get better at asking the question.

Now that I see what I REALLY WANT I can restate the original problem/question.

If the first situation is true/false then the second problem is also true/false.

So it is either true/true or false/false.

In other words, the second part isn't really a test at all. It is just something that needs to happen after the first part is true.

HOWEVER!!!! IT IS NIFTY CODE YOU GAVE ME AND WE WILL BE SURE USING IT LATER IN A DIFFERENT PLACE SO ALL YOUR EFFORTS ARE NOT LOST. YOU'RE HELPING ME 'FALL INTO GREATER THINGS!'

THANKS!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

I think you want this instead. Note that I show two alternatives for putting a blank in column AB. In one, you set the value of the cell to an empty string In the other, you clear the contents of the cell. I can't say which alternative is better for you, but you should understand the difference and choose wisely.

Open in new window