Solved

Listing appears in worksheet

Posted on 2014-09-22
4
58 Views
Last Modified: 2014-09-22
Folks,
When you open the attached workbook under "Category" select 'Dynamic Date Filters'. A list will appear under "Topics" and double-click on 'Filtering with Pivot Tables". This appears (without the arrows):
Offset shows upThis I know. The data that appears is the same that appears in the tab "Topics" in column AE. In "Name Manager" this is labeled 'Category'.
I've looked for why this is happening and again, I'm clueless. When I delete the contents of A3:A23 the listing does not reappear.
Excel-Dashboards-Rev.xlsm
0
Comment
Question by:Frank Freese
  • 3
4 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40336968
Add line 12.
Private Sub Workbook_Open()
'Dim lngLastCat As Long
'
'    lngLastCat = Range("A65536").End(xlUp).Row
'
'    With Worksheets("Menu")
'        .Activate
'        .Range("A3:A" & lngLastCat).ClearContents
'        .Range("A3:A" & Range("Category").Rows.Count + 2) = Range("Category").Value
'    End With

    Worksheets("Menu").Activate
    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."

    ActiveSheet.ScrollBars("vscrTopic").Value = 1
End Sub

Open in new window

0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40336981
Without that line the last sheet you looked at became the ActiveSheet when the workbook was reopened and so everything in Workbook_Open including the call to GenCategoryFormulas was run against that sheet. GenCategoryFormulas results in the Category list and so it wound up on Filtering With Pivot Tables page rather than MainMenu.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40337002
BTW I think we saw evidence of this happening before when we saw a similar misplaced list in the Topics sheet a while back and at that time I assumed that it was the result of some testing.
0
 

Author Closing Comment

by:Frank Freese
ID: 40337073
BTW - this has appeared in the past and I do recall you believed it was the result of some testing. Thanks for all you do for me. We're getting this tweaked. Your explanation makes sense!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now