Заполнение поля со списком Vba Excel из диапазона
Обновлено: 21.11.2024
ComboBox — это элемент управления для пользовательских форм VBA, который позволяет пользователю выбирать из списка параметров. Оно также известно как раскрывающееся меню, но для того, чтобы оно выполняло свою функцию в качестве раскрывающегося меню, вам сначала нужно заполнить поле со списком для пользователя, чтобы он действительно мог сделать выбор. Есть два способа заполнить ComboBox с помощью VBA:
- С помощью метода .AddItem
- С помощью свойства .List
В оставшейся части этого руководства вы узнаете, как добавлять записи или заполнять свои собственные поля со списком пользовательских форм VBA, и мы немного повеселимся, научим этому!
Создайте пользовательскую форму
Прежде чем мы напишем какой-либо код для заполнения поля со списком, нам нужно создать само поле и пользовательскую форму, в которой оно находится.
Я уверен, что у вас уже есть собственный дизайн, но в этом руководстве наша пользовательская форма будет носить имя Combobox_Userform , наша командная кнопка будет CmdButton_For_CB , а само поле со списком будет ComboBox_Demo1 .
Вот как выглядит макет:
Как видите, мы поместили текст по умолчанию «Нажмите здесь» в поле со списком. Это не один из вариантов раскрывающегося списка; это просто заполнитель перед тем, как пользователь щелкнет меню. Вы можете редактировать его напрямую, щелкнув поле со списком в окне предварительного просмотра, как и для элементов управления Label и CommandButton, но на самом деле вам нужно заполнить поле со списком с помощью кода VBA, как мы продемонстрируем в следующем разделе.< /p>
Заполните поле со списком VBA
В этом разделе мы покажем вам, как заполнить поле со списком в событии UserForm_Initialize VBA, добавляя элементы последовательно или читая значения непосредственно из массива.
Доступ к событию инициализации
VBA передает управление событию UserForm_Initialize только после вызова пользовательской формы. Если вы хотите более подробно обсудить логику отображения пользовательской формы, ознакомьтесь с нашей статьей «Показ пользовательской формы». Вкратце, вот код для отображения нашей конкретной пользовательской формы:
Убедитесь, что вы поместили это в обычный модуль, а не в специальный закрытый код пользовательской формы.
Заполнить поле со списком списком .List
Теперь мы начнем фактически заполнять значения в нашем ComboBox. Один из способов добавить несколько значений в ComboBox — использовать свойство ComboBox List или .List .
Чтобы использовать свойство ComboBox .List, вам просто нужно поместить параметры раскрывающегося меню в массив и отделить каждый элемент раскрывающегося списка запятой. Вы можете использовать функцию Array для создания массива, необходимого для свойства .List. Не забывайте, что каждая опция нуждается в кавычках, так как ваши записи являются строками!
Вот пример кода, который входит в закрытый код пользовательской формы, а не в обычный модуль, и добавляет два элемента в раскрывающийся список после инициализации пользовательской формы:
Создавайте мощные макросы с помощью нашего бесплатного комплекта разработчика VBA
Подобные уроки могут быть сложными. Вот почему мы создали наш бесплатный комплект разработчика VBA и нашу большую книгу макросов Excel VBA в дополнение к этому руководству. Возьмите их ниже, и вы сразу же сможете писать мощные макросы.
Запустив этот код, вы получите вот такую красоту:
Обратите внимание, что заголовок пользовательской формы — «UserForm3», а имя пользовательской формы — ComboBox_Userform . Я указываю на это, потому что то же самое различие относится и к вашему выпадающему списку. Не путайте значения (Имя) и Заголовок или Текст! Вы получите ошибку времени выполнения, если попытаетесь использовать заголовок в качестве имени объекта. Вы можете изменить имя и заголовок объекта в окне свойств (F4), обычно в левом нижнем углу VBE.
Заполнить поле со списком диапазоном
Вы также можете использовать свойство .List, чтобы заполнить поле со списком пользовательской формы диапазоном ячеек в вашей электронной таблице. Возьмем, к примеру, этот макрос:
Этот макрос быстро добавит все значения в диапазоне A1:A10 в поле со списком. Это невероятно быстро и очень полезно, если ваша пользовательская форма предназначена для приема данных из электронной таблицы.
Заполнение многоколоночного поля со списком
Говоря о столбцах данных, свойство .List также имеет необязательный второй аргумент, pvargColumn, позволяющий заполнять определенные столбцы многоколоночного ComboBox.
Если вы хотите использовать необязательные аргументы в свойстве .List для создания полей со списком с несколькими столбцами, необходимо выполнить несколько дополнительных действий.
Сначала вам нужно изменить количество столбцов в поле со списком, как в моем руководстве по другому выравниванию столбцов в поле списка UserForm. Это можно сделать в упомянутом ранее окне «Свойства» или во время выполнения, используя свойство .ColumnCount объекта со списком, например так:
Затем вы можете создать фиктивный массив, который соответствует нужным размерам для списка параметров. Помните, что массив .List начинается с нуля. Допустим, вам нужен список из 3 строк и 2 столбцов с любыми данными, например числами или датами, поэтому вы определяете размерность массива следующим образом:
С этого момента у вас есть два варианта.Вы можете либо (1) заполнить свой массив, а затем заполнить поле со списком полным массивом, либо (2) заполнить поле со списком пустым массивом, а затем заполнить каждый элемент массива по отдельности.
В любом случае мы будем использовать свойство .List, как и раньше, для добавления элементов в ComboBox. Второй вариант позволяет получить доступ к каждой «ячейке» в списке, чтобы заполнить последнее раскрывающееся меню.
Вариант 1: передать полный массив в ComboBox
Вариант 2: передать пустой массив в ComboBox
Какой бы вариант вы ни выбрали, у вас останется красивое поле со списком VBA с двумя столбцами и тремя строками, которое полностью заполнено, как это:
Заполнить ComboBox с помощью .AddItem
Если вместо этого вы хотите добавить элементы с помощью методов (а не свойств), вы можете использовать метод ComboBox .AddItem во время инициализации. Каждый раз, когда вы вызываете метод AddItems, в ComboBox появляется новый элемент.
Что еще интереснее, вы можете использовать .AddItems для динамического добавления элементов после того, как пользовательская форма уже создана.
В коде динамическое заполнение ComboBox пользовательской формы будет выглядеть примерно так:
Мы использовали событие UserForm_Click , поэтому, когда пользователь щелкает область пользовательской формы, элемент добавляется в нижнюю часть раскрывающегося списка. Первоначальная пользовательская форма имеет значения по умолчанию, которые мы добавили с помощью свойства .List, но теперь мы позволяем пользователю добавлять элементы, также щелкая пользовательскую форму.
Я знаю, что это глупый пример, но он демонстрирует, как вы можете динамически изменять поле со списком при соблюдении определенных условий; в данном случае, когда пользователь щелкает форму.
Вы также можете заполнить список .AddItem на этапе инициализации, если хотите, например так:
Метод VBA ComboBox AddItem — самый популярный способ добавления элементов в пользовательскую форму, хотя я считаю, что оба варианта одинаково гибки.
Представьте, что у вас есть много строк в текстовом файле или что-то, что вы хотите добавить в поле со списком. Вы можете вложить метод .AddItem в цикл For, чтобы программно добавить каждый элемент. Размещение метода AddItem ComboBox внутри цикла является обычной практикой для заполнения полей со списком UserForm.
Добавить элемент в разные позиции в ComboBox
Метод AddItem для полей со списком пользовательских форм также принимает необязательный второй аргумент: pvargIndex. При включении pvargIndex сообщает вашему полю со списком, куда вы хотите добавить новую запись.
Элементы в поле со списком находятся в диапазоне от 0 (вверху) до .ListCount (внизу). Например, если вы передадите аргументу 0, это добавит новую запись в верхнюю часть вашей пользовательской формы. Отсутствие аргумента аналогично передаче методу AddItem аргумента ComboBox_Demo1 .ListCount .
Давайте рассмотрим пару примеров. В следующем коде сообщение «You Clicked Me!» элемент добавляется в первую позицию вместо последней позиции по умолчанию.
чтобы поместить код вверху поля со списком (позиция 0).
Давайте рассмотрим еще один пример.
В этом примере мы динамически заполнили поле со списком, добавив элемент вверху, внизу и в 3-й записи в раскрывающемся меню, указав позицию pvargIndex. Обратите внимание, как pvargIndex, равный 2, добавляет элемент в 3-й слот. Помните, что индексация ComboBox начинается с позиции 0!.
Заключение
Креативные разработчики могут использовать как .List, так и .AddItem для создания интерактивных раскрывающихся списков пользовательских форм. С помощью полей со списком (или раскрывающихся списков) разработчики могут направлять взаимодействие пользователей с программой, оставляя конечным пользователям определенную степень свободы.
Надеемся, вам понравился этот урок. Чтобы поднять свои навыки VBA на новый уровень, ознакомьтесь с другими нашими бесплатными учебными пособиями по VBA. Проверьте свои навыки, пытаясь связать концепции. Например, вы можете объединить функцию [VBA GetOpenFilename] с ComboBox для интерактивного раскрывающегося списка. Когда вы будете готовы вывести свой VBA на новый уровень, подпишитесь, заполнив форму ниже.
Готовы делать больше с VBA?
Мы собрали гигантский PDF-файл с более чем 300 готовыми макросами и хотим, чтобы вы получили его бесплатно. Введите свой адрес электронной почты ниже, и мы вышлем вам копию вместе с нашим комплектом разработчика VBA, полным советов, приемов и ярлыков VBA.
Прежде чем мы начнем, я хочу сообщить вам, что мы разработали набор шпаргалок по VBA, чтобы вам было проще писать лучшие макросы. Мы включили более 200 советов и 140 примеров макросов, поэтому в них есть все, что вам нужно знать, чтобы стать лучшим программистом VBA.
Эта статья была написана Кори Сарвером, автором статей для блога VBA Tutorials. Посетите его в LinkedIn и на его личной странице.
Создавайте раскрывающиеся списки в пользовательской форме с помощью элемента управления ComboBox. В этом примере есть два элемента управления ComboBox: один для идентификатора детали и один для местоположения.
Код поля со списком пользовательской формы Excel
В этом видео вы увидите код, который заполняет элементы в полях со списком в пользовательской форме Excel, созданной в этом руководстве: Создание пользовательской формы
Письменное объяснение кода показано под видео.
Создать поле со списком
В пользовательской форме Excel вы можете создавать раскрывающиеся списки с помощью элемента управления ComboBox. В этом примере пользовательская форма имеет два элемента управления ComboBox: один для идентификатора детали и один для местоположения.
Комбобокс может иметь один столбец, например этот список названий местоположений.
Или ComboBox может иметь несколько столбцов, например этот список деталей, в котором отображаются как идентификатор детали, так и описание детали.
Этот пример Excel VBA основан на инструкциях по созданию пользовательской формы Excel с полями со списком.
На этой странице код Excel VBA указан, но подробно не объяснен. В этом уроке мы рассмотрим, как работает код Excel ComboBox. Сначала мы создадим код VBA для списка ComboBox с одним столбцом, а затем мы создадим код Excel VBA для ComboBox с двумя столбцами.
Одностолбцовое поле со списком
Эта пользовательская форма Excel содержит поле со списком с именем cboLocation. Мы хотим, чтобы этот ComboBox отображал все местоположения из именованного диапазона — LocationList — на листе LookupLists.
В списке на листе только один столбец, поэтому нам понадобится только один столбец в поле со списком.
Мы хотим, чтобы списки ComboBox создавались автоматически, когда кто-то открывает нашу пользовательскую форму. Для этого мы будем использовать событие Initialize для пользовательской формы.
- В редакторе Visual Basic (VBE) выберите пользовательскую форму и в строке меню нажмите «Просмотр», затем нажмите «Код».
- В раскрывающемся списке в левом верхнем углу VBE выберите «Пользовательская форма» (возможно, она уже выбрана).
- В раскрывающемся списке Процедура в правом верхнем углу выберите Инициализировать
Определить переменные
Переменная cLoc — это объект Range, и мы будем использовать ее для ссылки на ячейку в диапазоне LocationList на листе.
Переменная ws – это объект Worksheet, и мы устанавливаем ее на рабочий лист LookupLists, где хранится список местоположений.
Добавить цикл
Далее мы добавим For Each. Следующий цикл посетит каждую ячейку в диапазоне LocationList на листе LookupLists.
Добавьте с. Конец с
Далее мы добавим с. Оператор End With, который ссылается на поле со списком Location с именем cboLocation. Этот код находится в модуле UserForm, поэтому Me относится к UserForm.
Добавить элементы списка
Наконец, внутри файла With. End With, мы поместим код для добавления элементов списка. Метод AddItem добавляет элемент в ComboBox, и наш код указывает Excel использовать значение из текущей ячейки (cLoc) в диапазоне LocationList.
Если вы протестируете пользовательскую форму с этим кодом инициализации, поле со списком Location будет иметь один раскрывающийся столбец, показывающий все четыре местоположения из диапазона LocationList. Поле со списком деталей будет иметь пустой раскрывающийся список, потому что мы еще не добавили в него элементы.
Комбобокс с несколькими столбцами
Далее мы добавим элементы в поле со списком Part, которое называется cboPart. Он покажет идентификаторы деталей из именованного диапазона — PartIDList — на листе LookupLists.
В списке деталей на листе есть два столбца, поэтому нам понадобятся два столбца в поле со списком, с идентификатором детали в первом столбце и описанием детали во втором столбце.
Добавить переменную
В верхней части процедуры Initialize мы добавим еще одну переменную для ячеек в списке идентификаторов деталей на рабочем листе.
Переменная cPart — это объект Range, и мы будем использовать ее для ссылки на ячейку в диапазоне PartIDList на листе.
Добавить цикл
Далее мы добавим For Each. Следующий цикл посетит каждую ячейку в диапазоне PartIDList на листе LookupLists.
Добавьте с. Конец с
Далее мы добавим с. Оператор End With, который ссылается на поле со списком Part с именем cboPart. Этот код находится в модуле UserForm, поэтому Me относится к UserForm.
Добавить элементы списка
Далее внутри файла With. End With, мы поместим код для добавления элементов списка. Метод AddItem добавляет в ComboBox строку со значением из текущей ячейки (cPart) в диапазоне PartIDList в первом столбце раскрывающегося списка.
Добавить значения второго столбца
Далее, под кодом AddItem, мы поместим код для добавления значений во второй столбец с помощью свойства List. Наш код сообщит Excel, какую строку и столбец раскрывающегося списка использовать.Свойство ListCount — это количество элементов в раскрывающемся списке.
Для свойства "Список" количество строк и столбцов начинается с нуля. Итак, если в раскрывающемся списке есть 1 элемент, он находится в строке 0. Вот почему мы вычитаем 1 из ListCount, чтобы получить номер строки.
Мы хотим, чтобы описание нашей части было во втором столбце. Первый столбец — это 0, поэтому второй столбец — это столбец 1.
Ячейка cPart находится в столбце A, и мы хотим, чтобы описание детали из столбца B было в той же строке. Итак, мы используем свойство Offset, чтобы получить значение на 0 строк вниз и на 1 столбец вправо.
Изменить свойства поля со списком
Для ComboBox с несколькими столбцами измените свойства ComboBox, чтобы увеличить количество столбцов, и установите ширину столбцов. Вы также можете отобразить заголовки столбцов, если хотите.
Завершенный код Excel VBA
Вот завершенный код Excel VBA для процедуры инициализации пользовательской формы. Он добавляет список из одного столбца в поле со списком "Расположение" и список из двух столбцов в поле со списком "Часть".
В конце код Excel VBA помещает текущую дату в текстовое поле Дата и число 1 в текстовое поле Количество.
Метод SetFocus перемещает курсор в поле со списком Part, чтобы пользователь мог выбрать часть.
<р>1). У меня есть даты, указанные в Excel (01.01.2006 по 31.12.2006), скажем, из диапазона B12: B376.Как передать эти значения в поле со списком.
2). Если это возможно, и если я выберу значение в поле со списком, скажем, 07.08.2006, оно должно обновиться в определенной ячейке, скажем, A1
Поэтому выбор 07.08.2006 в ComboBox должен отражать 07.08.2006 в ячейке A1.
Обратите внимание: этот ComboBox находится в пользовательской форме, а не на листе Excel.
Любая помощь по этому вопросу.
Спасибо,
Нирмал Удупа.
этот код будет заполнять поле со списком каждый раз при инициализации пользовательской формы
для вопроса 2 вы спрашиваете о какой-то формуле поиска?
Спасибо за код. Работает отлично.
Позвольте мне подробно объяснить второй вопрос.
Теперь, когда я получил список в ComboBox, когда я выбираю значение из ComboBox, например, 07/08/2006, оно должно отражаться в ячейке A1.
т.е. Ячейка A1 должна иметь значение 07.08.2006.
Это не Vlookup, который я ищу, но текст поля со списком должен отражаться в ячейке A1. Что-то вроде этого ниже?.
Спасибо,
Нирмал Удупа.
Вставьте этот код в кнопку команды (ok) на вашей пользовательской форме
Спасибо за код. Удачной недели.
Привет, я понимаю, что эта ветка довольно старая, однако у меня есть похожий вопрос, на который можно ответить просто. У меня есть пользовательская форма с полем со списком, которое заполняется диапазоном с использованием кода в последних сообщениях. Чего я надеялся добиться, так это того, что когда значение поля со списком выбирается в поле со списком, появляется метка, которая отображает диапазон, связанный с этим выбором. Например, допустим, поле со списком содержит диапазон (Лист1) (A1: A100), например. Допустим, это имена контактов. Как я могу заставить его отображать соответствующий адрес электронной почты этого контакта в области ярлыков в пользовательской форме? Данные электронной почты находятся на том же листе в столбце рядом со столбцом имен контактов. Будет ли это vlookup? Будем признательны за любую помощь!
Привет!кто-нибудь может помочь мне решить эту проблему
Я создал базу данных заказов, и я хочу использовать командную кнопку и назначить марку. После назначения макроса командная кнопка будет ссылаться на пользовательскую форму или любую вкладку, нажав командную кнопку..нужен поиск по заказу на покупку. номер.. Я хочу ввести цифру в текстовое поле, чтобы найти ее.. просто введите цифру, а затем нажмите «ОК». данные будут показывать только выбранный номер заказа на поставку.. есть предложения?
Спасибо и с уважением
Ади Изван Шах
Пожалуйста, ознакомьтесь с правилами Fourm.
Не публикуйте свой вопрос в цепочках, созданных другими — это называется захватом темы.
Всегда создавайте новую тему для *ВАШЕГО* вопроса. Если вы считаете полезным разъяснить свои потребности, вы можете включить ссылку на эту (или любую другую) тему.
При создании новой темы обязательно дайте ей удобное для поиска название, точно описывающее ваши потребности.
Если вы хотите отобразить список значений, из которых пользователи могут выбирать, добавьте список на лист.
Добавить список на лист
Создайте список элементов, которые должны отображаться в вашем списке, как показано на этом рисунке.
Нажмите "Разработчик" > "Вставить".
Примечание. Если вкладка «Разработчик» не отображается, нажмите «Файл» > «Параметры» > «Настроить ленту». В списке основных вкладок установите флажок "Разработчик" и нажмите кнопку "ОК".
В разделе "Элементы управления формой" нажмите "Список" ("Элемент управления формой").
Щелкните ячейку, в которой вы хотите создать список.
Нажмите «Свойства» > «Управление» и задайте необходимые свойства:
В поле Диапазон ввода введите диапазон ячеек, содержащих список значений.
Примечание. Если вы хотите, чтобы в списке отображалось больше элементов, вы можете изменить размер шрифта текста в списке.
В поле Ссылка на ячейку введите ссылку на ячейку.
Совет. Выбранная вами ячейка будет иметь номер, связанный с элементом, выбранным в вашем списке, и вы можете использовать этот номер в формуле, чтобы вернуть фактический элемент из входного диапазона.
В разделе "Тип выбора" выберите "Одиночный" и нажмите "ОК".
Примечание. Если вы хотите использовать Multi или Extend, рассмотрите возможность использования элемента управления списка ActiveX.
Добавить поле со списком на лист
Вы можете упростить ввод данных, предоставив пользователям возможность выбирать значение из поля со списком. Поле со списком объединяет текстовое поле со списком для создания раскрывающегося списка.
Вы можете добавить поле со списком Form Control или ActiveX Control. Если вы хотите создать поле со списком, позволяющее пользователю редактировать текст в текстовом поле, рассмотрите возможность использования поля со списком ActiveX. Поле со списком ActiveX Control является более универсальным, потому что вы можете изменить свойства шрифта, чтобы сделать текст более удобным для чтения на увеличенном листе, и использовать программирование, чтобы он отображался в ячейках, содержащих список проверки данных.
Выберите столбец, который можно скрыть на листе, и создайте список, введя по одному значению в каждую ячейку.
Примечание. Вы также можете создать список на другом листе той же книги.
Нажмите "Разработчик" > "Вставить".
Примечание. Если вкладка «Разработчик» не отображается, нажмите «Файл» > «Параметры» > «Настроить ленту». В списке основных вкладок установите флажок "Разработчик" и нажмите кнопку "ОК".
Выберите тип поля со списком, которое вы хотите добавить:
В разделе "Элементы управления формой" нажмите Поле со списком ("Элемент управления формой").
В разделе "Элементы управления ActiveX" нажмите Поле со списком (элемент управления ActiveX).
Нажмите на ячейку, в которую вы хотите добавить поле со списком, и перетащите его, чтобы нарисовать.
Чтобы изменить размер окна, наведите указатель на один из маркеров изменения размера и перетащите край элемента управления, пока он не достигнет нужной высоты или ширины.
Чтобы переместить поле со списком в другое место на листе, выберите поле и перетащите его в другое место.
Форматировать поле со списком управления формой
Щелкните правой кнопкой мыши поле со списком и выберите "Управление форматом".
Нажмите «Управление» и установите следующие параметры:
Диапазон ввода: введите диапазон ячеек, содержащих список элементов.
Ссылка на ячейку. Поле со списком может быть связано с ячейкой, в которой отображается номер элемента при выборе элемента из списка. Введите номер ячейки, в которой должен отображаться номер элемента.
Например, ячейка C1 отображает 3, когда выбран элемент Sorbet, потому что это третий элемент в нашем списке.
Совет. Вы можете использовать функцию ИНДЕКС, чтобы отобразить имя элемента вместо номера. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести следующую формулу: =ИНДЕКС(A1:A5,B1), когда мы выбираем элемент "Sorbet", отображается в C1.
Строки раскрывающегося списка: количество строк, которые должны отображаться при нажатии стрелки вниз. Например, если в вашем списке 10 элементов, и вы не хотите прокручивать их, вы можете изменить число по умолчанию на 10. Если вы введете число, меньшее, чем количество элементов в вашем списке, отобразится полоса прокрутки.
Форматировать поле со списком ActiveX
Нажмите "Разработчик" > "Режим конструктора".
Щелкните правой кнопкой мыши поле со списком и выберите "Свойства", нажмите "По алфавиту" и измените любой параметр свойства по своему усмотрению.
Чтобы установить это свойство
Нажмите "Цвет фона" > стрелку вниз > "Поддон" и выберите цвет.
Тип, стиль или размер шрифта
Нажмите «Шрифт» > the. и выберите тип, размер или стиль шрифта.
Нажмите "ForeColor" > стрелку вниз > "Поддон", а затем выберите цвет.
Связать ячейку для отображения выбранного значения списка.
Нажмите LinkedCell
Связать поле со списком со списком
Нажмите на поле рядом с ListFillRange и введите диапазон ячеек для списка.
Изменить количество отображаемых элементов списка
Щелкните поле ListRows и введите количество отображаемых элементов.
Закройте окно "Свойство" и нажмите "Режим конструктора".
После завершения форматирования можно щелкнуть правой кнопкой мыши столбец со списком и выбрать "Скрыть".
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Читайте также: