Как сделать выпадающий список в vba

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

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

Для начала следует создать обыкновенный выпадающий список.

Для этого необходимо:

• Войти во вкладку «Данные»;
• Выбрать опцию «Проверка данных»;
• Выбрать «Список»;
• Указать диапазон, из которого будет выбираться выпадающий список или создать список прямо в появившемся поле через знак «;».

После этой процедуры следует записать макрос в документ.

Для записи макроса следует:

• Открыть вкладку «Разработчик» ( Если вкладка отключена, включите ее в разделе Файл=> Параметры=> Настройка Ленты);

• Во вкладке «Разработчик» выбрать кнопку «Просмотр кода»;
• В открывшееся окно записать макрос;

• Закрыть окно с макросом.

Давайте рассмотрим несколько макросов с выпадающими списками.

Первый макрос со смещением списка в сторону (горизонтально).

Текст макроса:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B2:B10»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(0, 1)) = 0 Then
Target.Offset(0, 1) = Target
Else
Target.End(xlToRight).Offset(0, 1) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Необходимо обратить внимание, что в строке :
If Not Intersect(Target, Range(«B1:B10»)) Is Nothing And Target.Cells.Count = 1 Then
Значения («B1:B10»)— это диапазон в пределах которого будет работать выпадающий список.
Аналогичным образом можно создать выпадающий список со смещением вниз и выпадающий список, записывающий в ячейку несколько значений через знак табуляции или пробел.

Макрос выпадающего списка со смещением вниз:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«C2:F2»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(1, 0)) = 0 Then
Target.Offset(1, 0) = Target
Else
Target.End(xlDown).Offset(1, 0) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Макрос выпадающего списка с внесением нескольких значений в одну ячейку:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«B2:B5»)) 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

В строке If Not Intersect(Target, Range(«B2:B5»)) Is Nothing And Target.Cells.Count = 1 Then
указывается диапазон действия макроса.
В строке
Target = Target & «//» & newVal
указывается разделитель «//». Его можно заменить на любой знак препинания, текст или поставить пробел.

Похожее:

1. Как поделить таблицу Excel на две колонки для печати на одном листе.О том, как при помощи несложного макроса.
2. Как расположить вертикальный список, перенесенный из «Excel» в «Word», в одну строку (горизонтально)Как расположить вертикальный список, перенесенный из «Excel».
3. Добавление кнопки или стрелки перехода вверх(в начало) на листе Excel. Макрос.Иногда для листов с большим объемом позиций.

Макрос выпадающего списка с несколькими значениями в Excel: 2 комментария

Добрый день! Макрос выпадающего списка с внесением нескольких значений в одну ячейку почему то не работает. Нижеприведенные строки почему то становятся красным. Я так понимаю В2:В5 это диапазон который можно изменять на другую область например на F2:F200 допучстим? Или я не прав? Подскажите пожалуйста.

Надстройка для облегчения ввода значений в ячейку Excel

Автор: nerv
Last Update: 27/03/2012

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

А, может, иметь дело с одними теми же, но не структурированными данными?

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

Как это работает:

По нажатию Ctrl+Enter рядом с выделенной ячейкой появляется список, который позволяет не только выбирать, но и производить поиск по интересующим Вас данным.

Посмотрим, что он умеет:

• Не содержит повторов (уникальный). Легко выявить однотипные данные;
• Отсортирован по возрастанию. Возможность быстро найти то, что нужно;
• После вызова сразу готов к поиску/выбору из списка. Лишние движения ни к чему;
• Позволяет искать с использованием специальных подстановочных символов (*.

и т.п.);

Осуществлять быстрый поиск по "шаблону". Если ячейка, из которой был вызван список, содержит информацию, поиск будет произведен по ней;

Появляется рядом с текущей/активной ячейкой и не "убегает" за пределы экрана;

Навигация привычными стандартными клавишами: Up [Вверх], Down [Вниз], Page Up [На страницу Вверх ], Page Down [На страницу вниз];

Корректная работа со всеми типами данных: строки, даты, числа;

Обработка ошибок формул листа. Никаких пустых строк в списке;

Обработка защиты ячеек листа. В защищенные ячейки ввод запрещен;

Информация об общем количестве списка и найденных по запросу элементах;

Быстрый вызов по нажатию Ctrl+Enter;

Быстрое закрытие: клавиша Esc;

Быстрый ввод клавишей Enter

Помимо всего вышеперечисленного, позволяет сэкономить на размере файла за счет формирования списка "на лету", который создается в разы быстрее, если данные упорядочены или частично упорядочены по возрастанию.

Отличия версии 1.6 от 1.5:

• новая, более мощная/быстрая процедура сортировки;
• переход после ввода на следующую ячейку (в зависимости от установок Excel);

• использования и формирования списка (подробнее во вложении "how to use");
• поиска с учетом регистра и без него;
• маски поиска;
• заголовков.

Вложение	Размер	Загрузки	Последняя загрузка
nerv_DropDownList_1.6.zip	28.74 КБ	19	5 лет 40 недель назад

• 134866 просмотров

Комментарии

Автор этой надстройки – не я.
Тот, кто её написал, уже давно в комментах на сайте не отвечает, – а я не планирую поддерживать чужие решения.
Потому, комменты к этой статье я сейчас закрою.

На вопросы по этой надстройке больше отвечать некому.
Надстройка не поддерживается, не дорабатывается, – если хотите её использовать, то используйте в таком виде, в каком она сейчас есть.
Если нужно что-то аналогичное, – оформляйте заказ на сайте, сделаем «с нуля»

Уважаемый Игорь!
При работе у меня был сформирован список к примеру с 500 позиций в одном столбце, но уникальных элементов 100, возможно ли скопировать или экспортировать эти 100 уникальных элементов?
Спасибо за надстройку.

Люди добрые! Оч полезная надстройка! Но вот у меня одна проблема когда в двух соседних столбцах расположен "автопоиск" выдает ошибку (Unknown error). Допустим автопоиск настроен на ячейку А2 и Второй на В2. записан макрос на автозапуск
Sub va()
‘
‘ va Макрос
‘

‘
Application.Run "nerv_DropDownList.DropDownListShow"
End Sub

и этот макрос запускается командой
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A2:A2000"), Target) Is Nothing Then
Application.Run "va"
End If
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B2:B2000"), Target) Is Nothing Then
Application.Run "va"

End Sub
При переходе на ячейку А2 выходит окно поиска ввожу данные нажимаю enter, происходит переход на след ячейку B2 и выскакивает ошибка Unknown error!
Помогите советом что сделать !?

>Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла
Тот же вопрос: можно ли в качестве "Шаблона Подстановки" использовать другую книгу?
Благодарю за шикарную надстройку.

ИНДЕКС() + ПОИСКПОЗ() Вам в помощь.

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

Подскажите пожалуйста, как его можно привязать к personal.xls или рабочему файлу со списками, чтобы избежать запуска

Здравствуйте!
А сколько строк эта надстройка может обработать?
Можно ли сделать чтобы обрабатывала 200 000

Скажите, пожалуйста, как выбрать сразу несколько значений. Чтобы в одной ячейке было "Апельсин; Лайм". Это возможно?

Здравствуйте! Могли бы Вы обновить файл nerv_DropDownList_1.6.zip? Файл скачивается, запускается excel, но ничего не открывается и ошибки не выдает. Спасибо!

Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла

Отличная штука, спасибо!

Есть небольшой глюк при использовании настройки – если на ячейки, куда автоподбором вставляются значения, установить проверку, то проверка не срабатывает когда значение вводится через окно надстройки.

>> /////и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).

>> Это легко можно организовать штатными методами екселя – /Данные/Проверка данных/список, указав предварительно созданный именованный динамический список из тогоже диапазона что и обсуждаемая надстройка.

Excel 2007 – не получается так задать, макрос обходит запрет

Заполнение ListBox данными с помощью кода VBA Excel. Добавление значений в список методом AddItem, с помощью свойств List и RowSource. Примеры.

В примерах используется событие пользовательской формы UserForm_Initialize, реализуемое в модуле формы. Это очень удобно при тестировании, когда запуск формы или кода приводит к одному результату. Кроме того, из модуля формы обращаться к форме можно с помощью ключевого слова «Me».

Создайте в редакторе VBA Excel пользовательскую форму с любым именем и разместите на ней список с именем ListBox1. Вставляйте в модуль формы код примера, запускайте код или форму и смотрите результат.

Чтобы запустить форму, фокус должен быть на ее проекте или на одном из ее элементов управления. Чтобы запустить код, курсор должен быть в одной из его строк. Запускается код или форма нажатием клавиши «F5» или треугольной кнопки «Run Sub/UserForm»:

Заполнение ListBox методом AddItem

Метод AddItem используется для загрузки отдельного элемента в ListBox. Он создает в списке новую строку и записывает в нее значение. Используя цикл, можно загрузить в ListBox одномерный массив.

Пример 1
Загрузка элементов в ListBox по отдельности:

Результат работы кода:

Пример 2
Загрузка данных в ListBox из одномерного массива при помощи цикла VBA Excel:

Заполнение ListBox с помощью свойства List

Свойство List позволяет в коде VBA Excel скопировать целиком одномерный или двухмерный массив значений в элемент управления ListBox. А также добавлять данные в элементы двухмерного списка по их индексам в строки, созданные методом AddItem.

Пример 3
Заполнение списка данными из одномерного массива.

Загрузка значений, возвращенных функцией Array:

Загрузка значений из переменной одномерного массива:

Пример 4
Заполнение списка данными из двухмерного массива.

Результат получается следующий:

Пример 5
Заполнение списка с тремя столбцами по каждому элементу отдельно. Создаем строку и записываем значение в первый столбец методом AddItem. Значения во второй и третий столбцы записываем с помощью свойства List по индексам:

Результат работы кода будет таким же, как в Примере 4.

Заполнение ListBox с помощью свойства RowSource

Свойство RowSource позволяет загрузить в элемент управления ListBox значения из диапазона ячеек на рабочем листе Excel. Задать адрес диапазона свойству RowSource можно как в ходе выполнения кода VBA, так и в окне Properties элемента управления ListBox.

Адрес диапазона ячеек для свойства RowSource указывается по следующей формуле: "Имя_листа!Адрес_диапазона" . Имя_листа соответствует имени листа по ярлыку. Адрес в окне Properties вводится без парных кавычек.

Если адрес диапазона указать без имени рабочего листа, то данные будут загружаться в список из соответствующего диапазона активного листа. Если имя рабочего листа содержит пробелы, то его следует заключить в одинарные кавычки: "’Данные для списка’!A1:A10" .

Пример 6
Импорт данных в одностолбцовый список из диапазона «A1:A7» рабочего листа «Лист1»:

Пример 7
Импорт данных в четырехстолбцовый список с заголовками из диапазона «A2:D4» рабочего листа «Лист1» (заголовки импортируются автоматически из диапазона «A1:D1»):

Другая информация об элементе управления ListBox представлена в отдельной статье.