Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
Hi!
I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
Perhaps the easiest way is to create Hyperlinks to cell A1 of each sheet.
From the Excel main menu: <insert<hyperlink Click the [Place in this document] button Select the sheet (in the Cell Reference structure) Change the text to display. Click [OK] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Sam" wrote: Hi! I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
There is vba code here, by David McRitchie, if you want to do it yourself... http://www.mvps.org/dmcritchie/excel/buildtoc.htm -0r- you could find the XL Extras add-in at my website and give it a try... http://www.realezsites.com/bus/primitivesoftware (it also sorts sheets and does other stuff) -- Jim Cone San Francisco, USA "Sam" wrote in message Hi! I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
Off topic...
I've been meaning to tell you this for a while... You have one of the best designed websites I've ever seen. No clutter, no junk, no gaudy graphics, no BS. Excellent job! -- Biff Microsoft Excel MVP "Jim Cone" wrote in message ... There is vba code here, by David McRitchie, if you want to do it yourself... http://www.mvps.org/dmcritchie/excel/buildtoc.htm -0r- you could find the XL Extras add-in at my website and give it a try... http://www.realezsites.com/bus/primitivesoftware (it also sorts sheets and does other stuff) -- Jim Cone San Francisco, USA "Sam" wrote in message Hi! I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
Biff, Thank you for the kind words. I have to share credit with Chuck Roberts "CLR" who was a big help to me in getting the thing put together. Also, congratulations to you for your well deserved recognition. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "T. Valko" wrote in message Off topic... I've been meaning to tell you this for a while... You have one of the best designed websites I've ever seen. No clutter, no junk, no gaudy graphics, no BS. Excellent job! -- Biff Microsoft Excel MVP "Jim Cone" wrote in message There is vba code here, by David McRitchie, if you want to do it yourself... http://www.mvps.org/dmcritchie/excel/buildtoc.htm -0r- you could find the XL Extras add-in at my website and give it a try... http://www.realezsites.com/bus/primitivesoftware (it also sorts sheets and does other stuff) -- Jim Cone San Francisco, USA "Sam" wrote in message Hi! I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
You're very welcome!
-- Biff Microsoft Excel MVP "Jim Cone" wrote in message ... Biff, Thank you for the kind words. I have to share credit with Chuck Roberts "CLR" who was a big help to me in getting the thing put together. Also, congratulations to you for your well deserved recognition. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "T. Valko" wrote in message Off topic... I've been meaning to tell you this for a while... You have one of the best designed websites I've ever seen. No clutter, no junk, no gaudy graphics, no BS. Excellent job! -- Biff Microsoft Excel MVP "Jim Cone" wrote in message There is vba code here, by David McRitchie, if you want to do it yourself... http://www.mvps.org/dmcritchie/excel/buildtoc.htm -0r- you could find the XL Extras add-in at my website and give it a try... http://www.realezsites.com/bus/primitivesoftware (it also sorts sheets and does other stuff) -- Jim Cone San Francisco, USA "Sam" wrote in message Hi! I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
Sam
I prefer Bob Phillips' browsesheet code to go to any sheet in the workbook so you don't have to keep going back to the TOC sheet. 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 NOTE: Gary Brown revised this code to cover hidden sheets and chart sheets and other potential problems. See this thread for that revised code. http://snipurl.com/1l8o4 I would make one more change to Gary's code. Const nWidth As Long = 8 'width of each letter Change to 10 or sheetnames < 4 chars will be incomplete Gord Dibben MS Excel MVP On Tue, 3 Jul 2007 18:24:01 -0700, Sam wrote: Hi! I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I create a first page index of worksheets in my workbook?
a real easy way to do this would be to use an excel addin called asap
utilities. it can do other neat stuff with just a click or two... its a free download that has made life easier. "Sam" wrote: Hi! I've a workbook with over 40 worksheets, and would like to create a list(index) on the first worksheet, so that (a) any user can see all the worksheets in the workbook, and (b) perhaps if I click on the worksheet name, I can go to that worksheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create New Workbook from Worksheets in One Workbook | Excel Discussion (Misc queries) | |||
How do I create an index or TOC for worksheets in an excel workboo | Excel Discussion (Misc queries) | |||
How do you create page numbers on excel worksheets? | Excel Worksheet Functions | |||
How do I create multiple worksheets from Page Pivots | Excel Worksheet Functions | |||
Print Workbook Without Page Breaks Between Worksheets | Excel Worksheet Functions |