Кнопки, формы и запуск макроса

Макрос, который запускают через Alt+F8 из списка с именами вроде Модуль1.Макрос3, не будет использовать никто. Делаем кнопку, форму и автозапуск по событию.

Интерфейс макроса — это то, как пользователь его вызывает и что ему сообщает. Именно он превращает «скрипт для себя» в инструмент, которым пользуется весь отдел.

Зачем это нужно

Вы написали быстрый, надёжный, с человеческими сообщениями макрос. Теперь его надо отдать людям. Если инструкция начинается со слов «нажмите Alt+F8, найдите в списке…», ей не будут следовать. Человеку нужна кнопка, на которой написано, что она делает.

Вариантов запуска несколько, и выбирать надо по задаче:

Кнопка на листе

Порядок такой:

1. Включите вкладку Разработчик (Файл → Параметры → Настроить ленту → галочка «Разработчик»).
2. Разработчик → Вставить → в группе Элементы управления формы выберите первую иконку — Кнопка.
3. Нарисуйте кнопку мышью на листе. Excel сам откроет окно «Назначить макрос» — выберите нужный и нажмите ОК.
4. Щёлкните по кнопке правой клавишей → Изменить текст и напишите на ней глагол: «Обновить отчёт», а не «Кнопка 1».

В окне «Вставить» есть два набора: Элементы управления формы и Элементы ActiveX. Для 95% задач берите первый: такие кнопки проще, не «съезжают» при смене масштаба и не ломаются при пересылке файла между версиями Excel. ActiveX нужен, только если требуется менять внешний вид кнопки из кода.

Макрос под кнопку должен быть публичной процедурой Sub без аргументов и лежать в обычном модуле (в дереве проекта — папка Modules), а не в модуле листа:

' Обычный модуль (Module1), НЕ модуль листа
Public Sub ОбновитьОтчёт()
    On Error GoTo Ошибка
    Application.ScreenUpdating = False

    ' ... основная работа ...

Завершение:
    Application.ScreenUpdating = True
    MsgBox "Отчёт обновлён.", vbInformation
    Exit Sub

Ошибка:
    MsgBox "Обновить отчёт не удалось: " & Err.Description, vbExclamation
    Resume Завершение
End Sub

Классическая ловушка новичка: код написали в модуле листа (потому что туда сразу попадаешь двойным кликом), а потом в окне «Назначить макрос» его нет в списке. Excel показывает там только процедуры из обычных модулей. Лечится переносом кода: Вставка → Модуль, перенести текст туда.

И ещё: книгу с макросами надо сохранять как .xlsm («Книга Excel с поддержкой макросов»). Сохраните как обычный .xlsx — весь код молча пропадёт.

UserForm: спрашиваем параметры

Кнопка хороша, когда действие одно. Но чаще нужно уточнить: за какой месяц отчёт, по какому отделу, с каким порогом суммы. Собирать это через цепочку InputBox — мучение: пять окон подряд, нельзя вернуться назад, нельзя выбрать из списка. Для этого есть UserForm — собственное диалоговое окно.

Создаётся оно мышью: в редакторе VBA (Alt+F11) — Вставка → UserForm. Слева появится панель элементов, справа — окно свойств (F4). Перетащите на форму нужные элементы и в свойстве Name дайте каждому осмысленное имя — по нему вы будете обращаться к элементу из кода:

Форма живёт своей жизнью: у неё есть событие «я открылась» и у каждой кнопки — событие «по мне щёлкнули». Начинаем с заполнения списков при открытии:

' Код внутри модуля формы frmОтчёт
Private Sub UserForm_Initialize()
    Dim i As Long

    ' заполняем список месяцев
    For i = 1 To 12
        cboМесяц.AddItem Format(DateSerial(2024, i, 1), "mmmm")
    Next i
    cboМесяц.ListIndex = Month(Date) - 1        ' по умолчанию — текущий месяц

    ' список отделов
    cboОтдел.List = Array("Продажи", "Логистика", "Бухгалтерия")
    cboОтдел.ListIndex = 0

    chkТолькоОплаченные.Value = True
End Sub

UserForm_Initialize срабатывает один раз, когда форму показывают. Здесь мы наполняем выпадающие списки и ставим значения по умолчанию — чтобы пользователю в типичном случае оставалось просто нажать ОК.

Теперь кнопка ОК. Её задача: проверить ввод, спрятать форму и передать параметры в рабочий макрос.

Private Sub cmdОК_Click()
    ' проверяем ввод ДО того, как что-то делать
    If cboМесяц.ListIndex = -1 Then
        MsgBox "Выберите месяц.", vbExclamation
        cboМесяц.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(txtПорог.Value) Then
        MsgBox "Порог суммы должен быть числом.", vbExclamation
        txtПорог.SetFocus
        Exit Sub
    End If

    Me.Hide                                      ' прячем форму, но не выгружаем

    ПостроитьОтчёт cboМесяц.ListIndex + 1, _
                   cboОтдел.Value, _
                   CDbl(txtПорог.Value), _
                   chkТолькоОплаченные.Value

    Unload Me                                    ' теперь можно выгрузить
End Sub

Private Sub cmdОтмена_Click()
    Unload Me
End Sub

Ключевые моменты:

• Проверки в начале — те самые защитные условия из урока про ошибки. Пустой список или буквы вместо числа отлавливаем здесь, а не в глубине расчёта.
• Me внутри модуля формы — это сама форма. Me.Hide скрывает её с экрана, Unload Me — выгружает из памяти и стирает введённые значения. Поэтому сначала Hide, потом работа, потом Unload.
• Форма не должна сама строить отчёт. Её дело — собрать параметры и позвать ПостроитьОтчёт. Тогда тот же отчёт можно запустить и из другого места, и по расписанию, без всякой формы.
• Значения передаём аргументами. Это честнее и понятнее, чем прятать их в глобальные переменные.

Показывает форму крошечный макрос в обычном модуле — именно его мы и вешаем на кнопку листа:

' Обычный модуль: этот макрос вешаем на кнопку листа
Public Sub ПоказатьФормуОтчёта()
    frmОтчёт.Show
End Sub

' Сюда форма передаёт собранные параметры
Public Sub ПостроитьОтчёт(ByVal месяц As Long, _
                          ByVal отдел As String, _
                          ByVal порог As Double, _
                          ByVal толькоОплаченные As Boolean)
    ' ... собственно построение отчёта ...
    MsgBox "Отчёт за месяц " & месяц & ", отдел «" & отдел & "»", vbInformation
End Sub

По умолчанию форма модальная: пока она открыта, работать с листом нельзя. Обычно это то, что нужно. Если требуется оставить доступ к листу, показывайте её как frmОтчёт.Show vbModeless.

События листа: макрос без кнопки

Иногда действие вообще не надо запускать — оно должно происходить само. Заполнили ячейку в столбце A — в столбце B автоматически появилась дата и время. За это отвечают события: процедуры с зарезервированными именами, которые Excel вызывает сам.

Код события листа живёт в модуле листа (двойной клик по нужному листу в дереве проекта слева), а не в обычном модуле. Самое ходовое событие — Worksheet_Change, оно срабатывает после изменения ячеек пользователем:

' Код в модуле ЛИСТА (двойной клик по листу в дереве проекта)
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim изменённые As Range
    Dim ячейка As Range

    ' нас интересует только столбец A со второй строки
    Set изменённые = Intersect(Target, Me.Range("A2:A1000"))
    If изменённые Is Nothing Then Exit Sub

    On Error GoTo Ошибка
    Application.EnableEvents = False              ' иначе наши же записи вызовут событие снова

    For Each ячейка In изменённые
        If ячейка.Value = "" Then
            ячейка.Offset(0, 1).ClearContents
        Else
            ячейка.Offset(0, 1).Value = Now
            ячейка.Offset(0, 1).NumberFormat = "dd.mm.yyyy hh:mm"
        End If
    Next ячейка

Завершение:
    Application.EnableEvents = True               ' ОБЯЗАТЕЛЬНО, даже после ошибки
    Exit Sub

Ошибка:
    MsgBox "Не удалось проставить дату: " & Err.Description, vbExclamation
    Resume Завершение
End Sub

Три вещи здесь абсолютно обязательны, и каждая из них — чей-то потерянный вечер:

• Intersect(Target, Me.Range("A2:A1000")). Target — это то, что изменилось. Intersect оставляет только пересечение с интересующим нас диапазоном. Если пересечения нет, функция вернёт Nothing, и мы просто выходим. Без этой проверки макрос будет срабатывать на любое изменение где угодно на листе.
• Target — это диапазон, а не одна ячейка. Пользователь может вставить сразу сто строк из буфера обмена. Поэтому мы перебираем For Each ячейка In изменённые, а не работаем с Target.Value напрямую.
• Application.EnableEvents = False. Наша запись даты в столбец B — это тоже изменение листа. Оно снова вызовет Worksheet_Change, тот снова запишет дату… В простом случае это «всего лишь» лишние срабатывания, в сложном — бесконечная рекурсия и падение Excel. И обратное включение обязано стоять в блоке уборки: если макрос упадёт с выключенными событиями, все события в Excel перестанут работать до перезапуска программы.

Другие полезные события: Worksheet_SelectionChange (сменилось выделение — удобно для подсветки активной строки), Workbook_Open (книгу открыли — пишется в модуле «ЭтаКнига»), Workbook_BeforeSave (перед сохранением — можно проверить заполненность обязательных полей и отменить сохранение через Cancel = True).

Как это работает

Кнопка формы хранит имя назначенного макроса как обычную строку. Когда по ней щёлкают, Excel ищет процедуру с таким именем среди публичных Sub в обычных модулях книги — вот почему макрос в модуле листа «не виден». Отсюда же следует неприятное свойство: если вы переименуете процедуру, кнопка будет искать старое имя и выдаст ошибку «Не удаётся найти макрос». Кнопку придётся переназначить.

Форма — это объект-класс: frmОтчёт.Show создаёт её экземпляр, показывает и держит в памяти, пока не выполнится Unload. Значения элементов (cboОтдел.Value) читаются прямо из этого объекта — но только пока он жив, поэтому после Unload Me обращаться к полям формы бессмысленно.

События же Excel вызывает сам, в момент, когда что-то происходит: он находит в модуле листа процедуру с точным зарезервированным именем и нужной сигнатурой (Private Sub Worksheet_Change(ByVal Target As Range)) и передаёт ей изменившийся диапазон. Одна буква в имени не та — процедура просто никогда не вызовется, и никакой ошибки вы не увидите. Поэтому имена событий выбирают из двух выпадающих списков вверху окна кода, а не набирают руками.

Частые ошибки

• Макрос для кнопки лежит в модуле листа. В списке «Назначить макрос» его не будет. Переносите в обычный модуль.
• Книга сохранена как .xlsx. Весь код исчезает без предупреждения. Только .xlsm.
• Нет Intersect в Worksheet_Change. Макрос срабатывает на каждое изменение на листе, включая свои собственные.
• Забыли EnableEvents = True после ошибки. События в Excel мертвы до перезапуска — и пользователь этого не понимает.
• Target считают одной ячейкой. Вставка из буфера или удаление столбца — и Target содержит тысячи ячеек.
• Форма делает всю работу сама. Логика запирается внутри окна, её нельзя переиспользовать и невозможно проверить.
• Кнопка называется «Кнопка 1», а сообщений нет вообще. Пользователь нажал — ничего не произошло (макрос отработал за 0,2 секунды). Нажал ещё раз. И ещё. Всегда подтверждайте результат: MsgBox или запись в строку состояния.

Итоги

• Кнопка «Элемент управления формы» + публичный Sub без аргументов в обычном модуле — стандартный способ отдать макрос людям.
• UserForm нужна, когда перед работой надо спросить параметры. Форма только собирает и проверяет ввод, а расчёт делает отдельная процедура.
• Me.Hide прячет форму, Unload Me выгружает её из памяти вместе со всеми введёнными значениями.
• Worksheet_Change позволяет реагировать на ввод автоматически, но требует трёх обязательных мер: Intersect, перебор всех ячеек Target и EnableEvents = False с гарантированным возвратом.
• Код событий — в модуле листа, код кнопок — в обычном модуле, книга — в формате .xlsm.