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 ExplicitPrivate 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 IfEnd SubPrivate 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 IfEnd 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.

Sure combining these two pieces of code in the same event would be fine. Your If statements will drive what happens in the code. What you need to decide is can it happen that both If statements could be true at the same time. If so, do you want both pieces of code to be run or only one. If only one, which one?

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 IfEnd 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 IfEnd Sub

There are a couple of potential issues with your code after it is combined as ltlbearand3 showed.
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 IfApplication.EnableEvents = TrueEnd Sub

After reading your comments this is what I came up; I had to change my sheet around and realized that the Column B could and should (I hope) trigger the two events and therefore be combined and thus able to run this code all at once.

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!

0

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

In the original question, you wanted to trap user changes to column B or Z. In your most recent Comment, however, you are trapping changes in either column B or ***column B***.

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.

Private Sub Worksheet_Change(ByVal Target As Range)Application.EnableEvents = FalseIf (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 = 0ElseIf Not Intersect(Target, Range("Z:Z")) 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 SelectEnd IfApplication.EnableEvents = TrueEnd Sub

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

Might more than one cell be changed at a time? If so, the code could be rewritten to check column AA of each changed row. As currently written, the code is only checking the first such row

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!

0

Featured Post

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data.
Rather than update each graph to point to a different set within a static set of data, t…

This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.
This l…