Vba excel выбор из списка
Обновлено: 07.07.2024
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) - данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК. Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.
Подробнее с остальными возможностями проверки данных(помимо выпадающих списков) можно ознакомиться в этой статье: Проверка данных
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 для загрузки элементов в поле со списком по одному:
Выбирает и возвращает значение из списка аргументов.
Синтаксис
Синтаксис функции Choose состоит из следующих частей:
| Part | Описание |
|---|---|
| индекс | Обязательный. Числовое выражение или поле, возвращающее значение в диапазоне от 1 до числа доступных вариантов выбора. |
| choice | Обязательный. Выражение варианта, содержащее один из возможных вариантов выбора. |
Примечания
Функция Choose возвращает значение из списка вариантов на основе значения аргумента index. Если index имеет значение 1, функция Choose возвращает первый вариант; для index равного 2 возвращается второй вариант выбора и т. д.
С помощью функции Choose можно искать значение в списке вариантов выбора. Например, если аргумент index получает значение 3, и choice-1 = "один", choice-2 = "два", а choice-3 = "три", функция Choose возвращает значение "три". Эта возможность часто используется в тех случаях, когда аргумент index представляет значение в группе вариантов.
Функция Choose возвращает значение Null, если аргумент index имеет значение меньше 1 или больше числа доступных вариантов в списке.
Если аргумент index имеет не целое значение, перед вычислением оно округляется до ближайшего целого.
Пример
В этом примере функция Выбрать отображает имя в ответ на индекс, переданный в процедуру в Ind параметре.
См. также
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Всем, привет!
Ситуация: программно создается несколько книг. Необходимо что бы в ячйках A1, A2,A3 созданных книг появлялся выпадающий список известных значений(значения текстовые).
Помогите с реализацией!
Скопировать строку Excel, за текущей строкой если из выпадающего списка выбрать второе значение в ячейке
Добрый день, никак не получается совместить макрос который я пытаюсь совместить Есть макрос.
Создание выпадающего списка
Нужно создать выпадающий список в ячейке средствами VBA, который ссылался бы не на диапазон ячеек.
Изменение выпадающего списка
Подскажите пожалуйста, второй день пытаюсь изменить выпадающий список созданный обычной функцией в.
Наполнение выпадающего списка ComboBox
Здравствуйте. Подскажите, как можно наполнить ComboBox, который находится на одном листе (может .
Исправьте разделитель списка на запятую, например А можно не много поподробнее! А то не совсем понятно. Вы не могли подсказать, что означает каждая функция?! Вы не могли подсказать, что означает каждая функция?! Мог бы. Но только за большие деньги
Вы можете сами найти ответы: в редакторе VBA поставьте курсор в слово например With и нажмите F1. Если с аглицким туго, найдите в инете справку по VBA для Офис-97 на русском. Ну или скачайте литературу по VBA: Учебники, справочники, самоучители
Эта конструкция задает параметры функции проверки значений, пишется одной строчкой: Cells(1, 1).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=$C$3:$C$10".
Просто принято делить. Использовать With нужно когда еще чтото делается над тем же объектом
Нижнее подчеркивание - знак переноса для VBA.
Нуже два примера работают одинаково, но читать удобнее первый
Делает она то что на скрине.
В том окошке еще много чего интересного, вот за это и отвечают остальные строки.
Казанский, Не стоит сразу посылать маны курить. Все когда-то начинали. Порой пара толковых объяснений дает больше толку и хороший пинок что разобраться дальше нежели прочитанный талмуд.
До прошлой недели сам использовал элементы управления (третий способ в ссылке на "планету"), у коллег машина ругается на наличие макроса со всеми вытекающими.
Formanter, оч. хочется посмотреть на Ваше отношение к "отсыланию в маны" постов этак через 2000. да даже хотя бы через 500
Давать ссылку на маны вместо решения - не нужно.Если маны даются вдовесок к решению - это больше, чем просто помощь.
У меня такая проблема. Хочу в выпадающий список в ячейке вставить массив данных.
Пробовала в формулу писать имя массива, не выходит..
Конечно есть вариант написать массив на скрытый лист, потом сослаться в формуле на диапозон.
Но хочется без лишнего веса обойтись, компы тормозные. Реально сделать.
Здравствуйте, @Feel_,
Поступил бы так:
Если строка получится длиннее 255 - то при открытии сохранённого xlsm с такой проверкой будет ругань.xlsb терпеливее - но пределы не изучал. Не получается так((( ругается. Ну ладно.. сделала уже с листом.. Надо скорее просто. Но если метод найдётся буду очень рада. Так как часто требуется!
Ребят, а может мне кто-нибудь подскажет вариант реализации? Задача у меня такая: есть файл с адресами: Город, Улица, Дом - это столбцы. Файл для каждого региона свой, и он динамичный лежит в общем справочнике. Мы работаем с другим файлом, в который вставляется нужный листик, в зависимости от того, какой регион его открывает. Это я написала. Так вот. теперь надо, чтобы на другом листочке в столбцах Город/улица/дом, выходили выпадающие списки, причём для определённого города, только его улицы, а для улиц дома. При этом не должно быть пустых и улицы уникальны.
Я придумала два варианта реализации. Но они оба не отличаются особым успехом.
Первый: почти без VBA
с помощью VBA я создаю уникальный список городов и делаю его именем и прописываю кодом, что значение в столбце должно быть список с именем "Город"
далее, так как у меня есть список Город-Улица (на адресном листе), с помощью формулы смещения и поиска позиции нахожу улицы только этого города.(минус этого такой, что эта формула в именах постоянно почему-то (!) сбивается. )
Далее аналогично, этой же формулой нахожу дома для улиц.
Второй способ - не осуществила до конца, так как ступор. Создала уникальные города на отдельном листе в строку. Далее создала под каждым городом его улицы - так же уникальные. А вот теперь надо через ДВССЫЛ через VBA создать столько имён, сколько у меня вышло городов. вообщем как это сделать. если это реально.
Ранее в публикациях рассказывалось о том, как создается выпадающий список в ячейках для упрощения внесения данных.
Ссылка на описания метода создания связанного выпадающего списка ниже:
В данной публикации описана процедура создания выпадающих списков, которые записывают в ячейки по нескольку значений.
Для начала следует создать обыкновенный выпадающий список.
Для этого необходимо:
- Войти во вкладку «Данные»;
- Выбрать опцию «Проверка данных»;
- Выбрать «Список»;
- Указать диапазон, из которого будет выбираться выпадающий список или создать список прямо в появившемся поле через знак «;».
После этой процедуры следует записать макрос в документ.
Для записи макроса следует:
- Открыть вкладку «Разработчик» ( Если вкладка отключена, включите ее в разделе Файл=> Параметры=> Настройка Ленты);
- Во вкладке «Разработчик» выбрать кнопку «Просмотр кода»;
- В открывшееся окно записать макрос;
Давайте рассмотрим несколько макросов с выпадающими списками.
Первый макрос со смещением списка в сторону (горизонтально).
Макрос выпадающего списка со смещением вниз:
Макрос выпадающего списка с внесением нескольких значений в одну ячейку:
Похожее:
-
О том, как при помощи несложного макроса.Как расположить вертикальный список, перенесенный из «Excel».Иногда для листов с большим объемом позиций.
Макрос выпадающего списка с несколькими значениями в Excel: 12 комментариев
Добрый день! Макрос выпадающего списка с внесением нескольких значений в одну ячейку почему то не работает. Нижеприведенные строки почему то становятся красным. Я так понимаю В2:В5 это диапазон который можно изменять на другую область например на F2:F200 допучстим? Или я не прав? Подскажите пожалуйста.
Читайте также: