Solved

How to combine this code?

Posted on 2014-04-13
8
691 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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