Solved

How to combine this code?

Posted on 2014-04-13
8
707 Views
Last Modified: 2014-04-14
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?

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

Open in new window


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!
0
Comment
Question by:BostonBob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
8 Comments
 
LVL 20

Assisted Solution

by:ltlbearand3
ltlbearand3 earned 250 total points
ID: 39998183
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 If
    
End Sub

Open in new window


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

Open in new window

0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 39998207
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 If
Application.EnableEvents = True
End Sub

Open in new window

0
 

Author Comment

by:BostonBob
ID: 39998237
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 39998260
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 = 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("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 Select
End If

Application.EnableEvents = True
End Sub

Open in new window

0
 

Author Comment

by:BostonBob
ID: 39998297
This is what I need:

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
0
 
LVL 81

Expert Comment

by:byundt
ID: 39999319
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
0
 

Author Comment

by:BostonBob
ID: 39999370
Mr. Buyndt,

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question