Solved

Listing appears in worksheet

Posted on 2014-09-22
4
59 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 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 4 28
Boolean help 6 27
Update As Well As Add 6 35
Excel "2016 error could not load some objects becuase they are not available on this machine. 7 21
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 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

18 Experts available now in Live!

Get 1:1 Help Now