Развертывание или сворачивание групп листов на панели вкладок Excel

Содержание

Вопрос или проблема
Ответ или решение
Шаг 1: Создание управления на листе
Шаг 2: Настройка значений для комбинированного списка
Шаг 3: Код макроса
Шаг 4: Привязка макроса к комбинированному списку
Заключение

Вопрос или проблема

Существует ли способ сгруппировать листы в Excel 2010 так, чтобы не все вкладки были сразу видны? По сути, чтобы их можно было развернуть и свернуть по мере необходимости.

У меня есть несколько книг Excel с довольно большим количеством вкладок. И немного неудобно прокручивать, чтобы найти конкретный лист.

Вот что я придумал, модифицируйте как вам угодно. Это могло быть гораздо короче, но вы понимаете суть.

Вставьте комбобокс (на главном листе, у меня он называется “управление”), назначьте ему список и затем поместите этот макрос в Visual Basic. Группируйте по желанию, обязательно оставьте один лист всегда видимым и оставьте вариант, чтобы показать все.

Sub DropDown1_Change()

With ActiveSheet.Shapes(Application.Caller)
        Select Case .ControlFormat.ListIndex

        Case 1
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetVisible
            Sheet3.Visible = xlSheetVisible
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 2
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetVisible
            Sheet5.Visible = xlSheetVisible
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 3
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetVisible
            Sheet7.Visible = xlSheetVisible
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 4
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetVisible
            Sheet9.Visible = xlSheetVisible
            Sheet10.Visible = xlSheetHidden
            Sheet11.Visible = xlSheetHidden

        Case 5
            Sheets("Control").Visible = xlSheetVisible
            Sheet2.Visible = xlSheetHidden
            Sheet3.Visible = xlSheetHidden
            Sheet4.Visible = xlSheetHidden
            Sheet5.Visible = xlSheetHidden
            Sheet6.Visible = xlSheetHidden
            Sheet7.Visible = xlSheetHidden
            Sheet8.Visible = xlSheetHidden
            Sheet9.Visible = xlSheetHidden
            Sheet10.Visible = xlSheetVisible
            Sheet11.Visible = xlSheetVisible

        Case 6
        For Each ws In Worksheets
        ws.Visible = xlSheetVisible

        Next
        End Select

    End With
End Sub

Ответ или решение

В Excel 2010 нет встроенной функции для группировки листов, как в некоторых других приложениях. Однако вы можете использовать комбинированный список и макросы VBA для управления видимостью листов, что позволяет сворачивать и разворачивать группы листов по мере необходимости. Вот подробное руководство о том, как это сделать:

Шаг 1: Создание управления на листе

Откройте вашу книгу в Excel.
Создайте новый лист (например, назовите его "Управление").
Вставьте комбинированное поле (ComboBox):
- Перейдите на вкладку "Разработчик" (Developer).
- Нажмите "Вставить" (Insert) и выберите "Комбинированный список" (ComboBox) из группы "Элементы управления формы" (Form Controls).
- Нарисуйте комбинированный список на листе.

Шаг 2: Настройка значений для комбинированного списка

Щелкните правой кнопкой на комбинированном списке и выберите "Формат контроля" (Format Control).
В разделе "Контроль" (Control) задайте диапазон для "Исходного диапазона" (Input Range), который будет содержать названия групп листов. Например:
- Группа 1: Листы 1, 2, 3
- Группа 2: Листы 4, 5
- и так далее.

Шаг 3: Код макроса

Нажмите ALT + F11, чтобы открыть редактор Visual Basic for Applications (VBA).
Вставьте новый модуль:
- Щелкните правой кнопкой на "VBAProject" -> "Вставить" (Insert) -> "Модуль" (Module).
Скопируйте и вставьте следующий код:

Sub DropDown1_Change()
    With ActiveSheet.Shapes(Application.Caller)
        Select Case .ControlFormat.ListIndex
            Case 1
                Sheets("Управление").Visible = xlSheetVisible
                Sheet2.Visible = xlSheetVisible
                Sheet3.Visible = xlSheetVisible
                Sheet4.Visible = xlSheetHidden
                Sheet5.Visible = xlSheetHidden

            Case 2
                Sheets("Управление").Visible = xlSheetVisible
                Sheet4.Visible = xlSheetVisible
                Sheet5.Visible = xlSheetVisible
                Sheet2.Visible = xlSheetHidden
                Sheet3.Visible = xlSheetHidden

            ' Добавьте дополнительные случаи для других групп листов...

            Case 6
                For Each ws In Worksheets
                    ws.Visible = xlSheetVisible
                Next
        End Select
    End With
End Sub

Не забудьте адаптировать код в соответствии с вашей структурой листов.

Шаг 4: Привязка макроса к комбинированному списку

Нажмите правой кнопкой на комбинированный список и выберите "Назначить макрос" (Assign Macro).
Выберите DropDown1_Change и нажмите "ОК".

Заключение

Теперь, когда вы используете комбинированный список на листе "Управление", вы сможете скрывать и показывать группы листов по вашему выбору. Это значительно упростит навигацию по книге с большим количеством листов. Если вам нужно изменить видимые группы, просто измените код в макросе.