Quickly Tab Selection
I am using Excel 2003 and i wondered if there was a way to quickly select
the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs...i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? Thanks in advance Mick |
Quickly Tab Selection
hi, Mickky !
I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs... i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? not directly (I guess), but you could assign a shortcut to a macro like... Sub myShList() With Application.CommandBars("workbook tabs").Controls(16) If Right(.Caption, 3) = "..." Then .Execute Else .Parent.ShowPopup End With End Sub hth, hector. |
Quickly Tab Selection
here's an option.
make your self a form with a command button in the lower right corner and then paste this code in form code module Option Explicit Private Sub CommandButton1_Click() Dim cntrl As control For Each cntrl In UserForm1.Controls If cntrl.Value = True Then Worksheets(cntrl.Caption).Activate unload me End If Next End Sub Private Sub UserForm_Initialize() Dim i As Long Dim cntrl As control Dim ws As Worksheet Dim x As Long i = 1 x = 30 For i = 1 To Worksheets.Count Set cntrl = Me.Controls.Add("Forms.optionbutton.1") With cntrl .Visible = True .Width = 50 .Height = 12.5 .Left = 5 .Top = x .Caption = Worksheets(i).Name End With x = x + 15 Next Me.Height = 25 * i End Sub -- Gary "Mickky" wrote in message ... I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs...i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? Thanks in advance Mick |
Quickly Tab Selection
One way
Insert worksheet, move this sheet to the most left position and name it as you like, for example index. open this worksheet module and paste codes below in this sheet's module. every time you get back to this worksheet, you could see list of sheet's name. if you select the name of sheet you want to move, you would move to selected sheet. Private Sub Worksheet_Activate() Dim i As Long, j As Long Dim sh Application.ScreenUpdating = False Cells(1, 1).Select i = 1 j = 1 Cells.ClearContents For Each sh In Sheets Cells(i, j) = sh.Name i = i + 1 If i 10 Then Columns(j).AutoFit i = 1 j = j + 1 End If Next End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Sheets(Target.Value).Select End Sub keiji "Mickky" wrote in message ... I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs...i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? Thanks in advance Mick |
Quickly Tab Selection
Just an idea,
Use as index sheet, as suggested, but use hyperlinks instead of code. Dave. "kounoike" wrote: One way Insert worksheet, move this sheet to the most left position and name it as you like, for example index. open this worksheet module and paste codes below in this sheet's module. every time you get back to this worksheet, you could see list of sheet's name. if you select the name of sheet you want to move, you would move to selected sheet. Private Sub Worksheet_Activate() Dim i As Long, j As Long Dim sh Application.ScreenUpdating = False Cells(1, 1).Select i = 1 j = 1 Cells.ClearContents For Each sh In Sheets Cells(i, j) = sh.Name i = i + 1 If i 10 Then Columns(j).AutoFit i = 1 j = j + 1 End If Next End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Sheets(Target.Value).Select End Sub keiji "Mickky" wrote in message ... I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs...i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? Thanks in advance Mick |
Quickly Tab Selection
On Jul 18, 5:58*am, "Mickky" wrote:
I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs...i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? Thanks in advance Mick Define the sheet names, e.g. Sheet1 then "Sheet1".Select Works perfectly for me |
Quickly Tab Selection
Hi Mickky,
I tried right clicking on the worksheet arrows situated to the left of the tabs. Came up with a list. Also if there were too many to list immediately, (I think 15 is the max) there is a "more sheets..." selection which brings up an "Activate" box, from which you can pick your sheet. Beege Mickky wrote: I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs...i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? Thanks in advance Mick |
Quickly Tab Selection
Hi Mickky
See http://www.contextures.com/xlToolbar01.html For code you can use this Sub SheetList_RDB() Dim ws Dim I As Long On Error Resume Next ActiveSheet.Select If Err.Number 0 Then MsgBox "There is no workbook open" Err.Clear On Error GoTo 0 Else For Each ws In ActiveWorkbook.Sheets If ws.Visible = True Then I = I + 1 Next ws If I 16 Then Application.CommandBars("Workbook Tabs").Controls(16).Execute Else Application.CommandBars("Workbook Tabs").ShowPopup End If End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mickky" wrote in message ... I am using Excel 2003 and i wondered if there was a way to quickly select the tab i needed within a workbook where there are over 40 tabs. I appreciate i can right click on the scroll arrows but this opens up a box whereby i can view only a small seelction of the tabs...i then have to select more sheets and then scroll to slect the one i require. Is there a way that the entire list is viewable and therefore quicker? Thanks in advance Mick |
Quickly Tab Selection
In the past, I've used various "Table of Contents" add-ins that create
a new worksheet with a clickable link to all the tabs within the workbook. I quickly did a search and found this one: http://www.tushar-mehta.com/excel/so...Help/toc10.htm Although I've never used it before, I'm sure its fairly similar to what I've used in the past. I don't know if this is what you're asking about, but it does give you a view of all the tabs in the workbook, and easier navigation. |
All times are GMT +1. The time now is 03:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com