📌 VBA Excel: примеры решений. Активация кнопки выполнения макроса в зависимости от значения в ячейке

30 января 202430 янв 2024

242

2 мин

Оглавление

▶️ Подготовка
▶️ Пример 1. Используем событие Worksheet.Calculate
▶️ Пример 2. Используем событие Worksheet.Change

Выполнение макроса по нажатию на кнопку пожалуй одна из самых распространенных техник применяемых в Excel.

Но, что если доступ к определенному макросу требуется ограничить?!😉

В данном обзоре рассмотрим 2 (два) подхода решения позволяющие управлять активацией кнопок вызова макроса в зависимости от значения в целевой ячейке, что позволит регулировать доступность к заданному функционалу.

📢 Файл с примером размещен в конце статьи 🔽

Данный подход будет полезен если требуется ограничить действия пользователя и разрешить вызов только соответствующего макроса.

▶️ Подготовка

Для целей данного примера создадим отдельный лист - справочник в котором определим все возможные макросы и их целевое действие:

В качестве кнопок на которые предположительно будут назначены макросы размещаем на листе нужное число элементов ActiveX:

🔘 Дополнительно (для примера 1):

для ячейки D1 листа-справочника зададим имя:

на листе с реализуемым примером разместим элемент управления выпадающий список для которого установим связь я ячейкой и критерии для выбора:

Размещаем элемент управления выпадающий список

и пропишем формулу:

Определяем формулу для управления пересчетом на листе

🔘 Дополнительно (для примера 2) определим именованный диапазон "Значение":

Определение именованного диапазона для выбора значений

Теперь, когда все подготовительные действия выполнены рассмотрим два примера решения

▶️ Пример 1. Используем событие Worksheet.Calculate

В модуле листа поместим код:

Данный макрос позволяет управлять состоянием трех кнопок (CommandButtonA, CommandButtonB, CommandButtonC) в зависимости от значения, находящегося в указанной целевой ячейке.

Если значение в целевой ячейке соответствует определенным условиям (1, 2, 3 или 4), то соответствующая кнопка включается, а остальные отключаются.

Событие Worksheet.Change: Активация кнопки выполнения макроса в зависимости от значения в ячейке

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

▶️ Пример 2. Используем событие Worksheet.Change

Для выбора нужного значения помимо использования элемента управления формы решение задачи можно реализовать посредством простого выпадающего списка.

В этом случае выпадающий список используется для выбора значения, и это значение сравнивается с данными из столбца "Значение". Затем, в зависимости от найденного индекса, соответствующая кнопка включается или отключается.

Макрос срабатывает при изменении ячейки A1 на данном листе. и обновляет состояние кнопок в соответствии с выбранным значением в выпадающем списке

⚙️ Краткое описание:

проверяем, была ли изменена ячейка A1 на текущем листе;
сбрасываем состояние всех кнопок на "Выключено";
получаем значение из выпадающего списка в ячейке A1.
поиск соответствующего индекса для выбранного значения в столбце "Значение";
записываем найденный индекс в ячейку D1. В зависимости от значения индекса включает соответствующую кнопку.

📝 Примечание автора: Поиск индекса осуществляется аналогично тому если была бы применена формула: =ПОИСКПОЗ(A1;Значение;0)

Событие Worksheet.Calculate: Активация кнопки выполнения макроса в зависимости от значения в ячейке

Данный вариант может быть использован как альтернативное решение, когда необходимо динамически управлять состоянием кнопок в зависимости от выбранных данных.