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

Обновлено: 22.01.2025

Есть несколько причин, по которым панель инструментов может не появиться в Excel на вкладке НАДСТРОЙКИ

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

Когда может случиться: при первом запуске скачанной надстройки

Решение проблемы : перед запуском скачанного файла надстройки, необходимо:

найти скачанный файл надстройки в папке (обычно это папка «Загрузки»)
щелкнуть правой кнопкой мыши на файле - Свойства- ставим галочку Разблокировать- ОК
и только после этого запускать

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

Причина : антивирус повредил файл надстройки при проверке

ЭТО НАИБОЛЕЕ ЧАСТО ВСТРЕЧАЮЩАЯСЯ ПРОБЛЕМА В 2018 - 2020 ГОДАХ
Даже если вам кажется, что у вас нет антивируса, наверняка он есть, — например, встроенный в систему Защитник Windows (он же Windows Defender)

Когда может случиться: в любой момент. чаще всего, это происходит при скачивании файла с сайта

ВАЖНО: убедитесь, что антивирус никак не реагирует на скачивание и запуск надстройки.
Если какое-то предупреждение от антивируса выскочило — с вероятностью 90%, антивирус повредил файл надстройки, и надстройка не запустится.

Причина : в настройках безопасности Excel заданы ограничения

Когда может случиться: в новых версиях Office такое иногда имеет место быть

Решение проблемы : нажимаем в меню Excel Файл — Параметры — Центр управления безопасностью, и потом справа кнопку Параметры центра управления безопасностью . .
В появившемся окне в левом столбце есть пункты Надёжные расположения и Надёжные документы.
По очереди заходим в каждый из этих разделов, и ставим галочки «Отключить все надёжные расположения» и «Отключить надёжные документы».
Теперь закрываем Excel, и пробуем снова запустить надстройку.
После этого Excel не будет считать надстройку ненадежной, только потому что она расположена не в той папке.

Причина : надстройка не запустилась вместе с Excel

Когда может случиться: при повторных запусках (когда до этого всё работало)

Решение проблемы : убедитесь, что надстройка запущена. Для этого, найдите файл надстройки в папке, и запустите его двойным щелчком.
Если надстройка запускается автоматически (включена опция автозапуска в О программе — Опции), убедитесь, что вы не перемещали и не переименовывали файл надстройки.

Причина : макросы в Excel отключены в режиме «без уведомлений»
Excel просто молча отключает все макросы во всех файлах.

Когда может случиться: при первом запуске скачанной надстройки
Встречается очень редко. По умолчению, макросы отключены «с уведомлением», т.е. Excel спрашивает, нужно ли включить макросы для каждого запускаемого файла с макросами.

Решение проблемы : нужно включить макросы в Excel
Нажимаем в меню Excel Файл — Параметры — Центр управления безопасностью, и потом справа кнопку Параметры центра управления безопасностью . .
В появившемся окне в левом столбце выбираем пункт Параметры макросов, и справа выбираем опцию «Включить все макросы»

Создайте или откройте файл Excel с расширением .xlsm (Книга Excel с поддержкой макросов) или с расширением .xls в старых версиях приложения.

Перейдите в редактор VBA, нажав сочетание клавиш «Левая_клавиша_Alt+F11».

В открывшемся окне редактора VBA выберите вкладку «Insert» главного меню и нажмите кнопку «UserForm». То же подменю откроется при нажатии на вторую кнопку (после значка Excel) на панели инструментов.

На экране редактора VBA появится новая пользовательская форма с именем «UserForm1»:

Добавление элементов управления

Обычно вместе с пользовательской формой открывается панель инструментов «Toolbox», как на изображении выше, с набором элементов управления формы. Если панель инструментов «Toolbox» не отобразилась, ее можно вызвать, нажав кнопку «Toolbox» во вкладке «View»:

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

Найдите на панели инструментов «Toolbox» элемент управления с подсказкой «TextBox», кликните по нему и, затем, кликните в любом месте рабочего поля формы. Элемент управления «TextBox» (текстовое поле) будет добавлен на форму.

Найдите на панели инструментов «Toolbox» элемент управления с подсказкой «CommandButton», кликните по нему и, затем, кликните в любом месте рабочего поля формы. Элемент управления «CommandButton» (кнопка) будет добавлен на форму.

Кликнув по элементу управления на форме, вы можете изменять его размер, перетягивая границы за белые квадратики, и перетаскивать по форме, ухватив за одну из границ. Кликнув по заголовку формы или по ее рабочему полю, вы можете также изменять ее размер, перетягивая границы за белые квадратики.

Нажатием клавиши «F4» вызывается окно свойств, с помощью которого можно вручную задавать значения свойств пользовательской формы и элементов управления. В окне свойств отображаются свойства выбранного элемента управления или формы, если выбрана она. Также окно свойств можно вызвать, нажав кнопку «Properties Window» во вкладке «View».

Отображение формы на экране

Чтобы запустить пользовательскую форму для просмотра из редактора VBA, необходимо выбрать ее, кликнув по заголовку или свободному от элементов управления полю, и совершить одно из трех действий:

нажать клавишу «F5»;
нажать на треугольник на панели инструментов (на изображении выше треугольник находится под вкладкой «Debug»);
нажать кнопку «Run Sub/UserForm» во вкладке «Run».

Для запуска пользовательской формы с рабочего листа Excel, можно использовать кнопки, описанные в этой статье. Например, чтобы отобразить форму с помощью «кнопки – элемента ActiveX», используйте в модуле рабочего листа следующий код:

Для запуска процедур и макросов обычно используется событие кнопки – Click.

Свойства элемента CommandButton

Свойство	Описание
AutoSize	Автоподбор размера кнопки. True – размер автоматически подстраивается под длину введенной надписи (заголовка). False – размер элемента управления определяется свойствами Width и Height.
BackColor	Цвет элемента управления CommandButton.
Caption	Надпись (заголовок) – текст, отображаемый на кнопке.
ControlTipText	Текст всплывающей подсказки при наведении курсора на кнопку.
Enabled	Возможность взаимодействия пользователя с элементом управления CommandButton. True – взаимодействие включено, False – отключено (цвет надписи становится серым).
Font	Шрифт, начертание и размер текста надписи.
Height	Высота элемента управления.
Left	Расстояние от левого края внутренней границы пользовательской формы до левого края элемента управления.
Picture	Добавление изображения вместо текста заголовка или дополнительно к нему.
PicturePosition	Выравнивание изображения и текста на кнопке.
TabIndex	Определяет позицию элемента управления в очереди на получение фокуса при табуляции, вызываемой нажатием клавиш «Tab», «Enter». Отсчет начинается с 0.
Top	Расстояние от верхнего края внутренней границы пользовательской формы до верхнего края элемента управления.
Visible	Видимость элемента управления CommandButton. True – элемент отображается на пользовательской форме, False – скрыт.
Width	Ширина элемента управления.
WordWrap	Перенос текста заголовка на новую строку при достижении ее границы. True – перенос включен, False – перенос выключен.

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

Пример кнопки с надписью и изображением

Примеры кода VBA Excel с кнопкой

Изначально для реализации примеров на пользовательскую форму UserForm1 добавлена кнопка CommandButton1.

Пример 1

Изменение цвета и надписи кнопки при наведении на нее курсора.

Условие примера 1

Действия при загрузке формы: замена заголовка формы по умолчанию на «Пример 1», замена надписи кнопки по умолчанию на «Кнопка», запись цвета кнопки по умолчанию в переменную уровня модуля.
Сделать, чтобы при наведении курсора на кнопку, она изменяла цвет на зеленый, а надпись «Кнопка» менялась на надпись «Нажми!»
Добавление кода VBA Excel, который будет при удалении курсора с кнопки возвращать ей первоначальные настройки: цвет по умолчанию и надпись «Кнопка».

Решение примера 1

1. Объявляем в разделе Declarations модуля пользовательской формы (в самом начале модуля, до процедур) переменную myColor:

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

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

Формирование панели инструментов происходит при загрузке файла, при закрытии же его - созданная панель скрывается:

Основной код модуля:

Sub ImageFromEmbedded()
Dim P As Excel.Picture
Dim Btn As Office.CommandBarButton
Set Btn = Application.CommandBars.FindControl(ID:=30007) _
.Controls.Add(Type:=msoControlButton, temporary:=True)
Set P = Worksheets("Sheet1").Pictures("ThePict")
P.CopyPicture xlScreen, xlBitmap
With Btn
.Caption = "Click Me"
.Style = msoButtonIconAndCaption
.PasteFace
End With
End Sub

Если вопрос личного плана, - на почту или в скайп.
Если надо что-то сделать за денежку, - оформляете заказ.
Если вопрос по Excel (но статьи на сайте нет), - обращайтесь на форумы по Excel (там всегда подскажут и помогут)

Да, не туды в Object Browser'e глянул, извиняюсь.

А если у меня вопрос к Вам, не касающийся этой темы, и подходящей темы нет, то как быть?

Ну а как нет-то. смотрите пример в этой статье
Если вы говорите про msoControlButton на панели инструментов Excel

Оно хорошо, но разве у Button есть свойство FaceID?

Ещё вопрос: как можно кнопке типа msoControlButton указать свою картинку? Что надо загнать в свойство Picture и какие требования предъявляются к таким иконкам?
Заранее спасибо за ответ)

Здравствуйте (свинтус я, что сразу не поздоровался).
Подход правильный, т.к. суть во внешней подписке, которая слушает эти события - конечная цель в ней, а не в них. Private убирал, но не видит он эти Sub'ы всё равно. Хотя, попробую ещё. А вот вариант "сам-себе-присвоил" красивый. Не знал, что эксель на такое ведётся и событие изменения генерит. Спасибо!
. хотя в идеале всё равно хотелось бы своё событие дёргать, а не штатное.

Здравствуйте, Яков
Самый простой способ вызвать обработчик события, - просто обратиться к нему, как к любому другому макросу.
Ну и конечно, слово Private перед Sub надо убрать:

Код вызова примерно такой:

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

как-то так
для разных событий - по-разному.

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

PS: Вариант с RaiseEvent не сработает, - он в других случаях применяется.

Как можно (и можно ли) вызвать из макроса, подцепленного к кнопке, любое штатное событие книги/листа/приложения? Нужно как-то "пнуть" обработчик события. Если есть способ дёрнуть RaiseEvent'ом или ещё как свои собственные события - тож хорошо.

Хоть вопрос и не имеет никакого отношения к теме статьи, всё же предложу вариант:

эта метка не сохраняется при сохранении файла
потому, если на следующий день снова откроете файл, и запустите макрос - он сработает

можно метку прописывать в ячейку - заменив в коде Cells(1).ID на range("h2").value

Добрый день Игорь. воспользовался Вашим кодом для создания панелей инструментов. У меня возник вопрос : по нажатию на кнопку копировать диапазон данные копируются на лист2 и удаляется столбец. Как запретить повторное копирование так как после повторного нажатия кнопки удаляется столбец на новом листе.
Использую такой код :
' Кнопка копирование диапазона"

Sub Copy_Range()
On Error Resume Next
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(5, 1), Cells(LastRow, 5)).Copy Sheets("Лист2").Cells(5, 1) ' Копирование данных на лист2
Sheets("лист2").Columns("D:D").Delete Shift:=xlToLeft 'удаление столбца D

Здравствуйте, вот код который добавляет на ленту в надстройки кнопку:

Sub addinn1()
With Application.Toolbars(1).ToolbarButtons.Add(Button:=222)
.Name = "Кнопка"
.OnAction = "макрос1"
End With
End Sub

Какой вид будет иметь кнопка, зависит от ее id. Мне нужен список с описанием "id - кнопка". Помогите кто чем может!

Здравствуйте!
Создал панель инструментов с кнопкой "Отчеты". Мне нужно ,что бы по нажатии на кнопку "Отчеты" появлялся выпадающий список (кнопки)с надписями(5штук)

Вообще-то можно всё, просто Вы с этим ещё не работали.
Я тоже раньше старался придерживаться совместимости версий, но после того, как убедил руководство полностью перейти на Excel 2010, забыл о нужных только мне проверках. Кстати, некоторые вещи без этих проверок не будут корректно выполняться в обоих версиях: например, при создании условного форматирования.
Но я никогда не писал универсальных программ, - только под чётко поставленное ТЗ (мне время дорого и некогда ждать обратную связь).

Я делаю универсальные программы, которые должны работать во всех версиях Excel (начиная с 2003).
Кроме того, меню в некоторых моих программах формируется динамически (макросом).
Как это сделать в Excel 2007? Никак? вот то-то же.
(т.е. сделать-то можно, но кода будет очень много)

А оставлять поддержку только Excel 2010 (где можно программно формировать панель инструментов на ленте) - ещё рано,
очень много пользователей до сих пор сидят на Excel 2003-2007

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

Спасибо за информацию, начну изучать.

XML надо не в VBA добавлять, а в сам файл.
Меню проще сделать вручную, чем писать какой-то хитрый код.
Поищите в гугле Ribbon Editor - есть несколько хороших реализаций.

PS: Тут я вам вряд ли что могу подсказать, - сам ещё ни разу не делал «красивые» меню,
ибо в моих программах важнее совместимость с Excel 2003,
а XML можно добавить только в файлы формата Office 2007 (c 4-буквенным расширением)

Доброго времени суток!
Интересует вопрос использования в разработке кода xml (в данном случае для отображения больших кнопок). С разметкой я знаком, но никак не могу взять в толк, как добавить в VB код XML, можете задать направление для поиска решения моей проблемы?

Добрый!
Отличный сайт много интересных и нужных вещей. Спасибо.
Теперь вопрос: Панельки прикрутил все запускается отлично, но при каждом открытии Excel панельки создаются в одном и том же месте. Как сделать что они были расположены там и где все панели, что небыло необходимости постоянно их убирать в нужное место?

Очень рад,что обнаружил этот сайт.
Скопировал и запустил в лоб, ОТЛИЧНО.
Я уже думал, что от старой версии Excel пользовательские меню и кнопки маросов исчезли навсегда.
Буду посещать сайт узнавать больше. СПАСИБО.

Подскажите, пожалуйста, как сделать кнопки большого размера на ControlBar?

Спасибо за подсказку. Пожалуй, так и сделаю.

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

Я бы посоветовал вам сделать группировку элементов панели - оно и удобнее, и выглядит лучше:

Здравстуйте, Игорь.
Спасибо за предоставленный код - очень помог в создании собственной надстройки.
Возник вопрос. Если контролов (кнопок, текстовых полей, полей ввода и тп) на разрабатываемой панели надстройки будет много, то все они вытягиваются в одну строку. Как переносить контролы на новую строку, чтобы не пришлось прибегать к кнопке скролинга на панели ">>" ?
Excel 2007.

Старайтесь не пользоваться китайской программой Office Tab, чтобы не было таких проблем как у меня! Она, как раз, управляет вкладками и лезет также в панели.
Снёс её и некоторые (к сожалению только некоторые) коды заработали, в том числе и приведённый вами код.
Спасибо за внимание! Вы оперативно отвели на моё письмо. Очень приятно!

Скажите, пожалуйста, есть ли код VBA для создания своей панели инструментоа в Excel 2010?!
Ни один приведённый здесь код не работает.
У меня просто не раегирует даже на CommandBars("Formatting").Visible = False

Наверное, в Excel 2010 сделать дто с помощью VBA просто не возможно.

Игорь,
Спасибо, за ответ. Я не программист, а простой пользователь.
Прошу прощения, что не нашел на сайте указанные материалы. Вы создали великолепный сайт. На нем очень много полезной информации, но найти нужное трудно.

Я так и не понял Вашу фразу "Хотя, достаточно один раз сформировать панель инструментов, и не удалять её программно при закрытии Excel - она никуда не денется, кнопки продолжат работать (при первом нажатии на кнопку, Excel сам откроет файл надстройки)".

Я взял ваш код вставил в свою надстройку, панель сформировалась, внес в неё свои макросы и удалил ненужный мне код для других кнопок. После закрытия excel панель не запоминается. Может я чего не правильно сделал. И существует возможность вставлять свои макросы в вашу настройку без работы в VBA. Но методом тыка я их не нашел))

Благодаря книги "Программирование на VBA в Microsoft Office 2010" (с.390) я нашел нужный мне код. Книга размещена на вашем сайте, за что отдельно спасибо.

Public Sub avf_panel()
Dim cbar1 As CommandBar
Dim cControll As CommandBarControl
Dim cId As Variant

Set cbar1 = CommandBars.Add(Name:="AVF")
With cbar1
.Enabled = True
.Visible = True

Set cControll = cbar1.Controls.Add(Type:=msoControlButton)
With cControll
.Caption = "Название кнопки"
.TooltipText = "Комментарий кнопки"
.Visible = True
.FaceId = 309 (номер иконки)
.OnAction = "Ваш макрос"

Чтобы создать новую кнопку нужно скопировать-вставить с Set по End With еще раз. Переименовать кнопки и макрос.

С уважением, AVF

AVF, вы не пробовали сначала поискать ответы у меня на сайте?

Если в 2007-м нужны КРАСИВЫЕ БОЛЬШИЕ КНОПКИ, то нужно использовать XML (соответственно, файл будет иметь расширение из 4 букв, и в Excel 2003 работать не будет)

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

Игорь, спасибо все получилось.
Но возникла новая проблема. Как сделать так, чтобы настройка не терялась при закрытие - новом открытие excel? Чтобы каждый раз не запускать макрос?
Может сохранить настройку нев Xla, а в Personal.xlsb. Но тогда теряется смысл настройки((
Еще вопрос в excel 2003 можно создать свои кнопки про помощи встроенного редактора. В 2007 и далее этой функции нет. Многие формучане дружественных форумах подминали эту проблему и её решение только писать код в VBA. Причем это код "разный" (у каждого мастера свое кун-фу ;^)). Таким образом это решение не для всех. Вопрос: если создать кнопку+панель в 2003 excele, потом сохранить её в формате .xla и запустить в excel 2007, то будет ли она работать (находиться в закладке надстройки, иметь собственные рисунки кнопок + макросы)?

Добрый день,
Отличный сайт и великолепный Код.
Долго искал как сделать собственную настройку, и наконец все нашел. Спасибо Вам за это.
Вопрос: где можно посмотреть иконки для кнопок и их номера? Если заменить 271 на другое число измениться и иконка.
"Add_Control AddinMenu, ct_BUTTON, 271, "CreateBackup", "Create Backup and Save", , True"
Как использовать свою иконку?

Уважаемый EducatedFool (Игорь), благодарю за то, что поделились наработками по созданию надстроек.

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

ПРИМЕР
Add_Control AddinMenu, ct_BUTTON, 271, "НазваниеВашегоМакроса", "Надпись на кнопке", , True

Спасибо, с этим разобрался!
Помогите разобраться, как теперь "привязать" макрос к "дополнительным" макросам из выпадающего списка.
Пробовал решить вопрос по аналогии, но не получилось.

Очень просто.
За добавление кнопок на панель, и назначение им макросов, отвечают эти строки кода:

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

Здравствуйте!
Подскажите, пожалуйста, как привязать макрос к созданной (с помощью Вашей надстройки) панели инструментов.
Спасибо.

Как при запуске поместить панель инструментов рядом с панелью "Стандартная"?

Надо задать значение свойства Position для панели инструментов.
В вашем случае Position = msoBarTop
Кроме того, не помешает последней строкой кода включить обновление экрана.
В итоге код функции будет выглядеть так:

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