Excel sub takes me to other locations

Folks,
The below code works OK until I get to line 9: Range("E7").Value = "". When I get there it sends me to other places - spooky. I've attached the workbook and the worksheet is "AverFuelConsump" towards the end of the workbook - tab is first black tab.

   If Range("D7").Text <> "13.23" Then
    Range("E7").Locked = False
    Range("E7").Font.Color = vbWhite
    Range("E7").Interior.Color = vbRed
    Range("E7").Value = "Error"
    CheckFormulaFunction
    Exit Sub
    Else
    Range("E7").Value = ""
    Range("E7").Font.Color = vbBlack
    Range("E7").Interior.Color = vbWhite
    Range("E7").Locked = True
   End If

Open in new window

Excel-Formulas-and-Functions-Rev.xlsm
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Where will we find that code and how is it being executed?

Also, what other 'place' are you being taken to?

Other worksheets? Other workbooks? Other planets?
0
NorieAnalyst Assistant Commented:
For some reason changing the value in E7 is causing the click event of a listbox on the Menu sheet to be triggered.

I think it's becuase the named range used to populate the listbox is being calculated.

One thing you can try is to temporarily turn off automatic calculation.

You can do this like this.
    Application.Calculation = xlCalculationManual

Open in new window


To turn automatic calculation back on:
    Application.Calculation = xlCalculationAutomatic

Open in new window

So to apply that in the sub in question:
Private Sub cmdCkAnserAverageCon_Click()
    Application.Calculation = xlCalculationManual
    If Range("D7").Text <> "13.23" Then
        Range("E7").Locked = False
        Range("E7").Font.Color = vbWhite
        Range("E7").Interior.Color = vbRed
        Range("E7").Value = "Error"
        CheckFormulaFunction
        Exit Sub
    Else
        Range("E7").Value = ""
        Range("E7").Font.Color = vbBlack
        Range("E7").Interior.Color = vbWhite
        Range("E7").Locked = True
    End If

    If Range("D8").Text <> "15.35" Then
        Range("E8").Locked = False
        Range("E8").Font.Color = vbWhite
        Range("E8").Interior.Color = vbRed
        Range("E8").Value = "Error"
        CheckFormulaFunction
        Exit Sub
    Else
        Range("E8").Font.Color = vbBlack
        Range("E8").Interior.Color = vbWhite
        Range("E8").Value = ""
        Range("E8").Locked = True
        Exit Sub
    End If

    If Range("D9").Text <> "15.61" Then
        CheckFormulaFunction
        Exit Sub
    End If

    If Range("D10").Text <> "13.87" Then
        CheckFormulaFunction
        Exit Sub
    End If

    If Range("D12").Text <> "14.19" Then
        CheckFormulaFunction
        Exit Sub
    End If

    If Range("D13").Text <> "14.07" Then
        CheckFormulaFunction
        Exit Sub
    End If

    If Range("D14").Text <> "14.13" Then
        CheckFormulaFunction
        Exit Sub
    End If

    If Range("D15").Text <> "113.46" Then
        CheckFormulaFunction
        Exit Sub
    End If

    FormulasOK
    Range("E7:E15").Font.Color = vbBlack
    Range("E7:E15").Interior.Color = vbWhite
    Range("E7:E15").Value = " "
    Range("E7:E15").Locked = True

    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
As soon as the worksheet is changed on line 9 the worksheet responds by recalculating all UDFs to incorporate the change whether that value affected those functions or not. That is where the code is being carried away.
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Frank FreeseAuthor Commented:
Open the attached workbook
Go to the tab labeled "AverFuelConsump" (it's towards the end)

There's a command button called "Check Answer"
Set a break point at the first Else.
The problem appears

    Range("E7").Value = "" (here's where it falls apart)

It did not execute the code but took me to a different sub:

Private Sub lstCategory_Click()

What I have seen is that is there is nothing in E7 is when I get the problem. The purpose for this bit of code is the clear out any left over messages once the formula has been corrected
0
NorieAnalyst Assistant Commented:
I found the code, and I've posted a possible solution.

By the way, you could avoid this sort of problem by not using named ranges to populate things like listboxes, comboboxes etc.
0
Frank FreeseAuthor Commented:
perfect - thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.