Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can I see a list of the worksheets within a workbook without scro.
I would like to see a vertical list of the names of the worksheets within a
workbook rather than having to scroll across the bottom looking at each tab as it comes into view, is this possible. Excel 2003 |
#2
|
|||
|
|||
Right click on the VCR type controls just to the left of the tabs themselves.
That will pop up a list of sheet names, up to 6 or 7 names long. If there are more than 6 or 7 sheets, you'll see a 'More' option that puts all the sheet names in scrollable list box. Hope that helps "shrn" wrote: I would like to see a vertical list of the names of the worksheets within a workbook rather than having to scroll across the bottom looking at each tab as it comes into view, is this possible. Excel 2003 |
#3
|
|||
|
|||
Hi
See David McRitchie's site for a TOC macro with selectable sheet names http://www.mvps.org/dmcritchie/excel/buildtoc.htm alternatively, right mouse click on the sheet selection buttons (bottom left of screen) and you can choose the worksheet you want to go to from there. Cheers JulieD "shrn" wrote in message ... I would like to see a vertical list of the names of the worksheets within a workbook rather than having to scroll across the bottom looking at each tab as it comes into view, is this possible. Excel 2003 |
#4
|
|||
|
|||
shrn In addition to the other answers you received......... Use Bob Phillips BrowseSheets macro(posted below)which displays a form with option buttons to select a sheet. I personally like this one. 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 Excel MVP On Fri, 25 Mar 2005 09:21:02 -0800, "shrn" wrote: I would like to see a vertical list of the names of the worksheets within a workbook rather than having to scroll across the bottom looking at each tab as it comes into view, is this possible. Excel 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Drop-down list to worksheets | Excel Discussion (Misc queries) | |||
Extracting data from multiple worksheets into a list | Excel Worksheet Functions | |||
print a list of worksheets and links | Excel Worksheet Functions | |||
List of Worksheets in a work book | Excel Worksheet Functions | |||
Consolidate list from multiple worksheets | Excel Worksheet Functions |