Программное создание пользовательской формы из кода VBA Excel. Добавление на форму кнопки и события Click в модуль созданной формы. Удаление формы.
Программная работа с формой
Создание
Пользовательская форма программно (динамически) может быть создана только как элемент коллекции VBComponents проекта VBA:
|
1 2 |
Dim myForm As Object Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3) |
Число (3) означает, что создается форма, если заменить его на (1), будет создан стандартный модуль.
Значения свойствам формы myForm, как элемента коллекции VBComponents, присваиваются следующим образом:
|
1 2 3 4 5 6 |
With myForm .Properties("Name") = "myForm1" 'Иногда эта строка вызывает ошибку .Properties("Caption") = "Эта форма создана программно" .Properties("Width") = 300 .Properties("Height") = 150 End With |
Присвоенные значения параметров динамической формы, как элемента коллекции VBComponents, сохраняются при закрытии формы.
Для элемента коллекции UserForms присвоение значений свойств будет выглядеть следующим образом:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Dim myForm As Object, myFormForms As Object 'Создаем форму как элемент коллекции VBComponents Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3) 'На основе myForm создаем элемент коллекции UserForms Set myFormForms = UserForms.Add(myForm.Name) With myFormForms .Caption = "Эта форма создана программно" .Width = 300 .Height = 150 .Show End With |
Присвоенные значения параметров динамической формы, как элемента коллекции UserForms, не сохраняются при закрытии формы.
Я не рекомендую работать из кода VBA Excel с программно созданной формой, как элементом коллекции UserForms, кроме случая, когда необходимо отобразить динамическую форму на экране. В других случаях можно получить непредсказуемые результаты, например, могут не работать программно созданные кнопки, а также они будут исчезать при закрытии формы.
Отображение
Здесь возникает проблема: у динамической формы, созданной как элемент коллекции VBComponents, нет метода, который позволил бы ее отобразить на экране.
Чтобы обойти это препятствие, преобразуем нашу форму myForm в элемент коллекции UserForms, как в предыдущем примере, и выводим ее на экран с помощью метода Show:
|
1 2 3 4 |
'извлекая имя из объектной переменной UserForms.Add(myForm.Name).Show 'или сразу по ранее присвоенному имени UserForms.Add("myForm1").Show |
Удаление
Удаление программно созданных форм, если это не однократное действие, крайне важно. Если этого не предусмотреть, то все вновь создаваемые формы будут сохраняться и накапливаться в проекте VBA.
Удаляем динамически созданную форму с именем myForm1 следующим образом:
|
1 2 3 |
With ThisWorkbook.VBProject.VBComponents .Remove .Item("myForm1") End With |
Программно удалить форму можно только через элемент коллекции VBComponents, так как у элемента коллекции UserForms удаление не предусмотрено (нет соответствующего метода).
Добавление кнопки на форму
Код VBA Excel, добавляющий командную кнопку на динамически созданную форму и задающий ее свойства:
|
1 2 3 4 5 6 7 8 9 10 11 |
Dim myButton As Control Set myButton = myForm.Designer.Controls.Add("Forms.CommandButton.1") With myButton .Name = "myCommandButton" .Caption = "Новая кнопка" .Font.Size = 10 .Left = 100 .Top = 80 .Width = 100 .Height = 20 End With |
Здесь, при создании кнопки на основе элемента коллекции VBComponents, добавляется только объект Designer, которого нет при программном создании элементов управления на основе элемента коллекции UserForms (также смотрите пример кода ниже). Константы, указывающие на класс создаваемого элемента управления, используются те же.
Запись процедуры в модуль
В модуль динамически созданной формы myForm запишем код, который будет выполняться при клике по программно созданной в предыдущем примере кнопке myButton (объектная переменная) с именем «myCommandButton». Этот код создает на форме элемент управления Label, выравнивает его и записывает в него текст: «Ура! Новая кнопка работает!».
Текст процедуры
Обработка события Click для кнопки с именем «myCommandButton»:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Private Sub myCommandButton_Click() Dim myLabel As Object Set myLabel = Me.Controls.Add("Forms.Label.1") With myLabel .Caption = "Ура! Новая кнопка работает!" .Font.Size = 10 .Left = 85 .Top = 30 .Width = 200 .Height = 20 End With End Sub |
Способ 1
Первый способ предназначен для записи в модуль формы myForm кода процедуры целиком. Для этого используется метод AddFromString:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
myForm.CodeModule.AddFromString ("Private Sub myCommandButton_Click()" & vbNewLine & _ "Dim myLabel As Object" & vbNewLine & _ "Set myLabel = Me.Controls.Add(" & Chr(34) & "Forms.Label.1" & Chr(34) & ")" & vbNewLine & _ "With myLabel" & vbNewLine & _ ".Caption = " & Chr(34) & "Ура! Новая кнопка работает!" & Chr(34) & vbNewLine & _ ".Font.Size = 10" & vbNewLine & _ ".Left = 85" & vbNewLine & _ ".Top = 30" & vbNewLine & _ ".Width = 200" & vbNewLine & _ ".Height = 20" & vbNewLine & _ "End With" & vbNewLine & _ "End Sub") |
Все прямые кавычки из текста процедуры заменены на функцию Chr(34), где 34 – числовой код парной прямой кавычки.
Способ 2
Этот способ предполагает запись кода процедуры в модуль формы myForm построчно. Для этого используется метод InsertLines:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Dim n As Integer With myForm.CodeModule n = .CountOfLines .InsertLines n + 1, "Private Sub myCommandButton_Click()" .InsertLines n + 2, "Dim myLabel As Object" .InsertLines n + 3, "Set myLabel = Me.Controls.Add(" & Chr(34) & "Forms.Label.1" & Chr(34) & ")" .InsertLines n + 4, "With myLabel" .InsertLines n + 5, ".Caption = " & Chr(34) & "Ура! Новая кнопка работает!" & Chr(34) .InsertLines n + 6, ".Font.Size = 10" .InsertLines n + 7, ".Left = 85" .InsertLines n + 8, ".Top = 30" .InsertLines n + 9, ".Width = 200" .InsertLines n + 10, ".Height = 20" .InsertLines n + 11, "End With" .InsertLines n + 12, "End Sub" End With |
Свойство модуля CountOfLines возвращает количество уже имеющихся в модуле строк, что позволяет с помощью переменной n добавлять новые строки ниже. Это актуально, если используется для записи кода уже существующий модуль, или новый модуль, в который записывается сразу несколько процедур.
Пример создания и удаления формы
Пример кода VBA Excel по динамическому созданию новой пользовательской формы, добавлению на нее командной кнопки и программного кода в ее модуль:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Sub AddNewForm() 'Создаем форму, как новый экземпляр коллекции VBComponents Dim myForm As Object Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3) 'Присваиваем значения свойствам формы myForm With myForm .Properties("Name") = "myForm1" .Properties("Caption") = "Эта форма создана программно" .Properties("Width") = 300 .Properties("Height") = 150 End With 'Создаем командную кнопку Dim myButton As Control Set myButton = myForm.Designer.Controls.Add("Forms.CommandButton.1") 'Присваиваем значения свойствам кнопки With myButton .Name = "myCommandButton" .Caption = "Новая кнопка" .Font.Size = 10 .Left = 100 .Top = 80 .Width = 100 .Height = 20 End With 'Записываем текст процедуры в модуль формы myForm Dim n As Integer With myForm.CodeModule n = .CountOfLines .InsertLines n + 1, "Private Sub myCommandButton_Click()" .InsertLines n + 2, "Dim myLabel As Object" .InsertLines n + 3, "Set myLabel = Me.Controls.Add(" & Chr(34) & "Forms.Label.1" & Chr(34) & ")" .InsertLines n + 4, "With myLabel" .InsertLines n + 5, ".Caption = " & Chr(34) & "Ура! Новая кнопка работает!" & Chr(34) .InsertLines n + 6, ".Font.Size = 10" .InsertLines n + 7, ".Left = 85" .InsertLines n + 8, ".Top = 30" .InsertLines n + 9, ".Width = 200" .InsertLines n + 10, ".Height = 20" .InsertLines n + 11, "End With" .InsertLines n + 12, "End Sub" End With 'Отображаем форму на экране UserForms.Add(myForm.Name).Show Set myForm = Nothing End Sub |
Пример удаления формы «myForm1», созданной процедурой «AddNewForm»:
|
1 2 3 4 5 |
Sub RemoveForm() With ThisWorkbook.VBProject.VBComponents .Remove .Item("myForm1") End With End Sub |
Вы можете скопировать этот код в стандартный модуль и проверить его работоспособность. После отображения динамически созданной пользовательской формы, нажмите кнопку «Новая кнопка». Результат работы кода:
Если хотите увидеть программно записанный код в модуле формы, кликните по форме правой кнопкой мыши и выберите в контекстном меню «View Code».
После удаления программно созданной формы, создать новую с тем же именем без перезагрузки Excel не получится. Без перезагрузки можно создавать любое количество динамических форм с разными именами.
Перезагрузка Excel – это закрытие всех открытых рабочих книг с последующим открытием любой книги.
Как скопировать пользовательскую форму из одной книги Excel в другую книгу с помощью кода VBA смотрите в статье Экспорт и импорт пользовательской формы.
Здравствуйте. У меня не работает. Ругается на
Dim myButton As Control. ПишетCompile Error - User-defined type not defined. Что не так?Здравствуйте, Максим!
Замените
Dim myButton As ControlнаDim myButton As Object. Скорее всего, у вас какая-то библиотека не подключена.После изменения на Object все равно ошибка
1004 Application-defined or object-defined error.Если у вас 64-битная версия Excel, попробуйте добавить ссылки на библиотеки, которых у вас нет (Tools – References…):
Если у вас 32-битная версия Excel, добавьте еще ссылки на библиотеки
Microsoft Windows Common ControlsиMicrosoft Windows Common Controls 2.У меня Office 2019 и нет таких библиотек. Думал будет все проще с макросом. У меня есть документ в Excel, и я хотел при его открытии сразу создавать пользовательскую форму. Так то оно все работает, если форма уже в нем создана. Но я решил заморочиться ))) и заморочился. К примеру, если я сделаю подобную форму, и все будет работать после добавления библиотек, придется ли другому пользователю добавлять сторонние библиотеки для того чтобы все работало?
Максим, необходимые библиотеки должны быть в списке ниже, их нужно только выделить галочками и нажать «OK». Если у вас нет в списке
Microsoft Windows Common Controls, значит у вас 64-битная версия Excel.Посмотрите у себя, есть ли у вас галочки на библиотеках Microsoft Excel Object Library, Microsoft Office Object Library и Microsoft Forms Object Library. Если нет, добавьте ссылки на них из списка ниже. Цифры у вас могут быть другие.
У другого пользователя ссылки на эти библиотеки тоже должны быть установлены.
Из того, что вы перечислили есть только Microsoft Excel Object Library, Microsoft Office Object Library и они включены.
Попробуйте с помощью строки поиска найти на диске «C:» файл FM20.DLL с библиотекой Microsoft Forms 2.0 Object Library. Если найдете, попробуйте добавить эту библиотеку в список References, нажав кнопку «Browse…» и выбрав файл FM20.DLL.
Если получится, библиотека Microsoft Forms 2.0 Object Library появится в списке, и ее можно будет выбрать.
Евгений, я добавил как вы сказали и 1004 Application-defined or object-defined error
То есть код не выполняется.
Максим, если в списке References появилась ссылка на библиотеку Microsoft Forms 2.0 Object Library и вы ее выбрали, то, в принципе, все должно заработать в 64-разрядной версии Excel. Проверьте разрядность вашей версии по инструкции Как узнать версию Excel? Попробуйте перезагрузить Excel (закрыть все файлы, потом открыть), если в течение дня этого не делали. Проверьте еще раз работоспособность кода из этой статьи.
Евгений, Excel 2019 64-разрядной версии, ссылка на Microsoft Forms 2.0 Object Library появилась и я ее выбрал, но к сожалению у меня код не работает. Хотя и Windows и Office лицензионные. Не знаю что уж тут к чему, но такие танцы с бубнами мне ни к чему. Спасибо за помощь и внимание к моей писанине. Я просто хотел сделать полностью автоматизированный документ чтобы упростить жизнь на работе себе и моим коллегам, но похоже что могу только ее усложнить ))).
Остальные статьи касательно Excel у вас написаны прекрасно и понятно. Я только увлекся изучением VBA скриптинга и они здорово помогают. Спасибо.
Евгений, решил эту проблему погуглив. Наверное можно добавить это сюда. В настройках самого Excel: Файл => Параметры => Центр управления безопасностью => Параметры центра управления безопасностью => Параметры макросов => Установить галочку на «Доверять доступ к объектной модели VBA». И как пишут там: «It doesn’t follow the workbook and you can’t change it in code». А это грустно. То есть я не смогу создать форму в коде, которая запустится у любого пользователя. Кстати, ваш код заработал после этой манипуляции.
Спасибо, Максим!
Я упустил из вида, что у многих пользователей выключена опция «Доверять доступ к объектной модели проектов VBA». Теперь добавил информацию об этом в статью.
Добрый вечер! Помнится, была тут у вас статья про то как форму превратить в код (её создания) для старых экселей. Не могли бы дать ссылку?)
Добрый вечер! Предположу, что эта: Размеры и расположение элементов управления.
Да, она, спасибо!
(Хотя проблему «Разрушительный сбой» уже решил))
Добрый день.
У меня возникает проблема с именем и событиями созданной формы. Если форму не удалять, то потом она работает после запуска из другой процедуры. Но, при создании из процедуры или ошибка 75 «Path/File accsess error из-за имени, или не реагирует на события.
Здравствуйте, Денис!
Без перезагрузки Excel нельзя повторно создать динамическую форму с таким же именем. Я добавил эту информацию в статью и вынес в отдельную процедуру код удаления формы. Попробуйте однократно создать форму и работать с ней по присвоенному имени, как с обычной пользовательской формой.
Пример рабочего кода для моей формы myForm1 после ее создания и закрытия крестиком:
Здравствуйте, Евгений! Имеется куча форм в формате FRM и FRX. Возможно ли групповое изменение их свойств — цвет фона формы и контролов, цвет шрифта и т.д. Ну или то же самое через VBE. Заранее благодарю. И отдельное спасибо за Ваш сайт!
Добрый день, Евгений!
Могу предложить такой алгоритм:
Циклом For Each… Next перебираете все файлы в папке. Если последние 4 знака имени файла = .frm, запускаете процедуру импорта, изменения свойств и экспорта формы.
Пример процедуры:
Теперь форма сохранена в папку с измененными свойствами.
Строка
On Error Resume Nextнужна для отладки, так как, если форма уже импортирована, будет генерироваться ошибка.Обсуждение закрыто.