Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
View All Sheet Names in Workbook at Once
I have a workbook that has 30 to 40 sheets. I know there is no way to
display all sheet names at once, is there some way to have a form pop up so the end user can select a sheet from a list? Thanks, -- Lincoln |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
View All Sheet Names in Workbook at Once
Hi Lincoln Right click on the arrows on the left of the first sheet tab -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lincoln" wrote in message ... I have a workbook that has 30 to 40 sheets. I know there is no way to display all sheet names at once, is there some way to have a form pop up so the end user can select a sheet from a list? Thanks, -- Lincoln |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
View All Sheet Names in Workbook at Once
Right-click on the navigation arrows at bottom left will give a list of 15
sheets and "more". If you want to see all names and select from a list use Bob Phillips' browsesheets macro. 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, 7 May 2007 12:35:00 -0700, Lincoln wrote: I have a workbook that has 30 to 40 sheets. I know there is no way to display all sheet names at once, is there some way to have a form pop up so the end user can select a sheet from a list? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Incremental sheet names in a workbook | Excel Worksheet Functions | |||
View Custom View with Sheet Protection | New Users to Excel | |||
how do you make a summary page showing the workbook name with the excel sheet names | New Users to Excel | |||
Password Required to View\edit a Sheet in a Workbook with Many She | Excel Discussion (Misc queries) | |||
How do I display list of tab names used in a workbook on a sheet | Excel Worksheet Functions |