Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Integration of two change event macro in a single workbook

0

I already have a workbook that has a change event macro that triggers action when column BJ:BL changes.But I also want to add another change event to same workbook to column M that performs different actions.I tried to work around but no luck.

Attached file is the existing workbook with change event. Below code is the additional event need to be integrated with existing code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("M5:M10000")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
Cells(Target.Row, 61) = Application.UserName & " - " & Date
End Sub
Answer
Discuss

Answers

0
Selected Answer

You have 3 variables, and you named them x, r and c, where c is a range and r is a value. At least x is a number. You have 3 Exit Sub in 5 lines of code. You have "saved" so much time on creating an infrastructure for your code that you failed to do the job in the end. It's like buying an airplane before thinking of an airport. You won't save time. You'll crash your new plane.

You want to record the value of the last selected cell. This thought is expressed in the code below.

Option Explicit

Private PrevVal As Variant            ' scoped for this code module

Private Sub Worksheet_Activate()
    ' ensure that PrevVal is always set
    PrevVal = ActiveCell.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' reset PrevVal when the selection changes
    PrevVal = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Reset the previous value after changing it

    PrevVal = Target.Value
End Sub

The next thought is that you don't want the code to respond if the PrevVal = "". You also don't want it to run if the user changed more than one cell. This results in one line of code.

If (Target.CountLarge > 1) Or (PrevVal = "") Then Exit Sub

You can have one - only one(!) - Exit Sub at the very beginning of the code. After that you shouldn't disturb the flow of the code anymore. Avoid jumps in the code backward or forward in the middle of a procedure.

The next piece of logic looks like this.

    If Not Intersect(Target, Range("BJ5:BL10000")) Is Nothing Then
        MsgBox "Do one thing in range BJ:BL"
    ElseIf Not Intersect(Target, Range("M5:M10000")) Is Nothing Then
        MsgBox "Do another thing in Column M"
    End If

I won't put this together for you for fear that you will dump more encoded gibberish on me in the future. But I advise you not even to think of what you want to do in columns BJ or M at this point. You need the basic structure of your code to be solid. Therefore you should test it thoroughly.

If you really want to think ahead, think of how to structure the code going forward. Your impulse will tell you to just replace the two MsgBox with wahtever code you can design. Self-preservation should dictate another way. Why should you take a tested and tried piece of code and destroy it by writing ad hoc something into it which you know will stop it from functioning? This reasoning will lead you to a solution as sketched below.

    If Not Intersect(Target, Range("BJ5:BL10000")) Is Nothing Then
        Call Procedure_A
    ElseIf Not Intersect(Target, Range("M5:M10000")) Is Nothing Then
        Call Procedure_B
    End If

For Christ's sake, please give descriptive names to the procedures. You will be rewarded in the afterlife, rest assured. The immediate advantage is that these are small procedures which you can test independently or even post here to ask questions about them. You will save a lot of time - but your preference seems to be to eat the egg rather than wait for the chicken to hatch lol:

Discuss

Discussion

Thank you Variatus for your insights and feedback really helped me a lot.
Dr Liss (rep: 26) May 27, '20 at 1:11 am
Add to Discussion
0

Good effort! You are really close but you need to reverse your IF statement check.

You had it like this:

If Intersect(Target, Range("BJ5:BL10000")) Is Nothing Then Exit Sub

but you need it like this:

If Not Intersect(Target, Range("BJ5:BL10000")) Is Nothing Then
    ' Do stuff
End If

Use this as a framework for creating code chunks that run when things happen to certain ranges.

Your new code section would probably be this:

    If Not Intersect(Target, Range("BJ5:BL10000")) Is Nothing Then
        If Target.Value = "" Then
            If r = "" Then Exit Sub
            x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(r, Sheets("Key").Range("B1:B20"), 0))
            If x >= 1 Then Cells(Target.Row, x + 47).ClearContents
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Offset(-1, 0).Select
            Exit Sub
        End If
        x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(Target.Value, Sheets("Key").Range("B1:B20"), 0))
        If x >= 1 Then Cells(Target.Row, x + 47) = Date
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Offset(-1, 0).Select
    End If

The big change is to put Not in the IF statement so it reverses the check and then surround the code to run with the IF statement instead of putting an Exit Sub there.

Side note, you should not just throw out Exit Sub statements all over the place, it makes it harder for you to control the code and maintain it - just encapsulate the code you don't want to run within the IF statement and reverse whatever is the current check with a Not or some other logical condition change.

Update

Here is your second chunk of code updated based on what I said in my answer.

If NOT Intersect(Target, Range("M5:M10000")) Is Nothing Then
If Target.Value = "" Then Exit Sub
Cells(Target.Row, 61) = Application.UserName & " - " & Date
End If

Put both of these chunks of code in the change event. Do this and if it still doesn't work then edit your question with an updated workbook.

Also, stop using Exit Sub's in the middle of the macros, it makes life tougher than needed ;P

Discuss

Discussion

Sorry don, I think you got my question wrong.The below 2 sections of code need to be integrated in a single workbook.
Dr Liss (rep: 26) May 26, '20 at 11:31 am
Public r As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim x As Long, c As Range
    If Intersect(Target, Range("BJ5:BL10000")) Is Nothing Then Exit Sub
    If Target.Value = "" Then
        If r = "" Then Exit Sub
        x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(r, Sheets("Key").Range("B1:B20"), 0))
        If x >= 1 Then Cells(Target.Row, x + 47).ClearContents
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Offset(-1, 0).Select
        Exit Sub
    End If
    x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(Target.Value, Sheets("Key").Range("B1:B20"), 0))
    If x >= 1 Then Cells(Target.Row, x + 47) = Date
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(-1, 0).Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    r = Target
End Sub
   
 Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("M5:M10000")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
Cells(Target.Row, 61) = Application.UserName & " - " & Date
End Sub
Dr Liss (rep: 26) May 26, '20 at 11:31 am
I understood you and that's why I said that you need to reverse the IF statements so that they encapsulate each chunk of code that you want to run based on which range had a value changed. I showed you how to change the IF statement for the first part and so you just make that same change for the second part.... I will add another example to my answer. 
don (rep: 1989) May 26, '20 at 1:45 pm
Got it.Thanks!
Dr Liss (rep: 26) May 27, '20 at 1:12 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login