Worksheet change events with multiple targets

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
411
Office Version
  1. 2019
Platform
  1. Windows
So i'm trying to code it where if I enter data into certain cells and hit enter it will take me to a another specific cell. The code that I have will work when I hit enter at the specific cells. The problem is it jumps me to other cells when not on target. For instance If I enter data in D34 and hit enter it takes me to C38. Just like I want it to. But every time I enter data and hit enter in cells C38 and C39 it takes me back to C38. It's not until i get back down to C40 which is another target that it takes me to the cell I told it to.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Count > 1 Then
    
        If Target = [B21] Then
            Application.Goto [B24]

        

        If Target = [B29] Then
            Application.Goto [E26]


        If Target = [E26] Then
            Application.Goto [B33]

            
 
        If Target = [B34] Then
            Application.Goto [D34]

        If Target = [D34] Then
            Application.Goto [C38]

        If Target = [C40] Then
            Application.Goto [D38]
            
        If Target = [D40] Then
            Application.Goto [C46]

        If Target = [C49] Then
            Application.Goto [D46]
            End If
        End If
        End If
         End If
          End If
           End If
            End If
             End If
              End If



End Sub
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   If Target.CountLarge = 1 Then Exit Sub
   Select Case Target.Address(0, 0)
      Case "B21"
         Application.Goto [B24]
      Case "B29"
         Application.Goto [E26]
      Case "E26"
         Application.Goto [B33]
   End Select
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
411
Office Version
  1. 2019
Platform
  1. Windows
When i have it like this nothing happens. It's the same as hitting enter with no code there

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.CountLarge = 1 Then Exit Sub

Select Case Target.Address(0, 0)



Case "B21"

Application.Goto [B24]



Case "B29"

Application.Goto [E26]



Case "E26"

Application.Goto [B33]



Case "B34"

Application.Goto [D34]



Case "D34"

Application.Goto [C38]



Case "C40"

Application.Goto [D38]



Case "D40"

Application.Goto [C46]



Case "C49"

Application.Goto [D46]


End Select

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
Oops, it should be > 1 not =1 at the beginning.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,653
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,295
Messages
5,571,394
Members
412,386
Latest member
Yasaman
Top