Создание списка в vba в excel

Обновлено: 02.07.2024

Приведённые практические работы по программированию могут быть использованы при изучении элементов выбора Visual Basic. Списки целесообразно изучать после знакомства с переключателями (OptionButton) и флажками (CheckBox).

При объяснении нового материала удобно использовать распечатки с изложением теоретического материала и примерами программ, которые выдаются каждому студенту (ученику), либо методические пособия (мы используем авторское пособие “Практикум по решению задач в среде Visual Basic”, часть 1, 2).

Приведённые практические работы рассчитаны на 3 занятия по 2 часа. Задания самостоятельной работы можно использовать в качестве домашних заданий.

Теоретический материал

комбинированный список ComboBox;
список ListBox.

1. Комбинированный список ComboBox

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

Свойство Style задаёт внешний вид комбинированного списка (по умолчанию оно равно 0 - элемент ComboBox отображается в виде текстового поля со стрелкой справа, позволяющей развернуть весь список и выбрать требуемое значение). Если Style = 1, то постоянно отображается весь список (или список с полосой прокрутки).

Основные свойства ComboBox

0 - Dropdown Combo (раскрывающийся комбинированный список);

1 - Simple Combo (простой комбинированный список);

Для добавления нового элемента в список используется метод AddItem.

Пример: Combo1.AddItem “Пение” - добавить в список Combo1 слово Пение.

Добавляемые в список элементы имеют тип String; [index] - порядковый номер в списке, под которым будет находиться новый элемент (необязательный параметр).

Добавить элементы в комбинированный список можно также на этапе разработки с помощью свойства List. Для добавления очередного элемента в список нужно ввести этот элемент в свойстве List и нажать комбинацию клавиш Ctrl + Enter.

2. Список ListBox

Данный элемент применяется в том случае, когда пользователю необходимо выбрать один элемент из имеющегося списка для выполнения определенных действий. Объекты ListBox используются обычно в сочетании с управляющими кнопками, которые позволяют обрабатывать элементы списка (добавлять, удалять и т. д.).

В отличие от ComboBox список ListBox можно создать многоколончатым и осуществлять выбор нескольких элементов одновременно.

щелчок мыши при нажатой клавише Shift позволяет выделить несколько подряд расположенных элементов;
щелчок мыши при нажатой клавише Ctrl даёт возможность выделять группу несмежных элементов списка.

Основные свойства элемента ListBox

0 - одноколончатый список с вертикальной прокруткой;

1 - одноколончатый список с горизонтальной прокруткой;

0 - None (обычный список);

1 -Simple (простой множественный выбор - щелчок мыши или нажатие Пробел выделяет очередной элемент или снимает выделение);

Методы ListBox аналогичны методам ComboBox.

Практическая работа 1. Проект “Результаты сессии” <Приложение 1>

Задание: создать проект, содержащий 3 списка: список студентов группы; список студентов, получивших зачёт и список неаттестованных. Двойным щелчком мыши в списке студентов (List1) выбирается фамилия и добавляется в список “Зачёт” (List2). Двойным щелчком в списке “Зачёт” фамилия возвращается назад. Кнопкой “Добавить в список” фамилия, введённая в текстовое поле Text1, заносится в список List1. Кнопка “н/а” заносит выбранную фамилию из списка студентов в список List3 ( н/а).

Private Sub Form_Load() ‘загрузка формы

List1.AddItem "Чесноков": List1.AddItem "Симонов"

List1.AddItem "Трушков": List1.AddItem "Морозова"

Private Sub List1_DblClick() ‘перенести из списка в зачёт

List1.RemoveItem List1.ListIndex ‘удалить из списка

Private Sub List2_DblClick() ‘перенести назад в Список из Зачёт

Private Sub List3_DblClick() ‘перенести назад в список из н/а

Private Sub Command1_Click() ‘добавить в список из текст. поля

Private Sub Command3_Click() ‘добавить в н/а

List1.RemoveItem List1.ListIndex ‘удалить из списка

Добавьте в проект список оценок (2, 3, 4, 5) – List4.
Измените проект таким образом, чтобы можно было добавлять в результаты фамилию и оценку ученика.
Создайте кнопку “Очистить результаты”.

Примерный вид формы показан на рисунке.

Практическая работа 2. Проект “Переводчик” <Приложение 2>

Задание: создать проект для проверки знания иностранных слов (англо-русский и русско-английский переводчик).

Открыть в меню Проект – Компоненты вкладку Управление (Control);
Установить флажок MicrosoftMultimedia Control 6.0;
На панели элементов управления появится новый значок MMControl.

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

Ris0.jpg – рисунок к первому слову списка;

Ris1.jpg – рисунок ко второму слову списка и т.д.

Для определения имени файла рисунка в проекте используем переменную Path:

Функция Mid используется для того, чтобы убрать пробел перед числом в имени файла, т.к. функция Str(n) для положительного числа формирует строку с пробелом перед числом. Таким образом, получаем неверное имя файла Ris 0.jpg, вместо Ris0.jpg.

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

1) В процедуре загрузки формы нужно заполнить списки русских и английских слов.

Dim n, k, z, p As Integer, Path As String

Private Sub Form_Load()

Combo1.AddItem "собака": Combo1.AddItem "дельфин"

Combo1.AddItem "осёл": Combo1.AddItem "красный"

Combo1.AddItem "зеленый": Combo2.AddItem "Dog"

Combo2.AddItem "Dolphin": Combo2.AddItem "Donkey"

Combo2.AddItem "Red": Combo2.AddItem "Green"

z = 0 ‘количество вопросов

2) При выборе кнопки “Перевод рус/англ” должно появляться русское слово. Для данного слова нужно найти перевод в списке английских слов. Список русских слов при этом должен скрываться.

Private Sub Command5_Click() 'Перевод рус/англ

p = 1 ‘признак перевода – рус/англ

Combo1.Visible = False ‘скрыть список русских слов

Combo2.Visible = True ‘показать список англ. слов

Label1.Caption = "Русское слово"

Timer1.Enabled = True ‘включить таймер

3) В процедуре таймера случайным образом определяем индекс слова в списке слов. Если p=1, то было выбрано направление перевода рус/англ., поэтому слово выбирается из списка русских слов Combo1.

Private Sub Timer1_Timer()

z = z + 1 ‘подсчёт кол. вопросов

n = Int(Rnd * 4) ‘случайный выбор индекса слова в списке слов

If p = 1 Then Text1.Text = Combo1.List(n)

4) Для выбора перевода заданного русского слова пользователь делает щелчок по списку английских слов. Если индекс исходного русского слова n совпадает с индексом слова-перевода, выбранного пользователем, то ответ верный.

Private Sub Combo2_Click() 'список англ. слов

If n = Combo2.ListIndex Then 'если ответ верный

Image1.Visible = True ‘показать рисунок

MMControl1.FileName = "da.wav" ‘загрузить звуковой файл

Image1.Picture = LoadPicture(Path) ‘загрузить файл с соответствующим рисунком

Else 'если ответ неверный

Создайте процедуру кнопки “Перевод англ/рус”.
Добавьте кнопку Сброс.
Подберите 2 звуковых файла для подтверждения верного и неверного ответа, подключите эти файлы к проекту.
Создайте заставку к проекту.

Создать проект “Выбор шрифта”, позволяющий выбрать с помощью списков тип шрифта, размер, начертание (свойства): жирный, подчёркнутый, курсив. Выбранные параметры должны применяться к метке Образец.

Свойства шрифта в метке задаются следующими командами:

Label1.Font.Italic = True – установить курсив

Label1.Font.Bold = False – отменить жирный шрифт

Label1.Font.Underline = True – установить подчёркнутый шрифт

Label1.Font = Combo1.Text – применить к метке тип шрифта, выбранный в комбинированном списке 1

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

Использование полос прокрутки уменьшает преимущество ListBox перед элементом управления ComboBox, которое заключается в том, что при открытии формы все позиции для выбора на виду без дополнительных действий со стороны пользователя. При выборе информации из большого списка удобнее использовать ComboBox.

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

Свойства списка

Свойство	Описание
ColumnCount	Указывает количество столбцов в списке. Значение по умолчанию = 1.
ColumnHeads	Добавляет строку заголовков в ListBox. True – заголовки столбцов включены, False – заголовки столбцов выключены. Значение по умолчанию = False.
ColumnWidths	Ширина столбцов. Значения для нескольких столбцов указываются в одну строку через точку с запятой (;).
ControlSource	Ссылка на ячейку для ее привязки к элементу управления ListBox.
ControlTipText	Текст всплывающей подсказки при наведении курсора на ListBox.
Enabled	Возможность выбора элементов списка. True – выбор включен, False – выключен*. Значение по умолчанию = True.
Font	Шрифт, начертание и размер текста в списке.
Height	Высота элемента управления ListBox.
Left	Расстояние от левого края внутренней границы пользовательской формы до левого края элемента управления ListBox.
List	Позволяет заполнить список данными из одномерного или двухмерного массива, а также обращаться к отдельным элементам списка по индексам для записи и чтения.
ListIndex	Номер выбранной пользователем строки. Нумерация начинается с нуля. Если ничего не выбрано, ListIndex = -1.
Locked	Запрет возможности выбора элементов списка. True – выбор запрещен**, False – выбор разрешен. Значение по умолчанию = False.
MultiSelect***	Определяет возможность однострочного или многострочного выбора. 0 (fmMultiSelectSingle) – однострочный выбор, 1 (fmMultiSelectMulti) и 2 (fmMultiSelectExtended) – многострочный выбор.
RowSource	Источник строк для элемента управления ListBox (адрес диапазона на рабочем листе Excel).
TabIndex	Целое число, определяющее позицию элемента управления в очереди на получение фокуса при табуляции. Отсчет начинается с 0.
Text	Текстовое содержимое выбранной строки списка (из первого столбца при ColumnCount > 1). Тип данных String, значение по умолчанию = пустая строка.
TextAlign	Выравнивание текста: 1 (fmTextAlignLeft) – по левому краю, 2 (fmTextAlignCenter) – по центру, 3 (fmTextAlignRight) – по правому краю.
Top	Расстояние от верхнего края внутренней границы пользовательской формы до верхнего края элемента управления ListBox.
Value	Значение выбранной строки списка (из первого столбца при ColumnCount > 1). Value – свойство списка по умолчанию. Тип данных Variant, значение по умолчанию = Null.
Visible	Видимость списка. True – ListBox отображается на пользовательской форме, False – ListBox скрыт.
Width	Ширина элемента управления.

* При Enabled в значении False возможен только вывод информации в список для просмотра.
** Для элемента управления ListBox действие свойства Locked в значении True аналогично действию свойства Enabled в значении False.
*** Если включен многострочный выбор, свойства Text и Value всегда возвращают значения по умолчанию (пустая строка и Null).

В таблице перечислены только основные, часто используемые свойства списка. Еще больше доступных свойств отображено в окне Properties элемента управления ListBox, а все методы, события и свойства – в окне Object Browser.

Вызывается Object Browser нажатием клавиши «F2». Слева выберите объект ListBox, а справа смотрите его методы, события и свойства.

Свойства BackColor, BorderColor, BorderStyle отвечают за внешнее оформление списка и его границ. Попробуйте выбирать доступные значения этих свойств в окне Properties, наблюдая за изменениями внешнего вида элемента управления ListBox на проекте пользовательской формы.

Способы заполнения ListBox

Используйте метод AddItem для загрузки элементов в список по одному:

Окно списка - это один из инструментов пользовательской формы в VBA. Как подсказывает само название, список будет содержать весь список значений, данных пользователем. В поле списка VBA входит пользовательская форма. В этой статье мы увидим, как создать список и как хранить значения в списке в Excel VBA.

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

Мы также можем нарисовать список в обычном листе Excel.

Как создать список в Excel?

Предположим, у вас есть список месяца в одном из листов Excel.

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

Шаг 1: Перейдите на вкладку разработчика. Под разработчиком, вкладка Перейти к опции Вставка и под вставкой, опция Перейти к контролю формы, под управлением формы выберите Список.

Шаг 2: После выбора поля списка нарисуйте поле списка на листе Excel.

Шаг 3: Щелкните правой кнопкой мыши на вставленном списке и выберите « Управление форматами» .

Шаг 4: В разделе «Управление форматами» перейдите в « Управление» . Выберите диапазон ввода в качестве диапазона названий месяцев и задайте ссылку на ячейку D1, а затем нажмите « ОК» .

Шаг 5: У нас будет список месяца в списке.

Шаг 6: Выберите любой из элементов в списке и увидите изменение в ячейке D1.

Итак, мы получили 3 в результате в ячейке D1, потому что Мар - третий элемент в списке.

Как создать список в VBA?

Создать окно списка не так просто, как мы видели в приведенном выше примере, вам необходимо иметь значительные знания о макросах Excel VBA.

Вы можете скачать этот шаблон Excel со списком VBA здесь - Шаблон Excel со списком VBA

Окно списка VBA - Пример № 1

Понимание списка VBA.

Вы можете рассмотреть следующий пример для лучшего понимания VBA List Box.

Выполните следующие шаги, чтобы создать список в VBA.

Шаг 1: В окне Visual Basic Вставьте форму пользователя.

Шаг 2: Как только вы вставите форму пользователя, вы увидите панель инструментов вместе с формой пользователя.

Шаг 3: В этом наборе инструментов выберите Список .

Шаг 4: Нарисуйте этот список на форме пользователя.

Шаг 5: После вставки списка, дайте правильное имя этому списку под окном Свойства.

Примечание. Если вы не видите этот список, нажмите клавишу F4, вы увидите окно свойств, подобное приведенному выше.

Шаг 6: Прокрутите вниз и найдите «Источник строки». В этом введите диапазон значений названия вашего месяца вместе с именем листа.

Шаг 7: Как только вы дадите ссылку, вы увидите названия месяцев в списке.

Шаг 8: Теперь в окне свойств списка выберите стиль линии как « 1 - fmListStyleOption ».

Шаг 9: Как только вы выберете стиль линии, вы сразу увидите влияние.

Шаг 10: Запустите этот код, вы увидите список в отдельности.

Шаг 11: Теперь мы добавим макрос-код для хранения данных, выбранных пользователем. Дважды щелкните по списку. Вы увидите отдельное имя макроса, как показано ниже.

Шаг 11: Когда пользователь выбирает месяц, мы будем хранить данные в ячейке G5. Чтобы сохранить введенные пользователем данные, добавьте код ниже.

Шаг 12: Теперь, если вы запустите код и выберете любой из месяцев, мы увидим значение, выбранное пользователем, в ячейке G5.

Я выбрал Jun, чтобы видеть Jun в ячейке G5. Таким образом, мы можем использовать поле со списком в ваших проектах VBA для получения информации от пользователей.

Окно списка VBA - пример № 2

Метод Add.Items для добавления значений в список

Выполните следующие шаги, чтобы создать список в VBA.

Шаг 1: Создайте новую пользовательскую форму. Вот это UserForm2.

Шаг 2: Добавить список в пользовательской форме 2.

Шаг 3: После вставки списка, дайте правильное имя этому списку в окне свойств, то есть Month_list_Box1 .

Шаг 4: Теперь в окне свойств списка выберите стиль линии как « 1 - fmListStyleOption ».

Шаг 5: Дважды щелкните по форме пользователя. Вы увидите автоматически вставленный макрос, как показано ниже.

Шаг 6: поместив курсор внутри макроса, выберите « Initialize ».

Шаг 7: Как только вы выбрали «Initialize», мы можем увидеть новый макрос, который будет создан чуть ниже текущего.

Шаг 8: Удалить старый макрос. Под инициализацией пользовательской формы напишите приведенный ниже код, чтобы добавить элементы в список.

Шаг 10: Теперь нажмите кнопку F5, чтобы запустить код, и вы получите следующий вывод.

То, что нужно запомнить

Если вы даете ссылку на ячейку напрямую, вам необходимо указать имя листа и ссылку на ячейку. (Вам необходимо добавить восклицательный знак (!) Перед именем листа).
Вы также можете добавить элементы в список с помощью метода Add.Item.
Если вы хотите разрешить пользователю выбирать более одного элемента, вам нужно выбрать опцию « fmMultiSelectMult» в разделе «Multi-Select» в окне «Свойства».

VBA Excel ListBox

Теперь надо попрактиковаться, напишем пример использования компонента VBA ListBox в Excel. Но прежде всего, надо заполнить четыре столбика различными данными. Я создал книгу в Excel и заполнил первые четыре столбика такими данными:

A – первое поле Автомобиль (оно будет служить заголовком), далее следуют марки автомобилей.
B – первое поле Телефон (оно будет служить заголовком), далее следуют модели телефона Samsung.
C – первое поле Компьютер (оно будет служить заголовком), далее следуют марки производителей PC.
D – первое поле Напиток (оно будет служить заголовком), далее следуют марки напитков.

Хорошо, таблицу в Excel мы сделали, теперь можно приступить к работе с проектом. И так, добавьте в проект новый модуль и форму.

Внешний вид формы вы можете увидеть на рисунке. На форме должны быть расположены два элемента управления vba excel ListBox, три элемента TextBox и одна кнопка (CommandButton). Первый список будет содержать четыре столбца, в которых будет отображаться содержимое первых пятидесяти ячеек для A, B, C и D. Второй список будет содержать также все 4 столбика, но отображаться будет только первый. При нажатии на кнопку, произойдет считывание выбранного элемента во втором списке, и в текстовых полях отобразится содержимое для второй, третьей и четвертой колонки.

Чтобы не путать все еще больше, приступим к практике:

UserForm_Initialize – настройка данных при инициализации формы (UserForm). Тут происходит настройка свойств для первого и второго объекта ListBox vba языка:

ListBox1 – будет отображаться заголовок, компонент содержит четыре колонки, их ширина: 60;80;60;30. Данные в список будут добавляться из диапазона A2:D50, присутствует возможность выделять только один элемент в списке.

ListBox2 – тут практически все тоже самое, что и для первого списка, только ширина колонок: 60;0;0;0 – то есть, будет отображаться содержимое только первой колонки.

CommandButton1_Click – тут происходит обработка щелчка по кнопке, а именно: происходит проверка, выбрано ли что-то во втором списке (vba excel ListBox), если да, то в первое текстовое поле запишется содержимое из второй колонки, во второе – из третей и в третье – из четвертой.

Спасибо за внимание. Автор блога Владимир Баталий

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