Раскрывающийся список в экселе как сделать vba

Добавил пользователь Валентин П.
Обновлено: 10.09.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. Проект “Результаты сессии”

Задание: создать проект, содержащий 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 ‘удалить из списка

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

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


Практическая работа 2. Проект “Переводчик”

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


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

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

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

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

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

Path = "Ris" & Mid(Str(n), 2, 1) & ".jpg"

Функция 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" ‘загрузить звуковой файл

Path = "Ris" & Mid(Str(n), 2, 1) & ".jpg"

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

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

MMControl1.Command = "sound" ‘воспроизвести звук. файл

  1. Создайте процедуру кнопки “Перевод англ/рус”.
  2. Добавьте кнопку Сброс.
  3. Подберите 2 звуковых файла для подтверждения верного и неверного ответа, подключите эти файлы к проекту.
  4. Создайте заставку к проекту.
  1. Создать проект “Выбор шрифта”, позволяющий выбрать с помощью списков тип шрифта, размер, начертание (свойства): жирный, подчёркнутый, курсив. Выбранные параметры должны применяться к метке Образец.


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

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

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

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

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

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

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

Создание списков
Необходимо выбрать ячейку(или сразу несколько ячеек), в которую необходимо поместить этот список. В меню выбираем Данные (Data) -Проверка данных (Data Validation) . Переходим на вкладку Параметры (Settings) и в выпадающем списке Тип данных (Allow) выбираем Список (List) .
Поле Источник (Source) : предназначено как раз для ввода значений, из которых будет состоять выпадающий список. Варианты указания значений:

  • Вариант 1
    Ввести список значений вручную (значения в этом случае необходимо заносить через "точку-с-запятой" для русской локализации и через запятую для английской).
  • Вариант 2
    Указание в качестве источника обычной ссылки на диапазон ячеек
    При использовании обычного диапазона в поле Источник (Source) просто указываем диапазон ячеек со значениями (A1:A10). Для этого ставим курсор мыши в это поле и затем выделяем необходимый диапазон со значениями. Перед адресом диапазона Excel сам поставит знак равно: =$A$1:$A$10

    Недостаток данного метода: нельзя применять ссылки на другие листы и книги. Список будет работать только в случае, если ячейки расположены на том же листе, что и сам список. Что порой не очень удобно, поэтому чаще используется Вариант 3.
  • Вариант 3
    Указание в качестве источника именованного диапазона
    При использовании именованного диапазона необходимо сначала его создать: Создание именованных диапазонов. Предположим, что мы создали именованный диапазон с именем Список1.
    После создания именованного диапазона в поле Источник (Source) вписываем имя этого диапазона, не забыв поставить перед именем знак равно:

Параметры списков
Распространить изменения на другие ячейки с тем же условием (Apply this chages to all other cells with the same settings) - данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК. Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.

Подробнее с остальными возможностями проверки данных(помимо выпадающих списков) можно ознакомиться в этой статье: Проверка данных

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

Выпадающий список

1. Создадим приложение и книгу Excel

2. В созданной книге у меня один лист (ПриложениеExcel.SheetsInNewWorkbook = 1, значение можно изменить), переименуем в List

3. Заполним первый лист значениями, среди них и будем осуществлять поиск и переход. Может так случиться, что на присвоении Value будет появляться ошибка, можно попробовать использовать Value2

4. Добавим второй лист, после Лист1 (за это отвечает указанный второй параметр метода - After), переименуем в Groups; будем использовать этот лист для хранения именованного списка; этот лист можно затем скрыть для удобства

5. Заполним значения для создания именованного списка, будем использовать ссылки на случай изменения исходных значений. Обращу внимание на то, что для выпадающего списка мы можем использовать только стоящие рядом колонки или строки

6. Присвоим имя Search нашей группе ячеек

7. Добавим к ячейке (через объект Range) проверку вводимых значений с типом данных Список (первый параметр XlDVType: xlValidateList = 3) и источником Search (4-й параметр)

Выпадающий список создан. При желании по запросу "выпадающий список excel" можно найти статьи об интерактивном создании такого списка на тематических сайтах.

Макрос

Добавим макрос обработки выбора значения и поиска первого совпадающего с ним значения в первой колонке. После выбора значения из выпадающего списка, макрос переместит выделение к первой найденной ячейке с этим значением. Здесь может случиться казус в виде ошибки "Программный доступ к проекту Visual Basic не является доверенным". Необходимо включить флаг "Доверять доступ к объектной модели проектов VBA" (справедливо для Excel 2010):

Панель "Разработчик" -> группа "Код" -> Безопасность макросов -> меню "Параметры макросов" -> поле "Параметры макросов для разработчика".

Здесь же выбрать "Отключить все макросы с уведомлением "(разрешать запуск с уведомлением "Включить содержимое") или "Включить все макросы". Другой вариант доступа, если вдруг у вас не включена закладка Разработчик:

Файл -> Параметры -> Центр управления безопсностью -> Параметры центра управления безопсностью. -> Параметры макросов.

параметр AccessVBOM = 1, параметр VBAWarnings установите 1 (Включить все макросы) или 2 (Отключить все макросы с уведомлением).

Параметр в VBComponents должен быть именно таким Лист1. Вставляем строку в модуль, начиная с первой строки. В переменную ТекстМакроса следует поместить

Это обработчик события изменения листа. В качестве параметра выступает объект Range. A1 - ячейка (точнее объект Range), изменение в которой мы отслеживаем, в ней содержится наш выпадающий список. A2:A65536 - диапазон, где будет осуществляться поиск (65536 - пережиток Excel 97), если ячейки объединены, то надо указать весь диапазон. то есть, если ячейки А и B объединены, то диапазон будет A2:B65536. Комментировать весь код на VBA я не стану, но его праобраз можно найти на одном из тематических сайтов (где главная тема - Excel).

"Заморозим" первую строку на листе List, чтобы после выделения найденной ячейки, ячейка с выпадающим списком не уходила за пределы экрана

Т.к. "замораживание" возможно только у объекта Window (это набор некоторых элементов управления листа), надо активизировать первый лист List.

Теперь можно открыть книгу сделав приложение видимым, но только если код выполнялся на клиенте

Для книги с макросами параметр ФорматФайла должен быть равен 52 для Excel 2007-2016 (xlOpenXMLWorkbookMacroEnabled, with or without macro's in 2007-2016, xlsm) или 56 для Excel 97 (xlExcel8, 97-2003 format in Excel 2007-2016, xls). После записи следует закрыть книгу и выйти из приложения

У этого метода есть один недостаток: выбрав значение из списка, мы модифицируем книгу. Но, например, элемент управления ComboBox также её модифицирует. Способа от этого избавиться я пока не нашёл.

Существуют различные способы создания выпадающего списка в ячейке Excel. На мой взгляд, самый простой и функциональный способ делается следующим образом:


Шаг 1


Шаг 2


Шаг 3


Шаг 4

Вы создали выпадающий список.

Стоит заметить, что при копировании ячейки со списком в другую, выпадающий список также копируется.


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

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