Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Navigation suggestions? 95 sheets! (2000/SR-1)

    One of our accounting department log files is maintained as a workbook with a large and growing number of sheets, each named for the client to which it pertains. Scrolling the tabs is crazy-making. I can envision building a navigation page with a button for each letter of the alphabet which generate a dynamic list of matching tabs for one-click navigation, but (1) I don't have time to figure out how to do that and (2) I can't believe there isn't something "built in" to go to a tab more efficiently. "Find" might be the best short-term workaround. Is there another/better way to leap among sheets in a workbook?

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)

    Right-click the horizontal arrows at the far left side of the sheet tabs for a pop-up menu of all sheets.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)

    That's great. I can right-click, then click More Sheets..., then choose from the Activate dialog. I wonder if I can get there faster? Yes!

    This will pop up the dialog:
    <hr><pre>Public Sub PopActivateSheets()
    CommandBars("Workbook tabs").Controls("More Sheets...").Execute
    End Sub</pre>

    <hr>
    Thanks for your help in tracking this down.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    Hi Jim. I've tried your code in Excel 2000 and receive a runtime error 5 - invalid procedure call or argument.

    Any suggestions?

    Alan

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)

    Hi Jefferson.

    This is what I do...
    I make a navigation sheet and make the list of clients. In the cell that corresponding to client, I create a HyperLink (I believe that it is Ctl +K in the version in English) and I associate it to the Sheet of client.
    (<font color=red>text</font color=red> See my attachment file, also it has a routine that orders your worksheets ascending<font color=black>text</font color=black>)


    I believe that it is a easy way
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    <pre>Controls("More Sheets...")
    </pre>


    only exists if there are too many sheets to be displayed in the pop-up. If there are 16 or fewer sheets, the More Sheets... control does not exist.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    On my installed version of XL 97 at 800 x 600 resolution, the maximum popup before the "More Sheets ..." dialog appears is 16, can a few other folks test and see if this is a constant?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    I have 1024x768, Excel 2000. The limit is 16.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    So we need something like this, with my questions resolved, and code de-uglied ... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Sub showtabs()
    Dim sSht As Worksheet
    Dim cCht As Chart
    Dim intC As Integer
    For Each sSht In ThisWorkbook.Sheets
    If sSht.Visible = True Then intC = intC + 1
    Next sSht
    <font color=red> 'do charts show in the pop-up? prolly a cleaner way to do this ...</font color=red>
    For Each cCht In ThisWorkbook.Charts
    If cCht.Visible = True Then intC = intC + 1
    Next cCht
    If intC < 17 Then
    <font color=red> 'show the appropriate commandbar, whatever it is, any takers?</font color=red>
    Else
    CommandBars("Workbook tabs").Controls("More Sheets...").Execute
    End If
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    John - try this.

    <pre> Dim sht, n As Integer

    n = 0

    For Each sht In ActiveWorkbook.Sheets
    If sht.Visible = -1 Then n = n + 1
    Next sht

    If n <= 16 Then
    CommandBars("Workbook tabs").ShowPopup
    Else
    CommandBars("Workbook tabs").Controls("More Sheets...").Execute
    End If
    </pre>


    Comments: 1) "Sheets" collection includes worksheets and chart sheets, so there is no need treat each one differently in this context. 2) ShowPopup shows the little pop-up you see if you right-click the sheet nav arrows when the number of visible sheets is 16 or less.

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    Hi Jim

    Method works well here. The only addition I'd like to make is for the checkbox mark on the popup to be restored to the currently active sheet. I'm presuming that looping through the sheets somehow sets this to the last visible sheet in the workbook (tried only for n<17).

    I'm sure there's a simple one liner to reset the checked box to the current sheet, but being new to VBA, I had no luck locating the appropriate code.

    cheers

    Alan

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)

    You should click here and download the FREE add-in. There is a utility included that will create an INDEX sheet for you with hyper-links to every sheet in your project. It takes 2-seconds!

    If you're not a fan of add-ins, you could simply uninstall the add-in once you've created the index sheet. BTW, once the index sheet is created (and it does a good job), you can do a little cosmetic work if you desire. But the tedious part of building the sheet is handled in an instant. Very nice!
    - Ricky

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)

    Ricky,

    Here's some trivial code (never thought I'd hear me say that) that adds a new sheet, called xxSheetTOC, loops thru all the sheets in the workbook including the new one (could be skipped), and adds a link to each sheet in the bunch. I just don't like add-ins since they take more time at load time. You could pretty this up also - it's just bare bones. Also, some extra code would be needed if you wanted to run this again after you've added a new sheet to update the TOC (either delete the xxSheetTOC if it exists and start again, probably the easiest; or go thru the sheets and add a link to the TOC sheet if not already in the list).

    Sub SheetTOC()
    Dim sht As Worksheet, shtname As String, i As Integer

    Sheets.Add 'add a sheet to left of whatever sheet is current; move if desired
    shtname = ActiveSheet.Name
    Sheets(shtname).Select
    Sheets(shtname).Name = "xxSheetTOC" 'need unique name
    Cells(1, 1) = "Sheet Name"
    i = 1

    For Each sht In ActiveWorkbook.Sheets
    i = i + 1
    Cells(i, 1).Select
    Cells(i, 1) = sht.Name 'includes the xxSheetTOC
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=sht.Name & "!A1"
    Next sht
    End Sub

    fred

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    I didn't test enough. If there are too few sheets for the More Sheets... item to be available you will get that error. This code works around it:
    <pre>Public Sub PopActivateSheets()
    On Error Resume Next
    CommandBars("Workbook tabs").Controls("More Sheets...").Execute
    If Err.Number <> 0 Then
    If Err.Number = 5 Then 'Not that many tabs
    CommandBars("Workbook tabs").ShowPopup
    Else
    MsgBox "Error number" & Err.Number & vbCrLf & Err.Description
    End If
    End If
    End Sub</pre>

    And call me Jefferson. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Navigation suggestions? 95 sheets! (2000/SR-1)-M

    Thanks Fred. Using your code in combination with Jim's, I can achieve the desired result, to have the active sheet correctly checked in the popup:

    Dim sht, n As Integer
    Dim small As Boolean
    Dim shtname As String

    n = 0
    small = True
    shtname = ActiveSheet.Name

    For Each sht In ActiveWorkbook.Sheets ' count sheets
    If sht.Visible = -1 Then n = n + 1
    Next sht
    If n > 16 Then small = False

    Sheets(shtname).Select ' resets checkmark in popup to active sheet

    ' show appropriate popup
    If (small) Then
    CommandBars("Workbook tabs").ShowPopup
    Else
    CommandBars("Workbook tabs").Controls("More Sheets...").Execute
    End If

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •