Not indexing to correct worksheet

Frank Freese
Frank Freese used Ask the Experts™
on
Folks,
Open the attached workbook. When I select the second item in the Category list box and then double-click the first Topic (there's only one at this point) of that Category rather than going to the correct worksheet it goes to the first worksheet.
Financial-Analysis.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Note the changes in lines 22 and 24.
Private Sub lstCat_Click()
'new
'Dim intCatFactor As Integer
'******* 4/22a Start *******
'******* 4-23a Start *******
'If lstCat.Text = "Analysis ToolPak" And lstTopic.List(0) = "Anova: Single Factor" Then
If ActiveSheet.Name <> "Menu" Then
'******* 4-23a End *********
    Exit Sub
End If
'******* 4/22a End *********

Select Case lstCat.ListIndex
Case 0
    lstTopic.ListFillRange = ""
    lstTopic.ListFillRange = "Analysis"
    Sheets("Menu").lblDescription.Caption = ""
    intCatFactor = 10
Case 1
    lstTopic.ListFillRange = ""
    lstTopic.ListFillRange = "Financial"
    intCatFactor = 10 + Sheets("Topics").Range("Analysis").Rows.Count
    Sheets("Menu").lblDescription.Caption = ""
    'intCatFactor = 10
End Select

Open in new window


The SheetNames sheet lists the names of the detail sheets. The first 20 are for the DataAnalysisToolpak and the next block starting at row 30 is for Financial. intCatFactor is row number of the sheet you want to go to so with DataAnalysisToolpak sheets you start with 10 and add to it the index number of the Topic. When you want to select a sheet related to Financial you need to start with 10 and add all the rows for the previous topic and the add the index of the current topic. So if you had a 3rd Category you would have to add these lines after line 24.

Case 2
    lstTopic.ListFillRange = ""
    lstTopic.ListFillRange = "Financial"
    intCatFactor = 10 + Sheets("Topics").Range("Analysis").Rows.Count _
                                  + Sheets("Topics").Range("Financial")                     
    Sheets("Menu").lblDescription.Caption = ""
    'intCatFactor = 10

Open in new window


Please let me know if you have ANY questions about this since it's key to the workbook.

Author

Commented:
No questions - just stupidity on my part, When I made the changes I put them under the lstTopic and that's not where it belonged. I'm watching the Grizzles getting whipped and doing this at the same time.
Thank you Martin. You're so patient with me. Have a great day tomorrow.
Frank

Author

Commented:
Again, thank you.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Author

Commented:
If you're up and ready for a question I have one for you on:
Thread: ID: 40036199
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
That looks like a post ID. If you have already created the question then I don't know why I didn't get an automatic notification but you can just post the URL here.

Author

Commented:
Question created - but I have a different problem so I want to post a differennt question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial