Solved

Listing appears in worksheet

Posted on 2014-09-22
4
61 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 48

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 48

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 48

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

690 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