Deleted scrolling code

Folks,
In modifying a workbook (Statistics) to serve as Dashboards I've eliminated code that did scrolling under the Topics section and have yet to figure out how to restore it to the new w/b/ Dashboards?
Excel-Dashboards.xlsm
Excel-Statistics.xlsm
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Your Dashboard workbook contains annotations of the changes made in the Statistics workbook. Do you want to remove those annotations? In other words this
Private Sub Workbook_Open()
'******* Menu Changed Start *******
'   With Worksheets("Menu")
'        .Activate
'        .lblDescription.Caption = ""
'        .lstCat.ListIndex = 0
'        .lstCat.Select
'    End With
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
    ' Select the first category
    Range("A3").Select
'******* Menu Changed End *********

    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."

    '******* Add scrollbar Start *******
    ActiveSheet.ScrollBars("vscrTopic").Value = 1
    '******* Add scrollbar End *********
End Sub

Open in new window

will become this
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
    ' 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
Frank FreeseAuthor Commented:
All annotations not need should be removed to keep things "clean" - yes please
0
Martin LissOlder than dirtCommented:
OK here you go. I cleaned up the workbook and also added a new sheet called ScrollbarData to hold the data for the scrollbar rather than having that data on the Topics sheet. I also made that tabs for SheetNames, Topics, and ScrollbarData the same red color.
Excel-Dashboards.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Frank FreeseAuthor Commented:
The scroll bars will only appear when need? What's the purpose for the tab ScrollbarData?
0
Martin LissOlder than dirtCommented:
The scrollbar will only appear if there are more than 23 (? I forget exactly how many) topics.

ScrollbarData stores parameters that are necessary for the scrollbar to work properly. You don't have to do any maintenance on that tab.
0
Frank FreeseAuthor Commented:
Thanks - again great job.
0
Frank FreeseAuthor Commented:
An excellent job, once again.
Really appreciate all the hard work you do to help me.
0
Frank FreeseAuthor Commented:
Martin,
I added another Category with 22 Topics, yet only 21 topics show? Attached is the w/b (Yours with just added Category / Topics)
Excel-Dashboards.xlsm
0
Martin LissOlder than dirtCommented:
I made 3 changes marked with 'new. Please look for them because I believe that one of them should also be changed to match in the Statistics workbook. The comment says which one. Note that if you can help it, please don't change cells D2 or D24. Somehow the formula in D24 got changed.
Excel-Dashboards.xlsm
0
Frank FreeseAuthor Commented:
The only change I made was to remove the label Excel 2013 Dashboard that was in cell C1 and made it a header. I may have inadvertently deleted the D2 formula. Now that I know that I'll be more careful. I also saw where you made the change to apply to Statistics - I'll do that also.
Thank you - I apologize if the problems was something I did.
0
Martin LissOlder than dirtCommented:
I also saw where you made the change to apply to Statistics
No I didn't. My copy of Statistics which should be pretty recent says (in GenFormulas)

    Sheets("Topics").Range("T3").Value = intCount
    
    If intCount < 23 Then
        Sheets("Menu").ScrollBars("vscrTopic").Visible = False
    Else
        Sheets("Menu").Shapes("vscrTopic").Visible = True
    End If

Open in new window


while the code in Dashboards says
    'new I BELIEVE THAT THIS SHOULD ALSO BE CHANGE IN STATISTICS
'    If intCount < 23 Then
    If intCount < 22 Then

        Sheets("Menu").ScrollBars("vscrTopic").Visible = False
    Else
        Sheets("Menu").Shapes("vscrTopic").Visible = True
    End If

Open in new window

0
Frank FreeseAuthor Commented:
I didn't mean to imply that changes were made to Statistics - I noticed your comment in Dashboards that implied in Statistics I should change "If intCount <23" to "If inCount <22" My version of "Statistics" has "If intCount <23". Make the change to <22 in statistics? That's all I was asking.
0
Martin LissOlder than dirtCommented:
OK you're right and I misread what you said. And yes I think that 23 should be changed to 22 in Statistics. It's logical that it should be 22 because there are 21 rows in the Topics area so anything less than 22 does not require a scrollbar while if there's more than 22 a scrollbar is required. Maybe if it were written this way it would be clearer.

    If intCount > 21 Then
        Sheets("Menu").ScrollBars("vscrTopic").Visible = True
    Else
        Sheets("Menu").Shapes("vscrTopic").Visible = False
    End If

Open in new window

0
Frank FreeseAuthor Commented:
The above code has now been changed in Statistics. Sorry for the miscommunications on my part.
BTW, would the same logic apply to Dashboards?
0
Martin LissOlder than dirtCommented:
That's where I found the problem so yup.
0
Frank FreeseAuthor Commented:
thanks
changes made
here's the files I'm using - lots of work on Statistics - range manger - weird. clean up there. then on to formula validation appreciate everything
Excel-Dashboards.xlsm
Excel-Statistics.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.