New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Scroll Tab Into View in Large Workbooks

Free Excel Macros

Scroll Tab Into View in Large Workbooks

Postby SamT » Tue Mar 26, 2013 10:32 pm

So you have a lot of sheets in your Workbook. So many that you can not see all the Tabs at one time. These Procedures are kept in the "Sheet Index" Tab's Code Page. By clicking on a sheet name in the Index list, they will activate the selected sheet and move the Sheet index tab next to the activated sheet.

The list of sheets names is alphabetically sorted and is always accurate, even if you rename, add, or delete sheets.

In the procedures below, you will notice several references to ShtNdx. This is the CodeName I gave to Tab "Sheet Index." This allows you to change the Tab Name as you prefer. If you just copy the code below into a sheet, you must change the CodeName of that Tab. In the Code samples, you will also note that ShtNdex is not used like Tab names are. Tab names must be referenced in VBA like:
Code: Select all
With Sheets("TabName")
   Do Something


CodeNames must never be referenced that way. Always use the bare CodeName:
Code: Select all
With CodeName
   Do something


When using the Sheets Collection, Sheets with custom CodeNames have lower index numbers than sheets with standard CodeNames like "SheetN" where N is a number.

BTW, Workbooks have CodeNames, too; But that is a tale for another time.

Be sure and view the Read Me Sheet for details and instructions

This Code has been tested in Excel 2002, and 2007 on Windows XP.

For Code Geeks, the Code is in the four code samples following.
Code: Select all
Option Explicit

Private Sub Worksheet_Activate()
  'An interactive index that refreshes each time it's viewed
  'so that it is always current.
  '
  'The Selection Change Sub below keeps this sheet next to
  'the Sheet chosen when the User clicks on a Sheet name
  'in the Sheet Index List.
 

  'ShtNdx is the CodeName of "Sheet Index"
  Const TopRowOfList As Long = 4 'Set As Desired
  Dim i As Long 'Common index variable
  Dim r As Long 'Row Counter for Index List
      r = TopRowOfList 'Set here because it's used to clear the list
   
  Application.ScreenUpdating = False

''''Clear the existing list
  If LastRow >= TopRowOfList Then ShtNdx.Range("A" & CStr(TopRowOfList) & _
                                      ":A" & CStr(LastRow)).ClearContents

''''Create the List of Sheet Names
  With ThisWorkbook
    For i = 1 To .Sheets.Count
      If .Sheets(i) Is ShtNdx Then
        'Don't list This sheet
        'Don't increment Row counter
        GoTo NextLoop
      Else
        ShtNdx.Cells(r, 1) = .Sheets(i).Name
        r = r + 1 'Increment Row Counter
      End If
NextLoop:
    Next i
  End With

''''Sort the list alphabetically
  Range("A4:A" & CStr(LastRow)).Sort _
    Key1:=Range("A1"), _
    Header:=xlNo
   
  Application.ScreenUpdating = True
End Sub


Code: Select all
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False

''''Check criteria for running this sub
    'Is only one Cell selected
  If Target.Count <> 1 Then Exit Sub
    'Is there a List
  If LastRow < 4 Then Exit Sub
    'Is the selection in the List
  If Intersect(Target, Range("A4:A" & CStr(LastRow))) Is Nothing Then Exit Sub
 
 
''''Move the Index Sheet, Activate the chosen Sheet, and Select "A1" _
    on the chosen Sheet.
  ShtNdx.Move Before:=Sheets(Target.Value)
  Sheets(Target.Value).Activate
  ActiveSheet.Range("A1").Select
 
Application.ScreenUpdating = True
End Sub


Code: Select all
Private Function LastRow() As Long
'Custom for this module.
'Always looks for the last non-empty cell in Column "A."
  LastRow = Cells(Rows.Count, 1).End(xlUp).Row
End Function


Code: Select all
Private Sub Workbook_SheetActivate(ByVal Sht As Object)
'Moves the Sheet Index when any other tab is clicked.

  With Application
    .EnableEvents = False
    .ScreenUpdating = False
  End With
   
    ShtNdx.Move Before:=Sht
    Sht.Activate
 
    With Application
    .EnableEvents = True
    .ScreenUpdating = True
  End With

End Sub
You do not have the required permissions to view the files attached to this post.
SamT
Private Function IsTrue()
If Not IsTrue Then IsTrue = Not IsTrue
End Function
SamT
Excel Junkie
 
Posts: 288
Joined: Feb 28, 2013
Location: Missouri, USA
Reputation: 24
Excel Version: 97+XP+2007+2013

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 24 guests

cron