VBA Excel. Элемент управления ComboBox (поле со списком)
Элемент управления пользовательской формы ComboBox для выбора и ввода информации в VBA Excel. Свойства поля с раскрывающимся списком, заполнение, извлечение данных, примеры кода.
Элемент управления ComboBox
ComboBox представляет из себя комбинацию двух элементов управления: текстового поля (TextBox) и списка (ListBox), поэтому его еще называют «комбинированным списком» или «полем со списком». Также ComboBox сочетает в себе свойства этих двух элементов управления.
Изначально комбинированный список прорисовывается на форме в виде текстового поля с кнопкой для отображения раскрывающегося списка. Далее по тексту будем использовать слово «поле» в значении текстового поля в составе элемента управления ComboBox, а словосочетание «раскрывающийся список» – в значении списка в составе элемента управления ComboBox.
Поле со списком используется в тех случаях, когда необходимо добавить в форму информацию, которая заранее известна, а ее отдельные позиции можно сгруппировать в список, а также для ручного ввода с клавиатуры или вставки из буфера обмена, если необходимое значение в списке отсутствует.
Элемент управления ComboBox незаменим при больших списках. При списках из нескольких позиций его можно заменить на ListBox, который отображает позиции для выбора сразу после загрузки формы, не требуя дополнительных действий от пользователя.
Свойства поля со списком
* При Enabled в значении False пользователь не может раскрывать список, а также вводить или редактировать данные в поле.
** Для элемента управления ComboBox действие свойства Locked в значении True аналогично действию свойства Enabled в значении False.
В таблице перечислены только основные, часто используемые свойства поля со списком. Еще больше доступных свойств отображено в окне Properties элемента управления ComboBox, а все методы, события и свойства – в окне Object Browser.
Вызывается Object Browser нажатием клавиши «F2». Слева выберите объект ComboBox, а справа смотрите его методы, события и свойства.
Свойства BackColor, BackStyle, BorderColor, BorderStyle отвечают за внешнее оформление комбинированного списка и его границ. Попробуйте выбирать доступные значения этих свойств в окне Properties, наблюдая за изменениями внешнего вида элемента управления ComboBox на проекте пользовательской формы.
Способы заполнения ComboBox
Используйте метод AddItem для загрузки элементов в поле со списком по одному:
Выпадающий список в Excel с помощью инструментов или макросов
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Excel activex поле со списком свойства
Создать поле со списком управления формой в Excel
Создать поле со списком управления формой в ExcelЧтобы вставить это Поле со списком управления формой, вам необходимо активировать разработчик вкладка на ленте. Пожалуйста, сделайте следующие шаги:
1. В Excel 2010/2013 нажмите Файл > Опции, А затем нажмите Настроить ленту на левой панели и проверьте разработчик в Параметры Excel диалоговое окно, последний щелчок OK.
В Excel 2007 щелкните Кнопка офиса > Параметры Excel для открытия Параметры Excel в диалоговом окне на левой панели щелкните Популярное, и на правой панели отметьте Показать вкладку разработчика на ленте под Лучшие варианты работы с Excel раздел.
2. После отображения разработчик На вкладке создайте свой список значений, который вы хотите добавить в поле со списком. Смотрите скриншот:
3. Затем перейдите и нажмите разработчик > Вставить, под Контроль формы, Нажмите Поле со списком кнопку, а затем перетащите мышь, чтобы нарисовать Поле со списком в конкретное место, см. скриншоты:
 |  |  |
4. Затем щелкните правой кнопкой мыши Поле со спискомИ выберите Управление форматом, см. снимок экрана:
6. После завершения настройки нажмите OK, ваше поле со списком было успешно создано, как показано на следующем снимке экрана:
Создать поле со списком ActiveX Control и изменить размер шрифта в Excel
В указанном выше поле со списком вы не можете изменить размер шрифта, но с помощью Поле со списком Active Control, вы можете установить размер шрифта для поля со списком. Однако создать это поле со списком несколько сложно.
1. Создайте список данных, которые вы хотите использовать для поля со списком, введите один из ваших данных в пустую ячейку, выберите его и щелкните правой кнопкой мыши, чтобы выбрать Определить имя чтобы создать имя диапазона для этой ячейки, чтобы связать список.
3. Нажмите OK чтобы завершить эту операцию, затем нажмите разработчик > Вставитьи нажмите Поле со списком под элемент управления ActiveX, затем перетащите мышь, чтобы нарисовать поле со списком, см. снимки экрана:
 | |  |
4. Щелкните правой кнопкой мыши поле со списком, чтобы выбрать Свойства для установки свойств поля со списком.
5. В Свойства окно, в LinkedCell введите имя ячейки и диапазона списка, которое вы только что создали на шаге 1 и шаге 2. См. снимок экрана:
6. Затем закройте это окно и нажмите Режим проектирования команда под разработчик вкладка, чтобы выйти из режима дизайна.
7. И ваше поле со списком создано и готово к использованию.
Советы:
Если вы хотите изменить размер шрифта поля со списком, нажмите Режим проектирования команда под разработчик для входа в режим конструктора и щелкните правой кнопкой мыши поле со списком, чтобы выбрать Свойства.
 | |  |
И размер шрифта вашего поля со списком будет изменен на ваши настройки.
5 способов создания выпадающего списка в ячейке Excel
Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие списки в Excel. Более сложные способы, основанные на динамических диапазонах и использовании данных из других таблиц, мы также рассмотрим.
Как нам это может пригодиться?
Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров. Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.
Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых необходимо указать только одно.
Важно то, что вы теперь будете не вводить, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок. Проверка того, что мы вписали в таблицу, теперь уже не нужна.
Как проще всего добавить выпадающий список? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз. Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.
Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).
В качестве источника можно использовать также и именованный диапазон.
К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».
Кроме того, и источник и в виде обычного диапазона ячеек, и именованный диапазон могут находиться на других листах вашей рабочей книги.
Вставим на лист новый объект – элемент управления «Поле со списком» с последующей привязкой его к данным на листе Excel. Делаем:
Вставив элемент управления на рабочий лист, щелкните по нему правой кнопкой мышки и выберите в появившемся меню пункт «Формат объекта». Далее указываем диапазон ячеек, в котором записаны допустимые значения для ввода. В поле «Связь с ячейкой» укажем, куда именно поместить результат. Важно учитывать, что этим результатом будет не само значение из указанного нами диапазона, а только его порядковый номер.
Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).
Формулу в F3 запишем, как показано на рисунке:
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.
Задача: Создать перечень, в который будут автоматически добавляться значения из заданного динамического диапазона. Если в диапазон будут внесены изменения, то сразу же изменится и набор предлагаемых значений. Никакие формулы и настройки здесь корректировать не нужно.
Вот как автозаполнение может выглядеть на простом примере:
Способ 1. Укажите заведомо большой источник.
Действительно самый простой способ, но не слишком удобный. Ведь зарезервированное место может и закончиться.
Конечно, в качестве источника можно указать и весь столбец:
Но обработка такого большого количества ячеек может несколько замедлить вычисления.
Способ 2. Применяем именованный диапазон.
Именованный диапазон отличается от обычного тем, что ему присвоено определенное наименование. С ним гораздо проще работать, так как не нужно вводить ссылку, а достаточно просто указать его имя. Давайте рассмотрим небольшой пример.
В столбце А находятся имена сотрудников, которые мы будем вводить. Перечень может быть сколь угодно длинным. Нам необходимо, чтобы каждая новая запись включалась в раскрывающийся список без всяких дополнительных действий с нашей стороны.
Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул. Создадим в С2 перечень значений. В качестве источника для него укажем выражение
Перечень ещё можно отсортировать, чтобы удобно было пользоваться.
А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.
Способ 3. «Умная» таблица нам в помощь.
Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.
Следовательно, автоматическое обновление набора используемой информации в списке можно организовать, если использовать содержимое какого-либо столбца «умной» таблицы.
Осталось только обозначить ее как источник. Проблема заключается в том, что программа в качестве источника в списке не понимает выражение вида
и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.
Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.
Формула теперь будет выглядеть следующим образом:
Не забудьте также заключить все выражение в кавычки, чтобы обозначить его как текстовую переменную.
Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.
Надеемся, вы сможете теперь с помощью списков без ошибок вводить часто повторяющиеся данные в таблицу.
Выпадающий список в ячейке листа
Видео
Способ 1. Примитивный
Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:
Способ 2. Стандартный
Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку. Еще одним часто используемым трюком для таких списков является создание связанных выпадающих списков (когда содержимое одного списка меняется в зависимости от выбора в другом).
Способ 3. Элемент управления
После нажатия на ОК списком можно пользоваться.
Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:
Способ 4. Элемент ActiveX
Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):
Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:
Самые нужные и полезные свойства, которые можно и нужно настроить:
Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)
При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия: