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

Topic

New Reply

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

Replies

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

Reply | Quote

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

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

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

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

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

Reply | Quote

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

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

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

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

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

Reply | Quote

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

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.

Reply | Quote

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

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

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

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

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