Как привязать макрос к ячейке excel

Обновлено: 03.07.2024

У вас большой монитор, но таблицы, с которыми вы работаете - еще больше. И, пробегая взглядом по экрану в поисках нужной информации, всегда есть шанс "соскользнуть" взглядом на соседнюю строчку и посмотреть не туда. Я даже знаю людей, который для таких случаев постоянно держат недалеко от себя деревянную линейку, чтобы приложить ее к строке на мониторе. Технологии будущего!

А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:

Поудобнее, чем линейка, правда?

Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ - со своими плюсами и минусами. Давайте разберем их детально.

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

Самый очевидный путь для решения нашей проблемы "в лоб" - нам нужен макрос, который будет отслеживать изменение выделения на листе и выделять целую строку и столбец для текущей ячейки. Также желательно иметь возможность при необходимости включать и отключать эту функцию, чтобы такое крестообразное выделение не мешало нам вводить, например, формулы, а работало только тогда, когда мы просматриваем список в поисках нужной информации. Это приводит нас к трем макросам (выделения, включения и выключения), которые нужно будет добавить в модуль листа.

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.

Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.

Плюсы этого способа:

относительная простота реализации
выделение - операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть

Минусы этого способа:

такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки - выделяются сразу все строки и столбцы, входящие в объединение
если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)

Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:

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

Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.

Вторая составляющая этого способа - условное форматирование. Эта крайне полезная функция Excel позволяет автоматически форматировать ячейки, если они удовлетворяют заданным условиям. Если соединить эти две идеи в одно целое, то получим следующий алгоритм реализации нашего координатного выделения через условное форматирование:

Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
Вводим формулу для нашего координатного выделения:

Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:

Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.

Плюсы этого способа:

Условное форматирование не нарушает пользовательское форматирование таблицы
Этот вариант выделения корректно работает с объединенными ячейками.
Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
Макросы используются минимально

Минусы этого способа:

Формулу для условного форматирования надо вводить вручную.
Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.

Способ 3. Оптимальный. Условное форматирование + макросы

Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.

Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.

Способ 4. Красивый. Надстройка FollowCellPointer

Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:

Красивое решение. Не без глюков местами, но попробовать точно стоит. Качаем архив, распаковываем на диск и устанавливаем надстройку:

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

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

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

Добавление кнопки макроса на панель быстрого доступа

Откройте вкладку Файл и выберите пункт Параметры, а затем — категорию Панель быстрого доступа.

В списке Выбрать команды из выберите пункт Макрос.

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

Чтобы заменить значок макроса по умолчанию другой кнопкой для макроса, нажмите кнопку Изменить.

В области Символвыберите значок кнопки для макроса.

Чтобы использовать более имя кнопки, в поле Отображаемая имя введите нужное имя.

В имя кнопки можно ввести пробел.

Дважды нажмите кнопку ОК.

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

Совет: После сохранения книгикнопки, которые вы назначаете макросам в личной книге, будут доступны во всех открытых книгах.

Добавление кнопки макроса в группу на ленте

Щелкните Файл > Параметры > Настроить ленту.

В списке Основные вкладки в списке Настроить ленту, если он еще не был проверен,в поле Разработчик.

Выберите вкладку, на которой вы хотите добавить свою группу.

Например, выберите Главная, чтобы добавить группу на вкладку Главная.

Выберите Новая группа.

При этом новая группа (настраиваемая) будет добавлена на вкладку, которую вы выбрали.

Чтобы использовать более понятное имя для новой группы, нажмите кнопку Переименовать ,введите нужное имя в поле Отображаемая группа и нажмите кнопку ОК.

В имени можно ввести пробел. Например, введите Мои макросы.

Чтобы добавить макрос в группу, в списке Выбрать команды из выберите пункт Макрос.

Выберите макрос, который вы хотите добавить в новую группу, и нажмите кнопку Добавить. Макрос будет добавлен в группу Мои макросы.

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

В имени можно ввести пробел.

В области Символвыберите значок кнопки для макроса.

Дважды нажмите кнопку ОК.

Новая группа появится на вкладке, на которой можно нажать кнопку для запуска макроса.

Более новые версии

На вкладке Разработчик в группе Элементы управления щелкните Кнопка.

Если вкладка "Разработчик" недоступна:

Перейдите в Excel > параметры. >ленты & панели инструментов.

В разделе Настройка ленты в разделе Основныевкладки, в разделе Разработчики нажмите кнопку ОК.

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

В диалоговом окне Назначение макроса щелкните имя макроса, который вы хотите назначить кнопке, и нажмите ОК.

Чтобы изменить размер кнопки, перетащите маркеры.

Чтобы задать свойства кнопки, щелкните ее, нажав клавишу CONTROL, или щелкните ее правой кнопкой мыши, а затем выберите форматирование.

Excel 2011 для Mac

На вкладке Разработчик в группе Элементы управления формы щелкните Кнопка.

Если вкладка "Разработчик" недоступна:

В правой части ленты нажмите кнопку и выберите параметры ленты.

В разделе Настройки установите флажок Разработчик.

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

В диалоговом окне Назначение макроса щелкните имя макроса, который вы хотите назначить кнопке, и нажмите ОК.

Вы можете использовать кнопку управления формы или кнопку (ActiveX) для запуска макроса, который выполняет действие, когда пользователь щелкает его. Обе эти кнопки также называются push-кнопками, которые можно настроить для автоматизации печати, фильтрации данных или вычисления чисел. Как правило, кнопки управления формы и ActiveX похожи по внешнему виду и функции. Однако между ними есть несколько отличий, которые объясняются в следующих разделах.

Кнопка (управление формы)

В разделах ниже вы узнаете, как добавить макрос на кнопку в Excel для Windows или Mac.

Примечание: ActiveX не поддерживаются на компьютере Mac.

Макрос и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта.

Прежде всего нужно включить его. Дополнительные сведения см. в статье Показ вкладки "Разработчик".

Добавление кнопки (управление формы)

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

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

Назначьте кнопке макрос и нажмите кнопку ОК.

Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.

Добавление кнопки (ActiveX управления)

На вкладке Разработчик в группе Элементы управления нажмите кнопку Вставить, а затем вгруппе Элементы управления ActiveX нажмите кнопку .

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

В группе Элементы управления нажмите кнопку Код представления. При этом Visual Basic редактора. Убедитесь, что в списке справа выбрано нажатие кнопки. При нажатии кнопки CommandButton1_Click процедуры (см. рисунок ниже) запускаются эти два макроса: SelectC15 и HelloMessage.

В подпроцеду для кнопки сделайте следующее:

Введите имя существующего макроса в книге. Макрос можно найти, щелкнув Макрос в группе Код. С помощью кнопки можно запускать несколько макроса, вводя их имена в отдельных строках подпроцесса.

При необходимости добавьте собственный код VBA.

Закроем Visual Basic редактора и нажмите кнопку Режим конструктора режим конструктора, чтобы отключить режим конструктора.

Чтобы запустить код VBA, который теперь является частью кнопки, нажмите кнопку ActiveX, которую вы только что создали.

Чтобы изменить элемент ActiveX, убедитесь, что выбран режим конструктора. На вкладке Разработчик в группе Элементы управления нажмите кнопку Режим конструктора.

Чтобы задать свойства кнопки, на вкладке Разработчик в группе Элементы управления нажмите кнопку Свойства . Можно также щелкнуть правой кнопкой мыши кнопку и щелкнуть свойства .

Примечание: Перед тем как нажать кнопку Свойства, выделите объект, свойства которого требуется просмотреть или изменить.

Появится поле Свойства. Для получения дополнительных сведений о каждом свойстве выберите свойство и нажмите клавишу F1, чтобы отобразить соответствующий раздел справка по Visual Basic. Можно также ввести имя свойства в поле Поиск справки Visual Basic. В следующей таблице 2010 2010 2016 2016 2016 2016 201

Описание параметра

Необходимое свойство

Загружает ли его при открытом книге. (Игнорируется для ActiveX элементов управления.)

Может ли он получать фокус и отвечать на события, созданные пользователем.

Можно ли изменять элемент управления

Имя элемента управления

Способ привязки элемента управления к расположенным под ним ячейкам (не закреплен, можно перемещать, но нельзя изменять размер, можно перемещать и изменять размер)

Можно ли выводить элемент управления на печать

Является ли элемент управления видимым или скрытым

Атрибуты шрифта (полужирный, курсив, размер, зачеркивание, подчеркивание и насыщенность)

Bold, Italic, Size, StrikeThrough, Underline, Weight (форма)

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

Будет ли содержимое элемента управления переноситься по словам в конце строки

Размер и положение

Будет ли размер элемента управления изменяться автоматически для отображения всего содержимого

Высота или ширина в пунктах

Height, Width (форма)

Расстояние от элемента управления до левого или верхнего края листа

Форматирование

Стиль фона (прозрачный или непрозрачный)

Цвет переднего плана

Наличие тени элемента управления

Изображение

Точечный рисунок, отображаемый в элементе управления

Положение рисунка относительно его заголовка (слева, сверху, справа и так далее)

Клавиатура и мышь

Сочетание клавиш для элемента управления

Настраиваемый значок мыши

Тип указателя, отображаемого при наведите указатель мыши на определенный объект (стандартный, стрелка, I-указатель и так далее).

Будет ли фокус при щелчке.

Макрос и средства VBA находятся на вкладке "Разработчик", которая по умолчанию скрыта, поэтому сначала нужно включить ее.

Перейдите в Excel > параметры. > ленты & панель инструментов.

В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

Добавление кнопки (управление формы)

На вкладке Разработчик нажмите кнопку .

Примечание: Если кнопка уже вставлена, щелкните ее правой кнопкой мыши и выберите назначить макрос.

Назначьте кнопке макрос и нажмите кнопку ОК.

Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.

Добавление кнопки (Visual Basic управления)

На вкладке Разработчик нажмите кнопку .

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

Примечание: Если кнопка уже вставлена, щелкните ее правой кнопкой мыши и выберите назначить макрос.

В диалоговом окне Назначение макроса выберите Новый, который откроет редактор Visual Basic(VBE)в области со следующим кодом:

В подпроцеду для кнопки между строками Sub и End Sub сделайте следующее:

Введите имя существующего макроса в книге. Вы можете запускать несколько макроса с помощью кнопки, введя их имена в отдельных строках внутри под процедуры.

Добавьте собственный код VBA.

Чтобы изменить кнопку, щелкните ее правой кнопкой мыши ивыберите Visual Basic .

Чтобы задать свойства кнопки, щелкните ее правой кнопкой мыши и выберите форматирование.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Предположим, вы создали макрос, который нужно выполнить несколько раз. Например, макросы, которые вы создали для абсолютных ссылок и относительных ссылок. Тогда вам будет легко, если вы сможете запустить макрос с помощью щелчка мыши. Вы можете сделать это, назначив макрос объекту, такому как фигура, графика или элемент управления.

В этой главе вы узнаете, как включить объект в рабочую книгу и назначить ему макрос.

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

Назначение макроса для фигуры

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

Нажмите вкладку INSERT на ленте.

Нажмите « Фигуры» в группе «Иллюстрации».

Нажмите вкладку INSERT на ленте.

Нажмите « Фигуры» в группе «Иллюстрации».

Нарисуйте форму и отформатируйте ее.

Щелкните правой кнопкой мыши на фигуре и выберите Edit Text из выпадающего списка.

Щелкните правой кнопкой мыши на форме.
Выберите Назначить макрос из выпадающего списка.

Откроется диалоговое окно «Назначить макрос». Щелкните имя макроса, т.е. RelativeMacro, и нажмите ОК.

Читайте также: