If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

frog5

New Member
Joined
Feb 27, 2016
Messages
11
Hi,

The following works great, with one exception, when the target.address is blank (nothing) the macro does not run (of course).

What I need, is for the macro to run in both instances, where the target.address is changed with existing data already entered, AND when the target address was originally blank, but new data was entered.

Here is the code...
Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that when any of the ranged of cells are changed
    ' triggers the following macros to run.
    
    Set KeyCells = Range("G15:G5006")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
                
        If ActiveCell.Offset(-1, -2) = "CashAcct" Then
            Call Cash
            Else


        If ActiveCell.Offset(-1, -2) = "BestBuyVisa" Then
            Call BestBuy
            Else
        
        If ActiveCell.Offset(-1, -2) = "SlateVisa" Then
            Call ChaseSlate
            Else
            
        If ActiveCell.Offset(-1, -2) = "BoAMC" Then
            Call BoA
            Else
... and so forth.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
I can't replicate the issue, but you should use be offsetting from Target rather than from the ActiveCell. Target is the changed cell. ActiveCell is usually a cell next to Target. (Whether below, to the right, above or to the left depending on how the user has set their preferences)

You also might prefer the SelectCase construction to nested If..Else..End If .


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that when any of the ranged of cells are changed
    ' triggers the following macros to run.
    
    Set KeyCells = Range("G15:G5006")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Select Case LCase(Target.Offset(-1, -2).Cells(1,1).Text))
            Case "cashacct" Then
                Call Cash
            Case "bestbuyvisa)
                Call BestBuy
            Case "slatevisa"
                Call ChaseSlate
            Case "boamc"
                Call BoA
            'etc
        End Select
    End If
End Sub
I don't know what those subordinate subs do, but if they write to cells, you should toggle Application.EnableEvents before and after calling them.
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("G15:G5006"), Range(Target.Address)) Is Nothing Then
    If Target.Cells.Count = 1 Then
      Select Case Target.Offset(-1, -2)
        Case "CashAcct"
          Call Cash
        Case "BestBuyVisa"
          Call BestBuy
        Case "SlateVisa"
          Call ChaseSlate
        Case "BoAMC"
          Call BoA
          ' and so forth
      End Select
    End If
  End If
End Sub
What's your question?
 

frog5

New Member
Joined
Feb 27, 2016
Messages
11
You guys are so awesome! Thank you so very much.

I don't know if I were to use the solution from MikeRickson, and if that would solve the new dilemma, but here it is:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("G15:G5006"), Range(Target.Address)) Is Nothing Then
    If Target.Cells.Count = 1 Then
      Select Case Target.Offset(0, -2)
        Case "CashAcct"
          Call Cash
        Case "BestBuyVisa"
          Call BestBuy
        Case "SlateVisa"
          Call ChaseSlate
        Case "BoAMC"
          Call BoA
        Case "AmazonVisa"
          Call ChaseAmazon
        Case "SS"
          Call SS
        Case "HH"
          Call HH
        Case "RS"
          Call RS
        Case "CE"
          Call CExpense
        Case "KE"
          Call KExpense
        Case "OC"
          Call OC
        Case "GF"
          Call GF
        Case "FH"
          Call FarmHouse
        Case "CapitalOneMC"
          Call CapOne
        Case "ZC"
          Call ZC
        Case "ENPP"
          Call ENPP
        Case "HHPP"
          Call HHPP
        Case "OCPP"
          Call OCPP
        Case "RSPP"
          Call RSPP
        Case "SSPP"
          Call SSPP
        Case "ZCPP"
          Call ZCPP
        Case "GFPP"
          Call GFPP
      
      End Select
    End If
  End If
End Sub

When the field is blank, the called macro (which only moves the cursor to the correct column--there are many, many columns) falls short by 2 columns.

However, when their is a previous value in the field, the called macro moves the cursor to where it should (it doesn't fall short of 2 columns).

It is the same macro being ran in either case. I suspect this has to do with the way I have it constructed. Would using the MikeRickerson's code alleviate this dealio?

Either way, I am truly thankful, though, I am still unsure what to do to fix this...
 

frog5

New Member
Joined
Feb 27, 2016
Messages
11

ADVERTISEMENT

Sorry, I'm kinda slow today. I guess I could try Mike's code and see if it fixes it. Will do that now.
 

frog5

New Member
Joined
Feb 27, 2016
Messages
11
Mike,

The subordinate subs just move the cursor to a specified row/column, nothing more. But that is good to remember (what you said).

I tried your code, and here's what it looks like and then what it did...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range


' The variable KeyCells contains the cells that when any of the ranged of cells are changed
' triggers the following macros to run.

Set KeyCells = Range("G15:G5006")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

Select Case LCase(Target.Offset(0, -2).Cells(1, 1).text)

' If Not Intersect(Range("G15:G5006"), Range(Target.Address)) Is Nothing Then
' If Target.Cells.Count = 1 Then
' Select Case Target.Offset(0, -2)

Case "CashAcct"
Call Cash
Case "BestBuyVisa"
Call BestBuy
Case "SlateVisa"
Call ChaseSlate
Case "BoAMC"
Call BoA
Case "AmazonVisa"
Call ChaseAmazon
Case "SS"
Call SS
Case "HH"
Call HH
Case "RS"
Call RS
Case "CE"
Call CExpense
Case "KE"
Call KExpense
Case "OC"
Call OC
Case "GF"
Call GF
Case "FH"
Call FarmHouse
Case "CapitalOneMC"
Call CapOne
Case "ZC"
Call ZC
Case "ENPP"
Call ENPP
Case "HHPP"
Call HHPP
Case "OCPP"
Call OCPP
Case "RSPP"
Call RSPP
Case "SSPP"
Call SSPP
Case "ZCPP"
Call ZCPP
Case "GFPP"
Call GFPP

End Select
End If
End Sub
 

frog5

New Member
Joined
Feb 27, 2016
Messages
11

ADVERTISEMENT

Sorry, the indent switch was omitted, trying again...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim KeyCells As Range


    ' The variable KeyCells contains the cells that when any of the ranged of cells are changed
    ' triggers the following macros to run.
    
    Set KeyCells = Range("G15:G5006")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        
        Select Case LCase(Target.Offset(0, -2).Cells(1, 1).text)
      
'  If Not Intersect(Range("G15:G5006"), Range(Target.Address)) Is Nothing Then
 '   If Target.Cells.Count = 1 Then
  '    Select Case Target.Offset(0, -2)
            
        Case "CashAcct"
          Call Cash
        Case "BestBuyVisa"
          Call BestBuy
        Case "SlateVisa"
          Call ChaseSlate
        Case "BoAMC"
          Call BoA
        Case "AmazonVisa"
          Call ChaseAmazon
        Case "SS"
          Call SS
        Case "HH"
          Call HH
        Case "RS"
          Call RS
        Case "CE"
          Call CExpense
        Case "KE"
          Call KExpense
        Case "OC"
          Call OC
        Case "GF"
          Call GF
        Case "FH"
          Call FarmHouse
        Case "CapitalOneMC"
          Call CapOne
        Case "ZC"
          Call ZC
        Case "ENPP"
          Call ENPP
        Case "HHPP"
          Call HHPP
        Case "OCPP"
          Call OCPP
        Case "RSPP"
          Call RSPP
        Case "SSPP"
          Call SSPP
        Case "ZCPP"
          Call ZCPP
        Case "GFPP"
          Call GFPP
      
        End Select
    End If
End Sub

Now, as to what it did.

When the field was blank it returned the cursor down one row and to the left 2 columns (as if there was no code).

When the field had a previous entry in it, it returned the cursor down one row (as if there was no code).

I will attach a pic of the spreadsheet layout to give you a better idea (if I can attach a pic).
 

frog5

New Member
Joined
Feb 27, 2016
Messages
11
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
You need to put the case tests in lower case, and you might add a Case vbNullString to handle the blank cell situation and a Case Else to handle wrong entry by the user.

Code:
Select Case [COLOR="#FF0000"]LCase[/COLOR](Target.Offset(0, -2).Cells(1, 1).text)

    Case [COLOR="#FF0000"]"cashacct"[/COLOR]
        Call Cash
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,839
Members
413,943
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top