Solved

How to combine this code?

Posted on 2014-04-13
8
667 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
Comment Utility
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 80

Assisted Solution

by:byundt
byundt earned 250 total points
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now