Создание списка в vba в excel
Поле списка в Excel VBA представляет собой список, назначенный переменной. Этот список имеет различные входы для выбора и позволяет выбрать несколько вариантов одновременно. Список можно вставить в пользовательскую форму, выбрав параметр списка.
Списки используют именованные диапазоны, имеющие определенные значения. Целью использования списка является отображение списка значений, из которых пользователи могут выбирать.
Как создать список в Excel?
Шаги для создания списка в Excel перечислены ниже:
-
Шаг 1. Включите вкладку «Разработчик» на ленте Excel, чтобы получить доступ к элементам управления ActiveX. В диалоговом окне «Параметры Excel» нажмите «Настроить ленту в Excel» и установите флажок «Разработчик».
Шаг 2: На вкладке «Разработчик» нажмите «Вставить», а затем элемент управления «список». Это вставит новый список на лист Excel.
Как создать список в VBA?
Шаги для создания списка в VBA перечислены ниже:
Данные в ячейке B17 изменяются, когда вы меняете свой выбор в списке. Это показано на следующем изображении.
Как создать список в пользовательской форме?
Шаги для создания списка в пользовательской форме перечислены ниже:
Частная подпрограмма UserForm_Initialize()
Как добавить динамический элемент управления в список?
Действия по добавлению динамического элемента управления в список на Пользовательская форма с использованием VBA Пользовательская форма с использованием VBA В VBA пользовательские формы — это настраиваемые пользовательские формы, предназначенные для приема пользовательского ввода в виде формы. Он имеет различные наборы элементов управления для добавления, такие как текстовые поля, флажки и метки. читать далее перечислены следующим образом:
Частная подпрограмма CommandButton1_Click()
‘Добавить окно динамического списка и назначить его объекту ‘LstBx’
Установите LstBx = UserForm3.Controls.Add(«Forms.ListBox.1»)
- Шаг 6: Нажмите F5, чтобы запустить макрос.
Как добавить элементы в список элементов управления в VBA?
Код для добавления элементов в элемент управления списком в VBA упоминается следующим образом:
Частная подпрограмма Insert _Items _To_LstBox ()
ListBox1.AddItem «Элемент 1»
ListBox1.AddItem «Элемент 2»
ListBox1.AddItem «Элемент 3»
ListBox1.AddItem «Элемент 4»
ListBox1.AddItem «Элемент 5»
Как удалить элементы из списка элементов управления с помощью VBA?
Код для удаления элементов из элемента управления списком в VBA упоминается следующим образом:
Пример
Допустим, у нас есть ежедневные данные о продажах. Мы хотим подготовить графический пользовательский интерфейс этих данных о продажах, который позволит нам выбирать элементы из списка. Чтобы получить информацию о продажах, мы должны изменить ячейку Index на «ссылку на ячейку» и применить формула ВПР Формула ВПР Функция Excel ВПР ищет определенное значение и возвращает соответствующее совпадение на основе уникального идентификатора. Уникальный идентификатор однозначно связан со всеми записями базы данных. Например, идентификатор сотрудника, номер списка учащихся, контактный номер клиента, адрес электронной почты продавца и т. д. являются уникальными идентификаторами.
читать далее как показано на следующих изображениях.
Константы списка в VBA
Константы, их значения и описание приведены в следующей таблице:
Часто задаваемые вопросы
Списки используются для управления таблицами в пользовательской форме. Строки информации аккуратно управляются без ненужного отображения информации в пользовательской форме.
Пользователь может прокручивать элементы списка. Настройка «типа выбора» позволяет пользователям выбирать несколько вариантов из списка. Когда пользователь выбирает элемент из списка, с таким выбором предпринимается действие.
По умолчанию поле со списком выдает результат, соответствующий порядковому номеру выбранного элемента. Таким образом, если выбран четвертый элемент, на выходе будет число 4.
Обычно список необходимо заполнять значениями каждый раз при открытии рабочей книги. Процедура «Открыть» выполняется каждый раз при открытии Рабочей книги.
Для заполнения списка необходимо использовать следующий код:
С Sheet1.lstListBox
.AddItem «Джеймс»
.AddItem «Келли»
.AddItem «Элизабет»
.AddItem «Джордж»
.AddItem «Уильям»
Конец с
С этим кодом список заполняется 5 именами — Джеймс, Келли, Элизабет, Джордж и Уильям.
Данные могут быть эффективно использованы только при правильном их извлечении. Шаги для извлечения данных из списка перечислены ниже:
1. Выберите «Управление форматом» в меню после щелчка правой кнопкой мыши по списку.
2. В поле «Ссылка на ячейку» введите координаты пустой ячейки.
3. Выберите ячейку, в которой будут отображаться данные из списка. Необходимо использовать следующую формулу:
«=ИНДЕКС([begin cell]:[end cell],[cell link],0)”
Примечание: «Начальная ячейка» означает первую ячейку списка. «Конечная ячейка» означает последнюю ячейку списка. «Ссылка на ячейку» означает ячейку, координаты которой были введены на втором шаге.
Различия между полем со списком и полем со списком перечислены ниже.
– Окно списка состоит из списка элементов и позволяет пользователям выбирать один или несколько параметров. Напротив, поле со списком состоит из комбинации текстового поля и раскрывающегося списка. Текстовое поле позволяет пользователям вводить элемент. Выпадающий список позволяет пользователям выбирать элемент.
– В поле со списком отображаются все элементы в заданной области сразу, а в поле со списком — по одному элементу за раз. Элементы раскрывающегося списка поля со списком видны, когда пользователь щелкает
Это.
– В поле со списком пользователь должен выбирать из доступных элементов, тогда как в поле со списком пользователь может ввести элемент, которого нет в списке.
– Окно списка используется, когда ввод ограничен элементами списка. Однако поле со списком используется там, где упоминается список предлагаемых вариантов.
– Окно со списком занимает больше места, поскольку сразу отображается полный список. С другой стороны, поле со списком помещается на маленьком пространстве, потому что список отображается только при нажатии кнопки
Кнопка «Стрелка вниз.
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 для загрузки элементов в поле со списком по одному:
UserForm.ListBox – это элемент управления пользовательской формы, предназначенный для передачи в код VBA информации, выбранной пользователем из одностолбцового или многостолбцового списка.
Список используется в тех случаях, когда необходимо добавить в форму информацию, которая заранее известна, а ее отдельные позиции можно сгруппировать в список. Элемент управления 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 для загрузки элементов в список по одному:
Всем, привет!
Ситуация: программно создается несколько книг. Необходимо что бы в ячйках 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 создать столько имён, сколько у меня вышло городов. вообщем как это сделать. если это реально.
Нужно создать выпадающий список в ячейке средствами VBA, который ссылался бы не на диапазон ячеек, а на внутренний диапазон в тексте программы.
Как это дедается?
Заранее спасибо обитателям форума
Создание выпадающего списка
Добрый день! Как при изменении значений одного выпадающего списка создать другой? 1. не пойму.
Создание выпадающего списка с описанием
Добрый день, нужна помощь с выпадающим списком, на листе "Счета" указан номер счета а в соседней.
Создание выпадающего списка в Excel
Добрый день. Хотел задать простой вопрос, вероятно он уже обсуждался здесь, но я что-то не смог.
Создание выпадающего списка как в яндексе или Т9
Привет профессионалам, может кто знает как это сказать. Вопрос следующчий возможно сделать такую.
В ячейке сделать не так просто, а вот добавить на лист объект Listbox (можно его сделать малозаметным и, например, невидимым, пока не кликните на ячейку) - без проблем.
Код находится в объекте Лист1.
Единственное, заполнение списка криво написано. Надо было его в событие Workbook_Open запихнуть, а не в Worksheet_Activate. При открытии книги последнее не срабатывает и приходится выбирать какой-нибудь другой лист, а потом возвращаться на Лист1, чтобы событие сработало.
Еще я узнал что можно так:
Gibboustooth, а и Вашего примера можно чтобы после появления Комбобокса он сразу разворачивал список вариантов (без ручного нажатия на стрелочку) ?
Gibboustooth, а и Вашего примера можно чтобы после появления Комбобокса он сразу разворачивал список вариантов (без ручного нажатия на стрелочку) ?
Создание выпадающего списка, ругается ошибкой 1004
Приветствую всех! Есть небольшой макрос, который должен вырасти в большой, но что-то ругается.
Создание выпадающего списка в VBA Excel с использованием диспетчера имен
Добрый день. Возникла проблема. Подготовил данные на отдельном листе, через диспетчер имен задал.
Создание выпадающего списка с возможностью занесения нескольких значений в одну ячейку
Доброго времени суток, подскажите пожалуйста как создать возможность выбора в выпадающем списке.
Изменение выпадающего списка
Подскажите пожалуйста, второй день пытаюсь изменить выпадающий список созданный обычной функцией в.
Читайте также: