Run-time error 91

Folks,
When my workbook is launched it opens with the following error:
DashboardI added in the Name Manager a range at AA from the Topics worksheet and it is labeled TextFormulaFunction. No problem here.
My workbook requires I add the worksheet name to the worksheet "SheetNames" - no problem here.
My workbook requires the name of a Category in the Topics worksheet at AE - no problem here.
Code was need on the Menu tab in the Worksheet_SelectionChange event. No problem here
However, when I select the new Category, in the case Text Formulas and Function I get this:
Objectat this part of the code
run time errorMy ranges from A:Z work fine. It's when I go beyond Z that this problem comes up
Dashboard-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.

Martin LissOlder than dirtCommented:
Just before the last line in Workbook_Open add

Sheets("Menu").Activate
0
Martin LissOlder than dirtCommented:
You should also add Application.ScreenUpdaing lines so that the sub looks like this
Private Sub Workbook_Open()

    Application.ScreenUpdating = False
    
    GenCategoryFormulas Sheets("Topics").Range("Category").Rows.Count
    
    Sheets("ScrollbarData").Range("C3").Value = Range("Category").Rows.Count

    ' Select the first category
    Range("A3").Select

    ActiveWindow.Zoom = 90
    '  Make sure that the statusbar is visible
    Application.DisplayStatusBar = True
    
    Application.StatusBar = "Concept and design by Frank Freese. Programming by MartinLiss at www.experts-exchange.com."
    
    Sheets("Menu").Activate
    ActiveSheet.ScrollBars("vscrTopic").Value = 1
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
Frank FreeseAuthor Commented:
Before I actually change the code in the Workbook_Open event I want to be positive this is what you're asking:
Private Sub Workbook_Open()

    Application.ScreenUpdating = False
    
    GenCategoryFormulas Sheets("Topics").Range("Category").Rows.Count
    
    Sheets("ScrollbarData").Range("C3").Value = Range("Category").Rows.Count

    ' Select the first category
    Range("A3").Select

    ActiveWindow.Zoom = 90
    '  Make sure that the statusbar is visible
    Application.DisplayStatusBar = True
    
    Application.StatusBar = "Concept and design by Frank Freese. Programming by MartinLiss at www.experts-exchange.com."
    
    Sheets("Menu").Activate
    ActiveSheet.ScrollBars("vscrTopic").Value = 1
    Sheets("Menu").Activate
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Martin LissOlder than dirtCommented:
You don't need line 20 in your code. Even after you make that change you'll run into another problem which I'm working on now.
0
Martin LissOlder than dirtCommented:
The problems occurred when you actually clicked Text Formulas and Functions
The first problem was mine and was due to the gstrTopicCol calculation in Menu's Worksheet_SelectionChange event. It worked fine when the Topics column was a single letter, but the new AA column caused a problem. Here's the fix.

In that sub, change
gstrTopicCol = Mid$(Range("D3").Formula, InStr(1, Range("D3").Formula, "!") + 1, 1)

Open in new window

to
    Dim intStartPos As Integer
    Dim intEndPos As Integer
    intStartPos = InStr(1, Range("D3").Formula, "!") + 1
    intEndPos = InStr(intStartPos, Range("D3").Formula, ",") - 1
    gstrTopicCol = Mid$(Range("D3").Formula, intStartPos, intEndPos - intStartPos)

Open in new window


To be consistent the two new Dim statements should be moved to the top of the sub with the rest of them.

The other problem is yours.
In Name Manager you had TextFormulaFunction as the name of your new range while in code you referred in at least one of the two places that name is referred to you had TextFormulaFunctions. I changed the Named Range name to TextFormulaFunctions and made sure that both places in the code matched.
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
Frank FreeseAuthor Commented:
OK...look like we're back on track. Changes from this thread have been implemented. Looks good.
Thanks again.
0
Frank FreeseAuthor Commented:
again, many thanks
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.