Показать только лист, выбранный в оглавлении, остальные скрыть.

[Alex_ST]

Написал макрос, который при клике по гиперссылкам листа ОГЛАВЛЕНИЕ делает видимым выбранный лист и скрывает все остальные. При большом числе листов очень удобно получается не выбранные листы прятать.
Этот-то макрос работает.
Но я решил усовершенствовать - сделать ещё один макрос, который будет в книге сам создавать лист ОГЛАВЛЕНИЕ с гиперссылками на её листы и прописывать на этот лист коды обработки событий.

Народ, знатоки!
Гляньте, пожалуйста те, кто умеет работать с VBProject.VBComponents

Я в макрос Sub ОГЛАВЛЕНИЕ_КНИГИ , формирующий гиперссылки-оглавление листов, пытаюсь внести доработку: если лист с именем ОГЛАВЛЕНИЕ в книге не существует, то такой лист создаётся перед первым листом, а потом на него должен прописаться код VBA для обработки событий.

Если в примере нажать на кнопку "Создать ОГЛАВЛЕНИЕ", то, т.к. имеющийся лист у меня переименован в _ОГЛАВЛЕНИЕ, вызванный из-за этого макрос Sub Create_shContent создаст новый лист с именем ОГЛАВЛЕНИЕ и запишет на него код обработки событий.

При пошаговом проходе Sub Create_shContent , вроде бы работает. Лист создаётся, код на него записывается. Но когда запускаю его, то Ёксель "умирает", предлагая перед смертью отправить сообщение об ошибке мелко-мягким

Может, я неправильно обращаюсь к VBComponents(ActiveSheet.CodeName) - кодовому модулю активного листа?
Или просто пора полный ребут компу устраивать?

[Дмитрий Щербаков(The_Prist)]

Алексей, я чуть изменил код. Вынес в отдельные процедуры сам код(будет работать даже из надстройки, если основной код в ней).
А еще изменил в той части, где прописывались сами коды в модуль листа - советую просмотреть изменения и принять к сведению. Возможно тот факт, что Вы обращались к объекту компонента VBA именно тем способом и было причиной ошибки.
Так же я подправил, чтобы Ваш записываемый код вдруг не стал записываться перед строками деклараций(Option Explicit и прочее).

[Alex_ST]

Дмитрий, спасибо.
Буду разбираться.
Но первая же попытка создать с вашим кодом новый лист ОГЛАВЛЕНИЕ привела опять к смерти Ёкселя.
Надо полностью перегружаться. Сейчас не могу. Попробую, наверное, дома.

[Alex_ST]

За CountOfDeclarationLines + 1 большое спасибо. Не знал как обойти декларации.

А в Sub Go_to_Hyp ошибочка вышла : не (Target As Range), а (Target As Hyperlink)
Ну, это и понятно — привычка.

[Alex_ST]

А всё-таки без выноса в отдельный модуль подпрограмм для скрывания/отображения листов получится проще, т.к. без этого модуля созданное в новой книге оглавление работать не будет. А значит придётся и этот модуль программно в новую книгу прописывать...

Поэтому я решил всё-таки прописывать код в создаваемый лист полностью без вызова подпрограмм. Но, естественно, с учётом замечаний Дмитрия.
Кстати, дома Ёксель дохнуть на макросе перестал.
А модуль Create_Content теперь можно вынести в personal.xls или в надстройку и прицепить кнопочку на панели инструментов к макросу Sub ОГЛАВЛЕНИЕ_КНИГИ

Надо бы ещё добавить сортировку листов. Её код есть в модуле, но не используется, т.к.  что-то как-то хитро глючит сортировка при скрытых листах.

[Alex_ST]

Тут я взялся дополировывать макрос для создания  в активной книге листа "<<ОГЛАВЛЕНИЕ>>" с гиперссылками на все её листы.
При выборе гиперссылки на лист из оглавления выбранный лист и "<<ОГЛАВЛЕНИЕ>>" остаются видимыми, а все другие листы скрываются.
При активизации листа "<<ОГЛАВЛЕНИЕ>>" все остальные листы книги скрываются.
На листе "<<ОГЛАВЛЕНИЕ>>" добавлен пункт-гиперссылка "Все листы", позволяющий при его активизации сделать видимыми все листы книги.

При необходимости название создаваемого листа "<<ОГЛАВЛЕНИЕ>>" и адрес ячейки начала оглавления на нём вводятся в первых строках кода процедуры СОЗДАТЬ_ОГЛАВЛЕНИЕ

Код может функционировать самостоятельно как надстройка - создавать листы "<<ОГЛАВЛЕНИЕ>>" в любой активной книге.

Вроде, всё работает как надо.
Только почему-то приходится два раза запускать макрос СОЗДАТЬ_ОГЛАВЛЕНИЕ для создания оглавления в новой книге:
- за первый запуск создаётся лист "<<ОГЛАВЛЕНИЕ>>" и код обработки событий
- за второй - прописываются гиперссылки.

НИЧЕГО НЕ ПОНИМАЮ! Пол-дня бьюсь...

[Дмитрий Щербаков(The_Prist)]

Алексей. Ну ведь ячейка для создания списка какая?

Код Выделить Развернуть

With ActiveWorkbook.Worksheets(shContent)
Set rCell = .Range(sStartCell).Offset(iSht.Index - 1, 0)

Т.е. если листа еще нет, то у нас родительского объекта для With тоже нет. Т.к. ошибка у Вас игнорируется(On Error Resume Next), то Вы и не обнаружили столь досадное недоразумение
Во вложении решение.

[Alex_ST]

Что-то у меня комп конкретно заглючил - при запуске и вашего и моего доработанного макроса умирает Ёксель и предлагает пожаловаться в микрософт...
Даже перезагрузка не помогла.
Я свой вариант доделал с вашими указаниями.
Если не затруднит, можете проверить на своём компе?

[Дмитрий Щербаков(The_Prist)]

Работает.
Но я на Планете отписал свое мнение, по поводу намеренного игнорирования подобных ошибок.

[Alex_ST]

Дмитрий, а у вас на компе в вашем файле Оглавление_автомат.xls код на листе создаётся?
У меня - нет. Лист с гиперссылками создаётся, все листы кроме оглавления скрываются. Но кода на листе нет и поэтому спрятанные листы по гиперссылкам не показываются...

[Дмитрий Щербаков(The_Prist)]

Дмитрий, а у вас на компе в вашем файле Оглавление_автомат.xls код на листе создаётся?
У меня - нет. Лист с гиперссылками создаётся, все листы кроме оглавления скрываются. Но кода на листе нет и поэтому спрятанные листы по гиперссылкам не показываются...

У меня все работает. Код создается и все ссылки рабочие.

[Alex_ST]

У меня вдруг тоже ваш пример заработал...
...
Правда, код прописался только со второго запуска макроса...

Вот, посмотрите: я внедрил предлагаемый вами метод проверки наличия листа непосредственно в макрос.
При первом запуске макроса создаётся лист с гиперссылками, но без кода. При втором запуске на лист прописывается код... Бред какой-то. Такое впечатление, что коллекция Worksheets после Add не сразу обновляется.
Самое интересное, что если лист с работающим оглавлением удалить, а книгу сохранить, но не закрывать, то новый лист при запуске макроса создаётся уже нормально - со ссылками и с макросом.
А вот если лист удалить, книгу сохранить и закрыть, то после открытия книги макрос полностью отработает только со 2-го раза...

[Дмитрий Щербаков(The_Prist)]

Алексей, последний файл тоже нормально работает. Посмотрите, нет ли у Вас перехвата событий при выполеннии макроса, нет ли инициализации каких форм, переменных, процедур. Если никакие посторонние коды не вклиниваются в процесс - виноват Excel. Может стоит переустановить?

[Alex_ST]

Добавил

Код Выделить Развернуть

Application.EnableEvents = False ... Application.EnableEvents = True
Вроде, сначала заработало. Но раз на 3-й опять глюкнуло. Наверное, надо и в самом деле Ёксель переставить...

[Alex_ST]

Сейчас попробовал свою последнюю версию на работе: Ёксель умирает при попытке прописать код в модуль созданного листа...
А дома всё в порядке. Хотя дома Офис - его близнец, поставленный с того же дистрибутива.
Буду, наверное, Ёксель переставлять...