В определенных случаях когда книга Excel содержит большое количество листов выбор нужного может быть несколько затруднителен.
О том, как оптимизировать навигацию по книге и создать ее содержание обсудим сегодня в обзоре.
📢 Файл с примером размещен в конце статьи 🔽
Рассмотренный прием будет полезен, если нужно быстро перемещаться между листами в книге Excel с множеством листов.
▶️ Подготовка листа "Содержание"
Для целей данного примера создадим несколько листов в книге первый из которых определим как лист с содержанием:
Выпадающий список с выбором нужного листа будем размещать в ячейке А4, а список всех листов с назначенными гиперссылками разместим в столбце В (начиная с В4).
▶️ Создание выпадающего списка и перечня листов
Рассмотренный ниже макрос создает выпадающий список в ячейке A4 с наименованиями листов активной книги Excel.
Кроме того, в столбце B начиная с строки 4, для каждого листа добавляются гиперссылки. При выборе листа из выпадающего списка, пользователь может щелкнуть на гиперссылке, чтобы быстро перейти на выбранный лист.
⚙️ Краткое описание:
- Объявляем переменные, включая массив arrSheetNames, в котором будут храниться имена листов.
- Проходим циклом по всем листам активной книги, и имена листов добавляются в массив arrSheetNames.
- Удаляем предыдущую валидацию из ячейки A4 и добавляем новую с созданным списком.
- Для каждого листа создаем гиперссылки в столбце B, начиная с строки 4.
Гиперссылки настроены так, чтобы при щелчке на них осуществлялся переход на ячейку A4 выбранного листа. В гиперссылке отображается текст "Перейти на [Имя листа]":
Если число листов в книге изменится для актуализации справочника достаточно будет нажать на кнопку "ЗАПОЛНИТЬ".
📝 Примечание автора: Чтобы навигация была действительно удобной в качестве конечной цели определим не только создание списка с гиперссылками, но возможность перехода перехода на нужный лист при выборе его из выпадающего списка.
▶️ Переход на нужный лист при выборе из выпадающего списка
Для решения поставленной задачи применим событие Worksheet.Change:
⚙️ Краткое описание
- Проверяем, было ли изменено содержимое ячейки A4 (Target.Address = "$A$4").
- Если изменение произошло в ячейке A4, то запоминается выбранное в ней имя листа (selectedSheet = Target.Value).
- Далее, проверяется существование листа с указанным именем.
- Если лист существует, макрос активирует его командой ws.Activate.
🔔 Важно! Макрос выполняется при изменении данных на листе, на котором он размещен. В данном случае, при изменении в ячейки A4.
Когда содержимое этой ячейки изменяется, макрос проверяет, существует ли лист с именем, указанным в A4, и если да, то активирует этот лист.
▶️ Возврат на лист справочника
Чтобы у пользователя была возможность вернуться на стартовый лист следует использовать событие Workbook_SheetSelectionChange в модуле ThisWorkbook:
Данный подход позволит будет активировать первый лист как только выбрана целевая ячейка.
📝 Примечание автора: Свойство address определяет положение целевого объекта (в данном случае это ячейка "$A$1"), но Вы можете назначить в качестве целевой ячейки любую другую (или указать диапазон). Главное, чтобы адрес был указан заглавными буквами как абсолютная ссылка .
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽
