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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Navigation suggestions? 95 sheets! (2000/SR-1)

    Author
    Topic
    WSjscher2000
    AskWoody Lounger
    March 12, 2002 at 5:33 pm #368094

    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?

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • WSJim Cone
      AskWoody Lounger
      March 12, 2002 at 5:44 pm #575847

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

      Reply | Quote
      • WSjscher2000
        AskWoody Lounger
        March 12, 2002 at 7:58 pm #575883

        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:
        [indent]

        Public Sub PopActivateSheets()
CommandBars("Workbook tabs").Controls("More Sheets...").Execute
End Sub

        [/indent]Thanks for your help in tracking this down.

        Reply | Quote
        • WSAlanMiller
          AskWoody Lounger
          March 15, 2002 at 1:37 pm #576457

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

          Any suggestions?

          Alan

          Reply | Quote
          • WSJIMbythebay
            AskWoody Lounger
            March 15, 2002 at 4:53 pm #576500 
            Controls("More Sheets...")

            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.

            Reply | Quote
            • WSJohnBF
              AskWoody Lounger
              March 15, 2002 at 7:57 pm #576524

              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?

              Reply | Quote
            • WSJIMbythebay
              AskWoody Lounger
              March 15, 2002 at 8:21 pm #576528

              I have 1024×768, Excel 2000. The limit is 16.

              Reply | Quote
            • WSJohnBF
              AskWoody Lounger
              March 15, 2002 at 9:11 pm #576534

              So we need something like this, with my questions resolved, and code de-uglied … grin

              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
              ‘do charts show in the pop-up? prolly a cleaner way to do this …
              For Each cCht In ThisWorkbook.Charts
              If cCht.Visible = True Then intC = intC + 1
              Next cCht
              If intC < 17 Then
              ‘show the appropriate commandbar, whatever it is, any takers?
              Else
              CommandBars(“Workbook tabs”).Controls(“More Sheets…”).Execute
              End If
              End Sub

              Reply | Quote
            • WSJIMbythebay
              AskWoody Lounger
              March 15, 2002 at 9:48 pm #576540

              John – try this.

                  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

              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.

              Reply | Quote
            • WSAlanMiller
              AskWoody Lounger
              March 16, 2002 at 11:24 am #576617

              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

              Reply | Quote
            • WSJIMbythebay
              AskWoody Lounger
              March 18, 2002 at 4:06 pm #576915

              Use Rory’s code, it is simpler and gives the behavior you want.

              Reply | Quote
            • WSAlanMiller
              AskWoody Lounger
              March 18, 2002 at 11:48 pm #577001

              I can see that this is a better solution. The problem with the solutions posted prior to Rory’s, was that counting the sheets resulted in the incorrect sheet being checked in the popup. My solution corrected this problem, but subsequently, Rory’s solution doesn’t even encounter/cause it.

              Alan

              Reply | Quote
          • WSjscher2000
            AskWoody Lounger
            March 17, 2002 at 1:33 am #576693

            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:

            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

            And call me Jefferson. wink

            Reply | Quote
            • WSrory
              AskWoody Lounger
              March 17, 2002 at 6:16 pm #576796

              Hi Jefferson,
              You could also use something like:

                  With Application.CommandBars("Workbook tabs")
        If .Controls(16).Caption  "More Sheets..." Then
            .ShowPopup
        Else
            .Controls("More Sheets...").Execute
        End If
    End With

              FWIW.

              Reply | Quote
    • WSServando
      AskWoody Lounger
      March 15, 2002 at 4:34 pm #576498

      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.
      (text See my attachment file, also it has a routine that orders your worksheets ascendingtext)

      I believe that it is a easy way

      Reply | Quote
    • WSTricky
      AskWoody Lounger
      March 16, 2002 at 3:17 pm #576637

      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!

      Reply | Quote
      • WSfburg
        AskWoody Lounger
        March 16, 2002 at 5:57 pm #576660

        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

        Reply | Quote
        • WSAlanMiller
          AskWoody Lounger
          March 17, 2002 at 12:42 pm #576765

          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

          Reply | Quote
    Viewing 2 reply threads
    Reply To: Navigation suggestions? 95 sheets! (2000/SR-1)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.