Создание пользовательской автоформы с помощью кода VBA и ее преимущества перед встроенной автоформой Excel. Отображение встроенной автоформы.
Встроенная автоформа Excel
Чтобы использовать в Excel встроенную автоформу, необходимо добавить кнопку ее вызова на панель инструментов. Как это сделать, смотрите в статье «Умная таблица» в Excel.
Встроенная автоформа работает с любой таблицей Excel, а не только с «умной». Главное, чтобы приложение могло интерпретировать диапазон как таблицу. Для этого необходимо выделить заголовки столбцов отличающимся от тела таблицы форматированием (цветом, курсивом, полужирным начертанием) и заполнить хотя бы одну строку.
Чтобы отобразить встроенную автоформу Excel, выберите ячейку внутри таблицы и нажмите кнопку вызова автоформы на панели инструментов.
Главный недостаток встроенной автоформы Excel заключается в невозможности использования раскрывающихся списков для заполнения ее полей. Для таблицы на изображении выше мы создадим с помощью кода VBA Excel пользовательскую автоформу с текстовыми полями и раскрывающимися списками.
Создание пользовательской автоформы
Рабочая таблица и списки
Наименования полей и наборы данных для раскрывающихся списков при создании пользовательской автоформы будем брать с листа «Списки».
Все изменения в наименования полей и наборы данных также вносятся на листе «Списки». На лист «Таблица» наименования граф копируются с листа «Списки» формулами, строка заголовков закреплена и добавлена кнопка «Новая запись» для вызова пользовательской автоформы.
В коде VBA, создающем автоформу, используются имена листов: Лист1 («Таблица») и Лист2 («Списки»). Благодаря этому, можно переименовывать ярлычки листов без внесения изменений в код. Кнопка «Новая запись» добавлена на рабочий лист из коллекции «Элементы ActiveX».
Создание проекта формы
Добавьте в проект VBA пользовательскую форму UserForm1 и добавьте на нее кнопки CommandButton1 и CommandButton2:
Размеры формы и кнопок не имеют значения, мы будем задавать их программно.
Код инициализации автоформы
В процессе инициализации формы мы будем добавлять элементы управления, их расположение и размеры. Наименования полей таблицы будут записаны в параметры Caption элементов Label.
Поля таблицы для заполнения в пользовательской автоформе будут представлены элементами TextBox и ComboBox. Если в таблице на листе «Списки» под наименованием графы есть данные для раскрывающегося списка, на форму добавляется ComboBox, если нет – TextBox.
Процедура размещается в модуле пользовательской формы UserForm1.
|
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 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
Private Sub UserForm_Initialize() 'Включаем обработчик ошибок и объявляем переменные On Error GoTo TxtEr Dim myArr As Variant, n1 As Byte, n2 As Long, i1 As Byte, i2 As Long, myCont1 As Control, myCont2 As Control 'Копируем в массив myArr данные из таблицы на листе «Списки» myArr = Лист2.Cells(1, 1).CurrentRegion 'Определяем количество строк n1 = UBound(myArr, 1) 'Определяем количество столбцов n2 = UBound(myArr, 2) 'Если в массиве 1 строка, нет данных для раскрывающихся списков, полезнее будет встроенная автоформа If n1 = 1 Then MsgBox "У вас нет данных для раскрывающихся списков, воспользуйтесь встроенной автоформой Excel!" Exit Sub End If 'Ограничиваем пользовательскую автоформу 12 полями (количество можно увеличить) If n2 > 12 Then MsgBox "Количество столбцов превышает 12!" Exit Sub End If 'Проходим циклом по условным столбцам таблицы с листа «Списки» в массиве For i1 = 1 To n2 'Добавляем элемент управления Label и задаем необходимые параметры Set myCont1 = Me.Controls.Add("Forms.Label.1") With myCont1 .Caption = myArr(1, i1) .Width = 70 .Height = 18 .Left = 20 .Top = 30 * i1 End With 'Если второй элемент в столбце не пустой, добавляем ComboBox с именем "Control№"+i1 If myArr(2, i1) <> Empty Then Set myCont2 = Me.Controls.Add("Forms.ComboBox.1", "Control№" & i1) 'Заполняем ComboBox данными из списка под названием графы For i2 = 2 To n1 If myArr(i2, i1) = Empty Then Exit For myCont2.AddItem myArr(i2, i1) Next 'Иначе (если второй элемент в столбце пустой) добавляем TextBox с именем "Control№"+i1 Else Set myCont2 = Me.Controls.Add("Forms.TextBox.1", "Control№" & i1) End If 'Задаем необходимые параметры элементу управления myCont2 With myCont2 'Если наименование графы - «Дата», элемент управления заполняется текущей датой и временем If myCont1.Caption = "Дата" Then .Text = Format(Now, "General Date") .Width = 136 .Height = 18 .Left = 90 .Top = 30 * i1 End With Next 'Задаем параметры пользовательской автоформы With Me .Height = myCont1.Top + 100 .Width = 260 .Caption = "Новая запись в таблицу" End With 'Задаем параметры кнопки «OK» With CommandButton1 .Top = myCont1.Top + 40 .Left = 60 .Width = 60 .Height = 20 .Caption = "OK" End With 'Задаем параметры кнопки «Отмена» With CommandButton2 .Top = myCont1.Top + 40 .Left = 130 .Width = 60 .Height = 20 .Caption = "Отмена" End With 'Выходим из процедуры, если не произошла ошибка Exit Sub TxtEr: 'Если произошла ошибка, выводим сообщение с ее описанием MsgBox "Произошла ошибка: " & Err.Description End Sub |
Элементам управления формы, по желанию, можно добавить наименования и размеры шрифтов.
Процедуры для кнопок
Процедуру для кнопки рабочего листа «Новая запись», отображающую пользовательскую форму на экране, размещаем в модуле листа «Таблица»:
|
1 2 3 |
Private Sub CommandButton1_Click() UserForm1.Show End Sub |
Код кнопки пользовательской формы CommandButton1 («OK»), записывающий новую строку в таблицу, размещаем в модуле формы:
|
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 |
Private Sub CommandButton1_Click() 'Включаем обработчик ошибок и объявляем переменные On Error GoTo TxtEr Dim n3 As Byte, n4 As Byte, i As Byte 'Определяем количество строк и столбцов в таблице на листе «Таблица» With Лист1.Cells(1, 1).CurrentRegion n3 = .Columns.Count n4 = .Rows.Count End With 'Заполняем новую строку таблицы данными из пользовательской автоформы With Лист1 For i = 1 To n3 .Cells(n4 + 1, i) = Controls("Control№" & i).Text Next 'Добавляем границы ячейкам новой строки .Range(Cells(n4 + 1, 1), Cells(n4 + 1, n3)).Borders.LineStyle = True End With 'Закрываем форму Unload Me 'Выходим из процедуры, если не произошла ошибка Exit Sub TxtEr: 'Если произошла ошибка, выводим сообщение с ее описанием MsgBox "Произошла ошибка: " & Err.Description End Sub |
Процедуру для кнопки пользовательской формы CommandButton2 («Отмена»), закрывающую форму, размещаем в модуле формы:
|
1 2 3 |
Private Sub CommandButton2_Click() Unload Me End Sub |