Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 7th 07, 08:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 2
Default 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   Report Post  
Old May 7th 07, 08:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,123
Default 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   Report Post  
Old May 7th 07, 11:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Incremental sheet names in a workbook Sinner Excel Worksheet Functions 2 March 30th 07 02:37 AM
View Custom View with Sheet Protection John H[_2_] New Users to Excel 1 February 16th 07 06:54 PM
how do you make a summary page showing the workbook name with the excel sheet names carole New Users to Excel 1 May 22nd 06 08:31 PM
Password Required to View\edit a Sheet in a Workbook with Many She Ed Excel Discussion (Misc queries) 1 March 27th 06 09:31 PM
How do I display list of tab names used in a workbook on a sheet tmottur Excel Worksheet Functions 2 December 1st 05 10:42 PM


All times are GMT +1. The time now is 06:58 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017