Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Navigatng worksheets in a large Workbook
Is there a shortcut to go back and forth to worksheets that are used often
but not readily available. For exam;ple sheet 25,28,31etc. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Navigatng worksheets in a large Workbook
Hello, you could try right clicking on the arrows at the lower left hand side
of the sheet. This should show all sheets in your workbook, Regards, Nick. "Trying To Excel" wrote: Is there a shortcut to go back and forth to worksheets that are used often but not readily available. For exam;ple sheet 25,28,31etc. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Navigatng worksheets in a large Workbook
With a large 500 sheet workbook an index page has proved invaluable. On one sheet (called 'GoToSheet') links have been created to all 500 pages. EG =HYPERLINK("#Sheet1!A1",1) =HYPERLINK("#Sheet2!A1",2) =HYPERLINK("#Sheet3!A1",3) =HYPERLINK("#Sheet4!A1",4) =HYPERLINK("#Sheet5!A1",5) =HYPERLINK("#Sheet6!A1",6) and so on up to =HYPERLINK("#Sheet500!A1",500) A return link to the index page is put on each of the 500 sheets to both the index page and a summary =HYPERLINK("#Summary!A1","Summary") =HYPERLINK("#GoToSheet!A1","GoToSheet") To create the links, entered the various items in separate columns, incremented the sheet and description columns and then concatenated across all the columns to create the 500 links, copied the links and pasted into the 'GoToSheet' Sheet. Manually created the return links (for those who didn't like using F5) on Sheet1, copied and then pasted across all remaining 499 sheets at once. There are 'VBA' (http://www.exceltip.com/st/Selecting..._List/645.html) solutions which I tried, but I prefer the index page. -- kghexce ------------------------------------------------------------------------ kghexce's Profile: http://www.excelforum.com/member.php...o&userid=29804 View this thread: http://www.excelforum.com/showthread...hreadid=495115 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Navigatng worksheets in a large Workbook
How about a toolbar that you can use with any workbook?
If you want to try one, start a new workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side. Paste this code there. Option Explicit Sub auto_close() On Error Resume Next Application.CommandBars("MyNavigator").Delete On Error GoTo 0 End Sub Sub auto_open() Dim cb As CommandBar Dim ctrl As CommandBarControl Dim wks As Worksheet On Error Resume Next Application.CommandBars("MyNavigator").Delete On Error GoTo 0 Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True) With cb .Visible = True Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True) With ctrl .Style = msoButtonCaption .Caption = "Refresh Worksheet List" .OnAction = ThisWorkbook.Name & "!refreshthesheets" End With Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True) With ctrl .Width = 300 .AddItem "Click Refresh First" .OnAction = ThisWorkbook.Name & "!changethesheet" .Tag = "__wksnames__" End With End With End Sub Sub ChangeTheSheet() Dim myWksName As String Dim wks As Worksheet With Application.CommandBars.ActionControl If .ListIndex = 0 Then MsgBox "Please select an existing sheet" Exit Sub Else myWksName = .List(.ListIndex) End If End With Set wks = Nothing On Error Resume Next Set wks = Worksheets(myWksName) On Error GoTo 0 If wks Is Nothing Then Call RefreshTheSheets MsgBox "Please try again" Else wks.Select End If End Sub Sub RefreshTheSheets() Dim ctrl As CommandBarControl Dim wks As Worksheet Set ctrl = Application.CommandBars("myNavigator") _ .FindControl(Tag:="__wksnames__") ctrl.Clear For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then ctrl.AddItem wks.Name End If Next wks End Sub Now back to excel and file|saveas choose save as type: Microsoft Office Excel Add-in (*.xla) at the bottom of that dropdown. Now close excel and reopen it. Turn on the addin. tools|addins look for that workbookname you just created and put a check mark there. You should see a toolbar that you can position where you want. If you swap workbooks, just click the other button to get a fresh list of worksheet names in the dropdown. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Trying To Excel wrote: Is there a shortcut to go back and forth to worksheets that are used often but not readily available. For exam;ple sheet 25,28,31etc. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Navigatng worksheets in a large Workbook
One more 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 Excel MVP On Tue, 20 Dec 2005 22:59:02 -0800, Trying To Excel wrote: Is there a shortcut to go back and forth to worksheets that are used often but not readily available. For exam;ple sheet 25,28,31etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting all worksheets of a workbook | Excel Discussion (Misc queries) | |||
Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
Appending external worksheets into existing workbook? | Links and Linking in Excel | |||
How do I build a workbook from the worksheets another workbook? | Excel Discussion (Misc queries) | |||
how do I keep a large workbook from losing all formatting upon cl. | Setting up and Configuration of Excel |