Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Selection
I'm using Excel 2003 and wondered if there Is a quick way of selecting a tab where at least 40 tabs exist in a workbook.
i appreciate that i can select the scroll arrows but this shows a pop up whereby i have to select more sheets and then i have to scroll through the lsit to select the tab i require. Is there a way to show all tabs in one window or some other quick option? Thanks in advance Mick --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Selection
Yes, there ist, but not out of the box.
1) Add a new sheet to hold a list of all tabs, name it 'Inhalt' or whatever you like 2) Add all the tab names to this list and create a hyper link to the respective tabs - the following macro is doing just that: Sub InhaltsverzeichnisErstellen() Dim i As Integer Dim j As Integer 'ActiveWorkbook.Sheets.Add Befo=Worksheets(1) 'ActiveSheet.Name = "Inhalt" Sheets("Inhalt").Select Cells.Select Selection.Clear Range("A1").Value = "Inhaltsverzeichnis" ActiveCell.Offset(2, 0).Select For i = 2 To ActiveWorkbook.Sheets.Count ActiveCell.Value = i - 1 If (Sheets(i).Type = xlWorksheet) Then ActiveCell.Offset(0, 2).Value = True Else ActiveCell.Offset(0, 2).Value = False End If ActiveCell.Offset(0, 1).Value = Sheets(i).Name ActiveCell.Offset(1, 0).Select Next i Range("B3").Activate For j = 2 To i If (ActiveCell.Offset(0, 1).Value = "Wahr") Then ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:= _ "'" + ActiveCell.Value & "'!A1", TextToDisplay:=ActiveCell.Value End If ActiveCell.Offset(0, 1).Value = "" ActiveCell.Offset(1, 0).Select Next j End Sub This can be topped by assigning key F12 to jump to the tab 'Inhalt'. "MickkyB" wrote: I'm using Excel 2003 and wondered if there Is a quick way of selecting a tab where at least 40 tabs exist in a workbook. i appreciate that i can select the scroll arrows but this shows a pop up whereby i have to select more sheets and then i have to scroll through the lsit to select the tab i require. Is there a way to show all tabs in one window or some other quick option? Thanks in advance Mick --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made easy =--------- -= Get GrabIt for free from http://www.shemes.com/ =- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tab Selection
this generates a userform from which you can select your tab........
i have it in my personal.xls file and attached it to a toolbar button so i have it available for any workbook........... =========================== Option Explicit Sub BrowseSheets() 'Bob Phillips' code 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 Application.DisplayAlerts = True End Sub ========================== hope it helps! susan On Jul 18, 1:09*am, "MickkyB" wrote: I'm using Excel 2003 and wondered if there Is a quick way of selecting a tab where at least 40 tabs exist in a workbook. i appreciate that i can select the scroll arrows but this shows a pop up whereby i have to select *more sheets and then i have to scroll through the lsit to select the tab i require. Is there a way to show all tabs in one window or some other quick option? Thanks in advance Mick --------------= *Posted using GrabIt *=---------------- ------= *Binary Usenet downloading made easy =--------- -= *Get GrabIt for free fromhttp://www.shemes.com/*=- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
Combo Box selection only shows bound column info after selection made. | Excel Programming | |||
Object Type of a selection... counting rows in a selection | Excel Programming | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming |