Solved

Listing appears in worksheet

Posted on 2014-09-22
4
60 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 47

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 47

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 47

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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