Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet tabs as menu?
Is there a way of setting up levels of worksheet tabs to use as a menu
system, instead of having to scroll along dozens of them? Thanks, Keith |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet tabs as menu?
OOps... should have mentioned I'm in Excel 2000
"Kevryl" wrote: Is there a way of setting up levels of worksheet tabs to use as a menu system, instead of having to scroll along dozens of them? Thanks, Keith |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet tabs as menu?
Kevryl
One row of tabs is all you get. You can right-click on the navigation arrows at lower left to see a list of 15 sheets plus"more sheets". One other method is to set up an index sheet with hyperlinks to sheets or do what I prefer................ Use VBA code from Bob Phillips.............. Sub BrowseSheets() Const nPerColumn As Long = 38 'number of items per column Const nWidth As Long = 13 'width of each letter Const nHeight As Long = 18 'height of each row Const sID As String = "___SheetGoto" 'name of dialog sheet Const kCaption As String = " Select sheet to goto" 'dialog caption Dim i As Long Dim TopPos As Long Dim iBooks As Long Dim cCols As Long Dim cLetters As Long Dim cMaxLetters As Long Dim cLeft As Long Dim thisDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As OptionButton Application.ScreenUpdating = False If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.DialogSheets(sID).Delete Application.DisplayAlerts = True On Error GoTo 0 Set CurrentSheet = ActiveSheet Set thisDlg = ActiveWorkbook.DialogSheets.Add With thisDlg .Name = sID .Visible = xlSheetHidden 'sets variables for positioning on dialog iBooks = 0 cCols = 0 cMaxLetters = 0 cLeft = 78 TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count If i Mod nPerColumn = 1 Then cCols = cCols + 1 TopPos = 40 cLeft = cLeft + (cMaxLetters * nWidth) cMaxLetters = 0 End If Set CurrentSheet = ActiveWorkbook.Worksheets(i) cLetters = Len(CurrentSheet.Name) If cLetters cMaxLetters Then cMaxLetters = cLetters End If iBooks = iBooks + 1 .OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5 .OptionButtons(iBooks).text = _ ActiveWorkbook.Worksheets(iBooks).Name TopPos = TopPos + 13 Next i .Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24 CurrentSheet.Activate With .DialogFrame .Height = Application.Max(68, _ Application.Min(iBooks, nPerColumn) * nHeight + 10) .Width = cLeft + (cMaxLetters * nWidth) + 24 .Caption = kCaption End With .Buttons("Button 2").BringToFront .Buttons("Button 3").BringToFront Application.ScreenUpdating = True If .Show Then For Each cb In thisDlg.OptionButtons If cb.Value = xlOn Then ActiveWorkbook.Worksheets(cb.Caption).Select Exit For End If Next cb Else MsgBox "Nothing selected" End If Application.DisplayAlerts = False .Delete End With End Sub Gord Dibben MS Excel MVP On Mon, 24 Jul 2006 16:39:01 -0700, Kevryl wrote: Is there a way of setting up levels of worksheet tabs to use as a menu system, instead of having to scroll along dozens of them? Thanks, Keith Gord Dibben MS Excel MVP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet tabs as menu?
If you right-click on the vcr-like arrows in the bottom left of the sheet
tabs bar (just to the left of the sheet tabs), you'd get a pop-up menu of all the visible sheets for easy navigation. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Kevryl" wrote: OOps... should have mentioned I'm in Excel 2000 "Kevryl" wrote: Is there a way of setting up levels of worksheet tabs to use as a menu system, instead of having to scroll along dozens of them? Thanks, Keith |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet tabs as menu?
You can also make an Index Sheet. I made a table sort of looking index, and
used hyper links as the title for each tab. So when you click on the name of a tab, you go right to that tab. On each sheet I added a link to go back to the index page. If I knew how to attach a file (or if you can) I'd send you a copy of my worksheet. It's actually pretty cool. Lots of drop downs, comment windows for instructions, etc. Umm . . . I use 2003. So maybe some of what I did won't work on your version. Like colored tabs. But I'd think most of it would work fine. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel worksheet Menu Toolbar | Excel Worksheet Functions | |||
Customizing Excel Worksheet Menu Bar without VBA coding | Excel Discussion (Misc queries) | |||
Customizing Worksheet Menu Bar for a workbook without VBA coding | Excel Discussion (Misc queries) | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
Worksheet Tabs | Excel Discussion (Misc queries) |