VBA code not working consistently

Preacherman771 · Jan 27, 2022

After being helped with a previous project dealing with dropdown list and VBA code resulting in working perfectly, I had a similar task on the project which I'm currently working on and used the VBA code making the proper changes according to worksheet. When applying the code and changing the necessary changes and pressing Ctr-Q it worked fine once and when I attempted a second and further attempts, it no longer does anything. What I'm trying to accomplish is pull down on the drop list and when selecting a given item, it should navigate to a given point on the worksheet. I do need to share that I also have another dropdown list which has it's own VBA code, and is still working properly, for the current worksheet which changes the schedule information as a team name is selected. When I applied the new VBA code above the other VBA code series, as stated stated earlier, it worked once, and then would no longer do anything. I even entered it at the end of the current codes.

Here is the a copy of the worksheet which has both dropdown lists. The one in question is the one which is "Week-1, Week-2, etc". I have pasted the VBA codes for both dropdowns. The first group is for the one in question and the other two are for the other dropdown which is working accurately:

NFL 2021-2022 Standings (Template).xlsm

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

2

NFL 2021/22

3

Team Game Schedule

4

5

Buffalo Bills

Week-4

6

7

8

REGULAR SEASON

9

10

11

12

13

-

14

15

LOGO

@

Pittsburgh Steelers (1-0-0)

16

1:00 PM EDT

CBS

17

Bills Stadium, Orchard Park

Next Opponent:

@

Miami Dolphins (1-1-0)

18

19

20

21

-

22

23

LOGO

@

Miami Dolphins (1-1-0)

24

1:00 PM EDT

FOX

25

Hard Rock Stadium, Miami Gardens

Next Opponent:

@

Washington Football (1-2-0)

26

27

28

29

-

30

31

LOGO

@

Washington Football (1-2-0)

32

1:00 PM EDT

FOX

33

Bills Stadium, Orchard Park

Next Opponent:

@

Houston Texans (1-3-0)

34

Team_Schedule

Cell Formulas
Range	Formula
G2	G2	=Sheet2!$I$8
G13	G13	=Calc_Team!B6
J13,J29,J21	J13	=IFERROR(INDEX(Tab_Schedule[Date],MATCH(1,($F$5=Tab_Schedule[Away])($G13=Tab_Schedule[Wk'#]),0)),INDEX(Tab_Schedule[Date],MATCH(1,($F$5=Tab_Schedule[Home])($G13=Tab_Schedule[Wk'#]),0)))
K13,K29,K21	K13	=IFERROR(INDEX(Tab_Weekly[Weekly],MATCH(1,(#REF!=Tab_Weekly[Team])*($G13=Tab_Weekly[Wk'#]),0)),"")
N13,N29,N21	N13	=IF($P13<>"---","FINAL","")
P13,P29,P21	P13	=IF($J15="BYE","---",IFERROR(INDEX(Tab_Schedule[ResA],MATCH(1,($F$5=Tab_Schedule[Away])($G13=Tab_Schedule[Wk'#]),0)),IFERROR(INDEX(Tab_Schedule[ResH],MATCH(1,($F$5=Tab_Schedule[Home])($G13=Tab_Schedule[Wk'#]),0)),"---")))
Q13,Q29,Q21	Q13	=IF($J15="BYE","---",IFERROR(INDEX(Tab_Schedule[ScoreA],MATCH(1,($F$5=Tab_Schedule[Away])($G13=Tab_Schedule[Wk'#]),0)),IFERROR(INDEX(Tab_Schedule[ScoreH],MATCH(1,($F$5=Tab_Schedule[Home])($G13=Tab_Schedule[Wk'#]),0)),"---")))
S13,S29,S21	S13	=IF($J15="BYE","---",IFERROR(INDEX(Tab_Schedule[ScoreH],MATCH(1,($F$5=Tab_Schedule[Away])($G13=Tab_Schedule[Wk'#]),0)),IFERROR(INDEX(Tab_Schedule[ScoreA],MATCH(1,($F$5=Tab_Schedule[Home])($G13=Tab_Schedule[Wk'#]),0)),"---")))
I15,I31,I23	I15	=IF($J15="BYE","",IF(IFERROR(INDEX(Tab_Schedule[Stadium],MATCH(1,(#REF!=Tab_Schedule[Home])*($G13=Tab_Schedule[Wk'#]),0)),"---")="---","@",""))
K16,K32,K24	K16	=IF($J15="BYE","---",IFERROR(INDEX(Tab_Schedule[Network],MATCH(1,($F$5=Tab_Schedule[Away])($G13=Tab_Schedule[Wk'#]),0)),IFERROR(INDEX(Tab_Schedule[Network],MATCH(1,($F$5=Tab_Schedule[Home])($G13=Tab_Schedule[Wk'#]),0)),"---")))
J15,J31,J23	J15	=IF(IFERROR(INDEX(Tab_Schedule[Away],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Home]),0)),IFERROR(INDEX(Tab_Schedule[Home],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Away]),0)),""))="BYE","BYE",(IF(IFERROR(INDEX(Tab_Schedule[Away],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Home]),0)),IFERROR(INDEX(Tab_Schedule[Home],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Away]),0)),""))="","",IFERROR(INDEX(Tab_Schedule[Away],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Home]),0)),IFERROR(INDEX(Tab_Schedule[Home],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Away]),0)),""))))&" ("&INDEX(Tab_Weekly[Weekly],MATCH(1,($G13=Tab_Weekly[Wk'#])* (IFERROR(INDEX(Tab_Schedule[Away],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Home]),0)),INDEX(Tab_Schedule[Home],MATCH(1,($G13=Tab_Schedule[Wk'#])($F$5=Tab_Schedule[Away]),0)))=Tab_Weekly[Team]),0))&")")
J16,J32,J24	J16	=IF($J15="BYE","---",IFERROR(INDEX(Tab_Schedule[Time],MATCH(1,($F$5=Tab_Schedule[Away])($G13=Tab_Schedule[Wk'#]),0)),IFERROR(INDEX(Tab_Schedule[Time],MATCH(1,($F$5=Tab_Schedule[Home])($G13=Tab_Schedule[Wk'#]),0)),"---")))
J17,J33,J25	J17	=IF($J15="BYE","---",IFERROR(INDEX(Tab_Schedule[Stadium],MATCH(1,($F$5=Tab_Schedule[Home])($G13=Tab_Schedule[Wk'#]),0)),IFERROR(INDEX(Tab_Schedule[Stadium],MATCH(1,($F$5=Tab_Schedule[Away])($G13=Tab_Schedule[Wk'#]),0)),"---")))
N17,N33,N25	N17	=IF(G13="Week-18","FINAL GAME","Next Opponent:")
O17,O33,O25	O17	=IF(OR($P17="---",$G13="Week-17"),"",$I23)
P17,P33,P25	P17	=IF(OR($N17="Final Game",$G13="Week-18"),"",$J23)
G21	G21	=Calc_Team!B7
G29	G29	=Calc_Team!B8
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F5:O6	List	=Calc_Team!$L$6:$L$37
P5:S6	List	=Calc_Team!$B$6:$B$23

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("P5"), Target) Is Nothing Then
        Dim JumpTo As Long, WeekNo As String
        WeekNo = Target.Value
        JumpTo = Application.Match(WeekNo, Me.Columns(7), 0)    '<~~ assumes column G (1) change to suit
        Application.Goto Me.Cells(JumpTo, 1), Scroll:=1
    End If
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

Micron · Jan 27, 2022

Suggest
a) put a break point on the first line of any procedure you want to validate (click on the left grey bar beside the line) and click whatever button or do whatever action is required to make the code run. If there is no such trigger, you can place the insertion point in the code press F5, then F8 to step through the code line by line. Check your variable values as you go and watch the code execution. Note: a line must be executed in order to see its effect (e.g. if it is performing a calculation).

b) enclose your code in code tags to retain formatting and indentation.
I usually won't bother to read code when it's pasted like that, but that's just me.

Preacherman771 · Jan 28, 2022

Thank you for your prompt reply. As I'm not familiar with coding, not sure what you meant under your a) comments. Below I am copying exactly as it appears in View Codes. I hope this is what you prefer as you stated in comment b). Again, the first group is the coding which worked initially once, but never again. The other two groups are for the other dropdown, which is working properly.

VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("P5"), Target) Is Nothing Then
        Dim JumpTo As Long, WeekNo As String
        WeekNo = Target.Value
        JumpTo = Application.Match(WeekNo, Me.Columns(7), 0)    '<~~ assumes column G (1) change to suit
        Application.Goto Me.Cells(JumpTo, 1), Scroll:=1
    End If
End Sub
___________________________________________________________________________________________________________________________

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub
___________________________________________________________________________________________________________________________

Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

Preacherman771 · Jan 28, 2022

I just checked my post, and noticed that when I posted it, it did not keep exactly the way I had entered the coding with indentions, line breaks, etc. Would you please share how I can do so without posting it changing the way it appears.

RoryA · Jan 28, 2022

Use the code tag options in the toolbar above the text box when posting.

Micron · Jan 28, 2022

As for a), instructions were in brackets - did you try?
Break point by clicking in gray margin:

F5 to begin executing if there is no other method (such as a button click); F8 to continue.

I'm coming from Access vba when I say that I've seen Is Nothing in Excel code several times when there is no object involved and don't understand that. Nothing is the state of an object variable and I'm pretty sure that is not limited to Access. See

Nothing keyword (VBA)

Office VBA reference topic

docs.microsoft.com

When your code first runs I guess it's returning Nothing for that test, but if there is already a value in the cell, then it's no longer true so it won't run. That's just a guess. You could try changing the line to (NOTE - I used A5 for my convenience. You'd have to change that cell reference):

If Not Range("A5") = "" Then

If any column values are formatted as dates or numbers, that might also raise an error. It's not something I'm familiar with because in Access you cannot have text data type in one record and a date data type in the next. So if that doesn't work, post what the problem values are or try

If Nz(Range("A5"),0) >0 Then

VBA code not working consistently

Preacherman771

New Member

Excel Facts

Micron

Well-known Member

Preacherman771

New Member

Preacherman771

New Member

RoryA

MrExcel MVP, Moderator

Micron

Well-known Member

Nothing keyword (VBA)

Similar threads

Forum statistics

Share this page

VBA code not working consistently

Preacherman771

New Member

Excel Facts

Micron

Well-known Member

Preacherman771

New Member

Preacherman771

New Member

RoryA

MrExcel MVP, Moderator

Micron

Well-known Member

Nothing keyword (VBA)

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock