Quickly Tab Selection
in [Programming]
|
Prev: listbox remove
Next: Tab Selection
From: Mickky on 18 Jul 2008 00:58 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
From: H�ctor Miguel on 18 Jul 2008 01:12 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.
From: Gary Keramidas on 18 Jul 2008 01:43 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" <mickky(a)ntlworld.com> wrote in message news:%23g2LeLJ6IHA.1468(a)TK2MSFTNGP05.phx.gbl... >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
From: kounoike on 18 Jul 2008 02:58 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" <mickky(a)ntlworld.com> wrote in message news:%23g2LeLJ6IHA.1468(a)TK2MSFTNGP05.phx.gbl... >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
From: Dave on 18 Jul 2008 04:33
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" <mickky(a)ntlworld.com> wrote in message > news:%23g2LeLJ6IHA.1468(a)TK2MSFTNGP05.phx.gbl... > >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 > > |