[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Deleted scrolling code

Posted on 2014-08-08
16
Medium Priority
?
77 Views
Last Modified: 2014-08-08
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
0
Comment
Question by:Frank Freese
  • 9
  • 7
16 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40248827
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
 

Author Comment

by:Frank Freese
ID: 40248853
All annotations not need should be removed to keep things "clean" - yes please
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40249391
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Frank Freese
ID: 40249413
The scroll bars will only appear when need? What's the purpose for the tab ScrollbarData?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40249516
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
 

Author Comment

by:Frank Freese
ID: 40249522
Thanks - again great job.
0
 

Author Closing Comment

by:Frank Freese
ID: 40249524
An excellent job, once again.
Really appreciate all the hard work you do to help me.
0
 

Author Comment

by:Frank Freese
ID: 40249688
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40249838
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
 

Author Comment

by:Frank Freese
ID: 40249902
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40249938
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
 

Author Comment

by:Frank Freese
ID: 40249945
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40249970
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
 

Author Comment

by:Frank Freese
ID: 40249988
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40249991
That's where I found the problem so yup.
0
 

Author Comment

by:Frank Freese
ID: 40250025
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

834 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