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

 

 

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

Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке.

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

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

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

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

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

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

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

Разработчик

 

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

Макрос

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

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

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

Горизонтальный список
Текст макроса:
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
указывается разделитель «//». Его можно заменить на любой знак препинания, текст или поставить пробел.

 

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

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

    If Not Intersect(Target, Range(«B2:B5»)) Is Nothing And Target.Cells.Count = 1 Then
    Target = Target & «//» & newVal

  2. Добрый день!
    Макрос с внесением нескольких значений работает. Только, как указали выше, ковычки нужно поправить.
    Скажите, а возможно ли в таком выпадающем списке сделать множественный выбор, например, флажками либо через CTRL? И что бы так же можно было размножить на много ячеек.
    Заранее благодарю и спасибо за статью!

    1. Добрый день, можно по клику на ячейку сделать отображение формы (userform) со списком или несколькими чек-боксами.

  3. Добрый день! Макрос работает, позволяет подставлять несколько значений из выпадающего списка. Однако пропала возможность вставки строк в файл, что, в моем случае делает работу с файлом невозможной. Как это исправить?

  4. А как сделать, чтобы в списке отображало значения из двух колонок, а результат только один (из первого столбца?

  5. Здравствуйте! Очень нужна ваша помощь! После долгих поисков в инете нашла у вас идеально подходящий мне по описанию макрос «Макрос выпадающего списка с внесением нескольких значений в одну ячейку». Вот только он почему-то не работает. Кавычки заменила, вставила код в рабочий лист, вышла , а в списке макросов он не появляется. Может быть, я какой-то нюанс упускаю. Буду благодарна за объяснение.

    1. Добрый день, макрос записываеться в лист и должен запускаться при изменении листа. В списке макросов его не нужно запускать.

  6. Добрый день! Спасибо за Макрос! Работает отлично, позволяет выбирать несколько значений. Но столкнулась с такой проблемой: если попытаться добавить то же самое значение второй раз, то остаётся по прежнему одно значение. Получается добавить то же самое значение, только если между ними выбрать другое значение. Возможно вы знаете как можно исправить эту проблему?

    1. Добрый день.
      в фрагменте:
      «if Len(oldval) <> 0 And oldval <> newVal Then
      Target = Target & «//» & newVal
      Else
      Target = newVal
      End If »
      попробуйте удалить : And oldval <> newVal

  7. После запуска этого макроса при изменении данных в любой ячейке,
    в изменоной ячейке появляется изменённая версия и через запятую не изменённая.

  8. Добрый день. Макрос работает, но не могу понять как сделать так что бы он сробатывал только для одного столбца где есть список. Этот Маркос распростронился на всю таблицу. Таблица стала некорректно работать при удаление строк или протягивание строк со значениями для их копирования. Прошу помочь сделать условие макроса только на один конкретный столбец. Заранее спасибо

  9. Я выбрал для себя «Макрос выпадающего списка с внесением нескольких значений в одну ячейку»

    Как сделать, чтобы :
    1) «Макрос выпадающего списка с внесением нескольких значений в одну ячейку» не давал повторно заносить одно и тоже значение данных повторно?
    2) Если сам список будет находиться на другом листе Эксель как это указать. Чтобы уже выпадающие списки брались из этого основного списка на другом листе эксель?

  10. Здравствуйте! Макрос работает, НО! Он применяется к целому листу, вместо конкретных ячеек, не доступна кнопка отмены (ctrl+z), не вставляются новые строки, не работает команда «delete» (вместо нее только команда очистить)… Как это можно исправить?

  11. Макрос выпадающего списка с несколькими значениями
    Как подкорректировать макрос так, чтобы выпадающий список был на другом листе

  12. Макрос выпадающего списка с несколькими значениями Как подкорректировать макрос так, чтобы выпадающий список был на другом листе

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.