Ввод данных в excel с помощью textbox и combobox
Очень часто при работе с формами(UserForm) приходится заполнять значениями КомбоБоксы(ComboBox) и ТекстБоксы(TextBox). Иногда однотипными данными. Или очистить их все после выполнения какого-либо действия. А может проверить, все ли элементы заполнены, прежде чем продолжать действие. Если КомбоБоксов/ТекстБоксов пару штучек - не проблема
и всех делов. А если их порядка двадцати? Или больше? Мало того, что писать это все долго, так еще и код растягивается, как портянка. Но это можно сделать гораздо проще и удобнее. Воспользоваться можно несколькими способами. Этот способ наиболее популярен, если необходимо произвести однотипные действия со всеми ТекстБоксами на форме:
Sub All_TextBoxes() Dim oControl As Control For Each oControl In UserForm1.Controls If TypeOf oControl Is MSForms.TextBox Then oControl.Value = "" Next oControl End Sub
В примере элементы формы очищаются, но в код можно подставить любое действие - добавление нового элемента списка(для ComboBox), поменять представление данных в TextBox, проверить наличие данных в проверяемом элементе и т.д. Тут уж зависит от поставленной задачи. Конечно, подобным способом можно проделать разные действия и с остальными элементами формы. Только надо будет заменить тип для проверки элементов:
Это не единственный способ проделывания однотипных действий с элементами формы. Код ниже использует имена элементов для обращения к ним:
Sub All_TextBoxes() Dim li As Long For li = 1 To 10 UserForm1.Controls("TextBox" & li).Value = li Next li End Sub
Недостаток данного метода: имена элементов должны строго соответствовать используемым в коде и лишь нумерация на конце имени должна различаться. За нумерацию отвечает переменная li и, конечно, цикл, в котором задается начальное и конечные значения. В примере ТекстБоксам с именами от "TextBox1" до "TextBox10" будут подставлены значения номеров самих ТекстБоксов. Но такой недостаток может быть очень полезным. Например, если необходимо проделать действия не над всеми ТекстБоксами, а лишь над некоторыми из них. Тогда все, что необходимо дать ТекстБоксам определенные имена с нумерацией("ToDB1","ToDB2" и т.д.) и в зависимости от имени можно проделывать различные действия: стирать значения, менять свойства элементов и т.д.
И есть еще один плюс такого подхода: когда необходимо заполнить значения ТекстБоксов значениями ячеек. Скажем надо заполнить 10 ТекстБоксов(с именами TextBox1 , TextBox2 , TextBox3 и т.д.) из ячеек диапазона A2:A11 листа с именем "Лист2"(т.е. из 10 ячеек, начиная с ячейки A2). Код будет выглядеть так:
Sub Fill_TextBoxes_FromCells() Dim li As Long For li = 1 To 10 UserForm1.Controls("TextBox" & li).Value = Sheets("Лист2").Range("A" & li).Value 'или применить Cells вместо Range 'UserForm1.Controls("TextBox" & li).Value = Sheets("Лист2").Cells(li, 1).Value Next li End Sub
Подробнее про обращение к диапазонам из VBA можно узнать из этой статьи: Как обратиться к диапазону из VBA
P.S. Небольшое дополнение: нужные элементы можно просто помещать внутрь объекта Frame. Тогда можно будет применять цикл исключительно по элементам внутри этого Frame(предположим, что Frame называется Frame1):
Sub All_TextBoxes_InFrame() Dim oControl As Control For Each oControl In Frame1.Controls If TypeOf oControl Is MSForms.TextBox Then oControl.Value = "" Next oControl End Sub
Кстати говоря, примерно так же можно перебрать элементы ActiveX не на форме, а на листе(вставляются на лист через вкладку Разработчик (Developer) -Вставить (Insert) -Элементы ActiveX (ActiveX Controls) ). Например, снимем флажки со всех CheckBox-ов:
Sub Off_ShapeCheckBoxes() Dim oControl 'цикл по всем объектам на листе For Each oControl In ActiveSheet.DrawingObjects 'определяем тип объекта - если это Элемент форм, то будет указание на конкретный тип If TypeName(oControl) = "CheckBox" Then oControl.Value = 0 End If Next oControl End Sub
В данном случае для определения конкретного типа контрола используется TypeName. В коде выше нам нужны CheckBox-ы и на них и проверяем. А вот список основных элементов форм, которые доступны по умолчанию для всех версий для вставки на лист:
Button - кнопка
DropDown - поле со списком
CheckBox - флажок
Spinner - счетчик
ListBox - список
OptionButton - радиокнопка
GroupBox - группа(рамка)
Label - надпись
ScrollBar - полоса прокрутки
Если совместить, то можно снять флажки со всех checkBox-ов - и ActiveX и Элементов форм:
Sub Off_AllCheckBoxes() Dim oControl 'цикл по всем объектам на листе For Each oControl In ActiveSheet.DrawingObjects 'определяем тип флажка: ActiveX или Элемент форм Select Case TypeName(oControl) Case "OLEObject" 'ActiveX - необходимо дополнительно проверить тип элемента If TypeOf oControl.Object Is MSForms.CheckBox Then oControl.Object.Value = 0 End If Case "CheckBox" 'Элемент форм CheckBox oControl.Value = 0 End Select Next oControl End Sub
В итоге, определяя конкретный тип элемента можно производить над ним нужные действия.
UserForm.TextBox – это элемент управления пользовательской формы, предназначенный для ввода, редактирования и вывода информации в текстовом формате.
Текстовое поле незаменимо, когда необходимо заполнить пользовательскую форму новой информацией. Например, это может быть ФИО нового сотрудника и его паспортные данные, сумма выручки за день, новый электронный адрес и многое-многое другое.
Если с помощью текстового поля требуется вводить заранее известную информацию, элементы которой можно сгруппировать в список, то в этом случае удобнее вместо элемента управления TextBox использовать ListBox или ComboBox.
Вводить информацию в TextBox на открытой форме можно не только с помощью клавиатуры, но и вставкой текста из буфера обмена. Когда текстовое поле применяется для ввода пароля, есть возможность отобразить все его знаки замещающим символом, например, звездочкой.
Иногда, если на пользовательской форме используется только одно текстовое поле и кнопка «OK» (или кнопки «OK» и «Отмена»), для ввода информации такую форму можно заменить функцией InputBox, а для вывода информации, когда не требуется редактирование отображаемого текста, – функцией MsgBox.
Свойства текстового поля
Свойство | Описание |
---|---|
AutoSize* | Автоподбор размера текстового поля. True – размер автоматически подстраивается под длину размещаемой строки. False – размер элемента управления определяется свойствами Width и Height. |
AutoTab | Включение автоматической табуляции – передачи фокуса следующему элементу управления при достижении максимального числа символов при значениях свойства MaxLenght > 0. True – автоматическая табуляция включена, False – выключена. |
ControlSource | Ссылка на источник данных для поля TextBox. |
ControlTipText | Текст всплывающей подсказки при наведении курсора на TextBox. |
Enabled | Возможность ввода, редактирования, копирования, вставки, удаления текста. True – все перечисленные опции включены, False – выключены (цвет текста в поле становится серым). |
Font | Шрифт, начертание и размер текста в поле. |
Height | Высота текстового поля. |
Left | Расстояние от левого края внутренней границы пользовательской формы до левого края текстового поля. |
Locked | Запрет ввода, редактирования, удаления текста. True – перечисленные опции запрещены (разрешено выделение и копирование текста), False – перечисленные опции разрешены. |
MaxLenght | Максимальная длина строки. По умолчанию – 0, что означает – ограничений нет. |
Multiline | Устанавливает многострочный (True) или однострочный (False) режим ввода-вывода текста. |
PasswordChar | Задает символ, который будет отображаться при вводе знаков пароля. |
TabIndex | Определяет позицию элемента управления в очереди на получение фокуса при табуляции, вызываемой свойством AutoTab или нажатием клавиш «Tab», «Enter». Отсчет начинается с 0. |
Text** | Текстовое содержимое (значение) поля (=Value). |
TextAlign | Выравнивание текста: 1 (fmTextAlignLeft) – по левому краю, 2 (fmTextAlignCenter) – по центру, 3 (fmTextAlignRight) – по правому краю. |
Top | Расстояние от верхнего края внутренней границы пользовательской формы до верхнего края текстового поля. |
Value** | Текстовое содержимое (значение) поля (=Text). |
Visible | Видимость текстового поля. True – TextBox отображается на пользовательской форме, False – TextBox скрыт. |
Width | Ширина текстового поля. |
WordWrap | Актуально при Multiline = True. Переход на новую строку при достижении границы текстового поля. True – переход включен, False – переход выключен. |
* При использовании свойства AutoSize в значении True следует учитывать, что автоматическое изменение размеров поля при вводе текста может нарушить дизайн пользовательской формы.
** Text и Value — это два обозначения одного и того же свойства. Если в окне Properties элемента управления TextBox в поле свойства Text начать ввод строки, в поле Value ввод будет дублироваться. И наоборот, при вводе текста в поле свойства Value, дублирование произойдет в поле Text.
В таблице перечислены только основные, часто используемые свойства текстового поля. Все доступные свойства отображены в окне Properties элемента управления TextBox.
Свойства BackColor, BackStyle, BorderColor, BorderStyle отвечают за внешнее оформление текстового поля и его границ. Попробуйте выбирать доступные значения этих свойств в окне Properties, наблюдая за изменениями внешнего вида элемента управления TextBox на проекте пользовательской формы.
Привязка текстового поля к ячейке
Привязать элемент управления TextBox к ячейке на рабочем листе можно двумя способами.
1. В окне Properties элемента управления TextBox в поле свойства ControlSource необходимо указать адрес ячейки:
ComboBox представляет из себя комбинацию двух элементов управления: текстового поля (TextBox) и списка (ListBox), поэтому его еще называют «комбинированным списком» или «полем со списком». Также ComboBox сочетает в себе свойства этих двух элементов управления.
Изначально комбинированный список прорисовывается на форме в виде текстового поля с кнопкой для отображения раскрывающегося списка. Далее по тексту будем использовать слово «поле» в значении текстового поля в составе элемента управления ComboBox, а словосочетание «раскрывающийся список» – в значении списка в составе элемента управления ComboBox.
Поле со списком используется в тех случаях, когда необходимо добавить в форму информацию, которая заранее известна, а ее отдельные позиции можно сгруппировать в список, а также для ручного ввода с клавиатуры или вставки из буфера обмена, если необходимое значение в списке отсутствует.
Элемент управления ComboBox незаменим при больших списках. При списках из нескольких позиций его можно заменить на ListBox, который отображает позиции для выбора сразу после загрузки формы, не требуя дополнительных действий от пользователя.
Свойства поля со списком
Свойство | Описание |
---|---|
AutoSize | Автоподбор размера комбинированного поля. True – размер автоматически подстраивается под длину выбранной или введенной строки. False – размер элемента управления определяется свойствами Width и Height. |
AutoTab | Включение автоматической табуляции – передачи фокуса следующему элементу управления при достижении максимального числа символов при значениях свойства MaxLenght > 0. True – автоматическая табуляция включена, False – выключена. |
ColumnCount | Указывает количество столбцов в раскрывающемся списке. Значение по умолчанию = 1. |
ColumnHeads | Добавляет строку заголовков в раскрывающийся список. True – заголовки столбцов включены, False – заголовки столбцов выключены. Значение по умолчанию = False. |
ColumnWidths | Ширина столбцов в раскрывающемся списке. Значения для нескольких столбцов указываются в одну строку через точку с запятой (;). |
ControlSource | Ссылка на ячейку для ее привязки к элементу управления ComboBox. |
ControlTipText | Текст всплывающей подсказки при наведении курсора на элемент управления. |
Enabled | Доступ пользователя к полю и раскрывающемуся списку. True – доступ разрешен, False – доступ запрещен*. Значение по умолчанию = True. |
Font | Шрифт, начертание и размер текста в поле. |
Height | Высота элемента управления ComboBox. |
Left | Расстояние от левого края внутренней границы пользовательской формы до левого края комбинированного списка. |
List | Позволяет заполнить ComboBox данными из одномерного или двухмерного массива, а также обращаться к отдельным элементам раскрывающегося списка по индексам для записи и чтения. |
ListIndex | Номер выбранной пользователем строки в раскрывающемся списке. Нумерация начинается с нуля. Если ничего не выбрано, ListIndex = -1. |
ListRows | Количество видимых строк в раскрытом списке. Если общее количество строк больше ListRows, появляется полоса прокрутки. Значение по умолчанию = 8. |
Locked | Запрет на отображение раскрывающегося списка, ввод и редактирование данных в поле. True – ввод и редактирование запрещены**, False – ввод и редактирование разрешены. Значение по умолчанию = False. |
MatchRequired | Задает проверку вводимых в поле строк с элементами списка. True – проверка включена (допускается ввод только строк, совпадающих с элементами списка), False – проверка выключена (допускается ввод любых строк). Значение по умолчанию = False. |
MaxLenght | Максимальная длина строки в поле. Значение по умолчанию = 0, что означает – ограничений нет. |
RowSource | Источник строк для раскрывающегося списка (адрес диапазона на рабочем листе Excel). |
TabIndex | Целое число, определяющее позицию элемента управления в очереди на получение фокуса при табуляции. Отсчет начинается с 0. |
Text | Текстовое содержимое (значение) поля (=Value). |
TextAlign | Выравнивание текста в поле: 1 (fmTextAlignLeft) – по левому краю, 2 (fmTextAlignCenter) – по центру, 3 (fmTextAlignRight) – по правому краю. |
Top | Расстояние от верхнего края внутренней границы пользовательской формы до верхнего края комбинированного списка. |
Value | Текстовое содержимое (значение) поля (=Text). |
Visible | Видимость поля со списком. True – ComboBox отображается на пользовательской форме, False – ComboBox скрыт. |
Width | Ширина элемента управления. |
* При Enabled в значении False пользователь не может раскрывать список, а также вводить или редактировать данные в поле.
** Для элемента управления ComboBox действие свойства Locked в значении True аналогично действию свойства Enabled в значении False.
В таблице перечислены только основные, часто используемые свойства поля со списком. Еще больше доступных свойств отображено в окне Properties элемента управления ComboBox, а все методы, события и свойства – в окне Object Browser.
Вызывается Object Browser нажатием клавиши «F2». Слева выберите объект ComboBox, а справа смотрите его методы, события и свойства.
Свойства BackColor, BackStyle, BorderColor, BorderStyle отвечают за внешнее оформление комбинированного списка и его границ. Попробуйте выбирать доступные значения этих свойств в окне Properties, наблюдая за изменениями внешнего вида элемента управления ComboBox на проекте пользовательской формы.
Способы заполнения ComboBox
Используйте метод AddItem для загрузки элементов в поле со списком по одному:
Создайте пользовательскую форму UserForm1 и разместите на ней поле со списком ComboBox1. Используйте метод AddItem для заполнения элемента управления значениями:
Скопируйте код и запустите его выполнение, на открывшейся форме раскройте поле со списком, в результате увидите, что элемент управления ComboBox1 заполнен соответствующими значениями:
Заполнение ComboBox значениями из массива
Для заполнения элемента управления ComboBox значениями из массива будем использовать свойство поля со списком List и функцию Array:
Результат выполнения кода будет таким же, как и на предыдущем изображении.
Таким же образом можно использовать не только функцию Array, но и переменную массива, предварительно объявленную и заполненную значениями:
Заполнение ComboBox значениями из ячеек
Для заполнения поля со списком значениями из диапазона ячеек рабочего листа будем использовать свойство комбинированного списка RowSource, предварительно заполнив диапазон «A1:A5» активного листа уже известными значениями:
Чтобы присвоить элементу управления ComboBox значения из диапазона ячеек любого рабочего листа, добавьте ссылку на него перед наименованием диапазона, например, замените «A1:A5» на «Лист1!A1:A5», и поле со списком будет заполнено значениями ячеек «A1:A5», расположенных на листе с именем «Лист1». Имя листа берется из наименования ярлыка.
Очистка ComboBox от значений
Очистить ComboBox можно как от всех значений сразу, так и удалить один или несколько значений (по одному) из списка.
Очистка ComboBox от всего списка значений:
Очистка ComboBox от одного значения:
где Index — порядковый номер элемента в списке ComboBox, нумерация начинается с нуля.
Автоматическое заполнение ComboBox
Предполагается, что, при открытии пользовательской формы или рабочего листа с ComboBox, поле со списком должно быть уже заполнено значениями. Как это сделать?
Заполнение ComboBox на форме
Автоматическое заполнение элемента управления ComboBox на пользовательской форме осуществляется с помощью процедуры UserForm_Initialize, размещенной в модуле этой формы:
Заполнение ComboBox на листе
Автоматическое заполнение элемента управления ComboBox из коллекции «Элементы ActiveX» на рабочем листе осуществляется с помощью процедуры Workbook_Open, размещенной в модуле «ЭтаКнига»:
Более подробно о заполнении данными ComboBox можно прочитать в статье о заполнении ListBox, так как оба эти элемента управления используют одинаковые способы заполнения.
Иногда возникает необходимость заполнения элементов управления ListBox и ComboBox уникальными значениями из диапазона ячеек с повторяющимся содержимым. Смотрите, как отсортировать уникальные элементы из списка с помощью объектов Collection и Dictionary.
Вы можете скачать файл Excel с представленными выше примерами. Файл упакован в ZIP-архив. Для проверки работоспособности кода, непосредственно в редакторе VBA помещайте курсор внутри тела каждой процедуры и нажимайте кнопку «Run Sub».
12 комментариев для “VBA Excel. ComboBox — заполнение поля со списком”
Не получился ни один из предложенных вариантов( С чем это может быть связано?
Спасибо за примеры, на одном все получилось. Чуток лишнее удалил и все ОК.
Потому что пример привязан к тестовому объекту.
Я тоже не мог понять, при отсутствии навыков.
Но вот четко к полю привязку по примеру удалось сделать.
Аналогично в powerpoint вставка поля со списком
Доброго дня!
Я новичок в Экселе. Пробую сделать инструмент для ведения базы данных объектов.
Если не трудно, помогите, пожалуйста, разобраться как мне воплотить в жизнь задуманное.
1. Появилась мысль вывод данных по объекту сделать через UserForm. В выпадающем списке (диапазон объекты) выбирается объект и при нажатии "Вывести данные по объекту",соответственно, данные объекта выводятся в соответствующие ячейки UserForm, а после нажатия кнопки "Вывести данные в таблицу и закрыть", данные выводятся в таблицу и UserForm закрывается.
2. Ввод нового объекта тоже делается через UserForm, при этом:
- При вводе нового объекта необходима проверка на совпадение наименования вводимого объкта с уже существующим с таким же названием объектом в БД;
- Данные по новому объекту должны вставляться в первую попавшуюся пустую строку диапазона АВ7:АВ108, не зависимо от местоположения этой пустой строки (так как некоторые объекты со временем будут переноситься из БД в архив и будут возникать пустые строки)
3. Как организовать перенос данных объекта с листа "ОБЪЕКТЫ-ОСНОВНАЯ ТАБЛИЦА" на лист "ОБЪЕКТЫ-ОСНОВНАЯ Архив" через UserForm с последующим удалением с листа "ОБЪЕКТЫ-ОСНОВНАЯ ТАБЛИЦА" ? Причём на листе "ОБЪЕКТЫ-ОСНОВНАЯ Архив" данные по переносимому объекту должны вставляться в первую попавшуюся пустую строку диапазона D7 : D108, не зависимо от местоположения этой пустой строки (Тут тоже возникнет ситуация, когда некоторые объекты со временем будут удаляться и будут возникать пустые строки).
4. Как организовать удаление данных объекта с листа "ОБЪЕКТЫ-ОСНОВНАЯ Архив" через UserForm?
Мне главное - понять принцип, как это работает. Потом я уже сам попробую допинать.
Доброго дня!
Я новичок в Экселе. Пробую сделать инструмент для ведения базы данных объектов.
Если не трудно, помогите, пожалуйста, разобраться как мне воплотить в жизнь задуманное.
1. Появилась мысль вывод данных по объекту сделать через UserForm. В выпадающем списке (диапазон объекты) выбирается объект и при нажатии "Вывести данные по объекту",соответственно, данные объекта выводятся в соответствующие ячейки UserForm, а после нажатия кнопки "Вывести данные в таблицу и закрыть", данные выводятся в таблицу и UserForm закрывается.
2. Ввод нового объекта тоже делается через UserForm, при этом:
- При вводе нового объекта необходима проверка на совпадение наименования вводимого объкта с уже существующим с таким же названием объектом в БД;
- Данные по новому объекту должны вставляться в первую попавшуюся пустую строку диапазона АВ7:АВ108, не зависимо от местоположения этой пустой строки (так как некоторые объекты со временем будут переноситься из БД в архив и будут возникать пустые строки)
3. Как организовать перенос данных объекта с листа "ОБЪЕКТЫ-ОСНОВНАЯ ТАБЛИЦА" на лист "ОБЪЕКТЫ-ОСНОВНАЯ Архив" через UserForm с последующим удалением с листа "ОБЪЕКТЫ-ОСНОВНАЯ ТАБЛИЦА" ? Причём на листе "ОБЪЕКТЫ-ОСНОВНАЯ Архив" данные по переносимому объекту должны вставляться в первую попавшуюся пустую строку диапазона D7 : D108, не зависимо от местоположения этой пустой строки (Тут тоже возникнет ситуация, когда некоторые объекты со временем будут удаляться и будут возникать пустые строки).
4. Как организовать удаление данных объекта с листа "ОБЪЕКТЫ-ОСНОВНАЯ Архив" через UserForm?
Мне главное - понять принцип, как это работает. Потом я уже сам попробую допинать.
Мне главное - понять принцип, как это работает. Потом я уже сам попробую допинать.
Заранее спасибо! Автор - ILIA-PLC
Дата добавления - 30.03.2020 в 17:34
Читайте также: