Трюк №7. Создание указателя листов в книге Excel

Если вы потратили много времени, работая в книге с множеством листов, то знаете, как трудно бывает найти определенный лист. Страница указателя, связанная со всеми листами, — это необходимая вещь для упрощения навигации. Страница указателя позволит быстро и просто перемещаться по книге одним щелчком мыши, и вы без всяческой неразберихи будете попадать куда нужно. Указатель можно создать двумя способами.

Возможно, вам покажется привлекательной идея создать указатель вручную. Создайте новый лист, назовите его Index или подобным именем, введите имена всех остальных листов и свяжите их гиперссылками с соответствующими листами, выбрав команду Вставка → Гиперссылка (Insert → Hyperlink) или нажав сочетание клавиш Ctrl/Apple+K. Хотя этого способа, вероятно, достаточно для книг с небольшим количеством листов, которые будут меняться нечасто, можно все же запутаться, поддерживая указатель вручную.

Следующий код автоматически создаст указатель с гиперссылками, щелкая которые, можно будет переходить ко всем листам в книге. Указатель создается заново каждый раз при активации содержащего его листа. Этот код нужно поместить в частный модуль для объекта Sheet. Вставьте в вашу книгу новый лист и назовите его удобным именем — например, Index. Правой кнопкой мыши щелкните ярлычок листа с указателем и в контекстном меню выберите команду Исходный текст (View Code). Введите код Visual Basic (Сервис → Макрос → Редактор Visual Basic (Tools → Macro → Visual Basic Editor) или Alt/Option+Fll), приведенный в листинге 1.10.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

// Листинг 1.10
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim lCount As Long
lCount = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
 
    For Each wSheet In Worksheets
     If wSheet.Name <> Me.Name Then
      lCount = lCount + 1
      With wSheet
          .Range("A1").Name = "Start" & wSheet.Index
          .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
            "Index", TextToDisplay:="Назад к оглавлению"
      End With
         Me.Hyperlinks.Add Anchor:=Me.Cells(lCount, 1), Address:="", SubAddress:= _
            "Start" & wSheet.Index, TextToDisplay:=wSheet.Name
     End If
    Next wSheet
End Sub

Нажмите сочетание клавиш Alt/Apple+Q, чтобы вернуться обратно к рабочей книге, и сохраните изменения. Обратите внимание в коде, что ячейке А1 на каждом листе присваивается имя Start (таким образом вы присваиваете имя ячейке или диапазону ячеек в Excel), а также уникальное целое число, представляющее номер листа в указателе. Это гарантирует, что у ячейки А1 каждого листа будет уникальное имя. Если у ячейки А1 на вашем листе уже есть имя, необходимо каждое упоминание ячейки А1 в коде изменить на что-то более подходящее — например, любую неиспользуемую ячейку листа.
[stextbox id=»alert»]Помните, что если выбрать команду Файл → Свойства → Документ (File → Properties → Summary) и ввести в качестве базы гиперссылки URL, возможно, указатель, созданный предыдущим кодом, не будет работать. База гиперссылки — это путь или URL, который удобно использовать для всех присутствующих в текущем документе гиперссылок с тем же базовым адресом.[/stextbox]
Другой, более удобный способ конструирования указателя — добавить ссылку к списку листов как элемент стандартного контекстного меню кнопок прокрутки ярлычков (рис. 1.11).

Рис. 1.11. Контекстное меню кнопок прокрутки ярлычков

Чтобы контекстное меню кнопки прокрутки ярлычков открывалось по щелчку правой кнопки мыши на любой ячейке, введите код в VBE, приведенный в листинге 1.11.

1
2
3
4
5
6
7
8
9
10
11
12
13

// Листинг 1.11
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Sheet Index").Delete
    On Error GoTo 0
        Set cCont = Application.CommandBars("Cell").Controls.Add _
                        (Type:=msoControlButton, Temporary:=True)
        With cCont
          .Caption = "Sheet Index"
             .OnAction = "IndexCode"
        End With
End Sub

Затем необходимо вставить стандартный модуль, который будет содержать макрос IndexCode, вызываемый предыдущим кодом, когда пользователь щелкает ячейку правой кнопкой мыши. Обязательно используйте стандартный модуль, так как помещение кода в тот же модуль, где находится Workbook_SheetBeforeRightClick, будет означать, что Excel не сможет найти макрос с именем IndexCode. Выберите команду Insert → Module и введите код из листинга 1.12.

1
2
3
4

// Листинг 1.12
Sub IndexCode()
 Application.CommandBars("workbook Tabs").ShowPopup
End Sub