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

Как сделать выпадающий список в 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».

    Читайте также:  Clear voice iii что это

    Создайте в редакторе 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 представлена в отдельной статье.

    Ссылка на основную публикацию
    Как развести алебастр с пва
    Не одно тысячелетие этот уникальный строительный материал используется при строительстве и ремонте помещений, для декорирования, изготовления скульптур, сосудов. Алебастр, который...
    Как подключить флешку к телевизору sony bravia
    К SONY BRAVIA можно подключить большое количество внешних устройств. Акустика, ДВД-проигрыватели, смартфоны, видеокамеры, флешки. Прочитав этот текст, вы узнаете, как...
    Как положить ламинат на осб плиту
    Пол созданный из панелей OSB является отличной основой для любого отделочного слоя. На него можно укладывать паркет, панели или керамическую...
    Как разделить колонтитулы в ворде
    СОЗДАНИЕ КОЛОНТИТУЛОВ. РАЗБИЕНИЕ НА СТРАНИЦЫ И РАЗДЕЛЫ. 1.1. Научиться разбивать текст на страницы. 1.2. Научиться создавать колонтитулы для разных разделов....
    Adblock detector