Выпадающий список с мультивыбором
Классический выпадающий список на листе Excel - отличная штука, но позволяет выбрать только один вариант из представленного набора. Иногда именно это и нужно, но бывают ситуации, когда пользователь должен иметь возможность выбрать несколько элементов из списка.
Давайте рассмотрим несколько типовых вариантов реализации такого списка с мультивыбором.
Вариант 1. Горизонтальный
Пользователь выбирает из выпадающего списка элементы один за другим, и они появляются справа от изменяемой ячейки, автоматически составляясь в список по горизонтали:
Выпадающие списки в ячейках С2:С5 в данном примере создаются стандартным образом, т.е.
- выделить ячейки С2:С5
- на вкладке или в меню Данные (Data) выбрать команду Проверка данных (Data Validation)
- в открывшемся окне выбрать вариант Список (List) и указать в качестве диапазона Источник (Source) ячейки с исходными данными для списка A1:A8
Затем в модуль листа нужно добавить макрос, который и будет делать всю основную работу, т.е. добавлять выбранные значения справа от зеленых ячеек. Для этого щелкните правой кнопкой мыши по ярлычку листа с выпадающими списками и выберите команду Исходный текст (Source code). В открывшееся окно редактора Visual Basic нужно вставить следующий код:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C2:C5")) 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
При необходимости, замените во второй строке этого кода чувствительный диапазон выпадающих списков С2:С5 на свой.
Вариант 2. Вертикальный
То же самое, что и в предыдущем варианте, но новые выбранные значения добавляются не справа, а снизу:
Делается совершенно аналогично, но немного меняется код макроса обработчика:
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
Опять же, при необходимости, замените во второй строке этого кода чувствительный диапазон выпадающих списков С2:F2 на свой.
Вариант 3. С накоплением в той же ячейке
В этом варианте накопление происходит в той же ячейке, где расположен выпадающий список. Выбранные элементы разделяются любым заданным символом (например, запятой):
Выпадающие списки в зеленых ячейках создаются совершенно стандартно, как и в предыдущих способах. Всю работу делает, опять же, макрос в модуле листа:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C2:C5")) 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
При желании, можно заменить символ-разделитель (запятую) в 9-й строке кода на свой (например, пробел или точку с запятой).
Почему-то у меня при нажатии на ссылку "Скачать пример" открывается та же страница в новой вкладке.
При попытке добавить значение, которого нет в списке, макрос ломается. Хотелось бы, чтобы при попытке добавить новое значение выдавало окно с ошибкой... Как быть в такой ситуации? Что необходимо дописать?
Макрос работает. Набираем несколько значений, здесь ок. Далее добавляем свой текст в ячейку после набранных значений. уходим из ячейки, все данные прописанные в ячейки удваиваются и т.д.
Николай, а можно ли сделать, чтобы на одном листе обрабатывалось более одного списка?
Спасибо!
Range("C2:C5,D2:D10,F15")Огромное спасибо за сайт, и за ваш труд.
Вопросик, возможно ли использование третьего примера, при условии что данные которые вводятся в выпадающем списке, находятся на другом листе?
Николай не могли бы вы подсказать или пример, или какой функцией реализовать,
У меня выпадающий список с названием организаций 7шт., и я бы хотел , чтобы при выборе какой либо из списка , строчки с реквизитами выбранной организации заполнялись автоматически, это реально ?
заранее благодарю, уже даже за выпадающие списки
Николай, очень обрадовалась когда нашла именно то, что мне было нужно - выбор с накоплением в той же ячейке
Маленький, но важный вопрос - есть ли возможность фильтрации по столбцу в котором данные идут с накоплением по первичным значениям?
Excel воспринимает строки с данными полученными накоплением как новые полные данные, а мне необходимо из множествастрок в столбце выбирать те которые содержат первичное значения.
Из доступных способов фижу только фильт->текстовый фильтр->содержит... Но хотелось бы иметь более простой инструмент, либо сводную таблицу.
Например: список у меня в ячейке B1, в нем я выбираю нужную позицию, и эта позиция должна отобразиться в ячейке A1 (т.е. со смещением влево). При этом, следующая выбранная позиция из списка, должна отобразиться сразу под A1, то есть в A2.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("B2")) 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(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End SubПомогите пожалуйста, никак не могу додуматься.
Спасибо!
p.s. отличный сайт! открыл для себя новую америку)))
Как сделать так чтобы список работал на нескольких листах одной книги? Опыт в работе с макросами = новичок.
Спасибо!
УРА ПОБЕДИЛ, ПРОСТО ДОБАВИЛ МАКРОС В МОДУЛЬ КАЖДОЙ СТРАНИЦИ КНИГИ!
И не только... проявились интересные последствия! При корректировке значений от руки в свободных ячейках (т.е. там где нет ни формул, ни списков), если заранее не удалить предыдущее изменяемое значение (полностью не очистить ячейку), новое значение не заменяет его, а дополняется через установленный разделитель.
Как это можно побороть? И как корректно прописать диапазон ячеек, в которых действует макрос, если подобный способ вставки необходим только в четырех рядом находящихся столбцах?
Спасибо.
Поправьте: варианты 1 и 2 называются одинаково.
Спасибо за подсказки, очень облегчает жизнь и работу.
Помогите пожалуйста решить проблему с применением третьего варианта. Как сделать так, чтобы пользователь не мог повторно ввести одно и то же значение? Например, появляется соответствующее уведомление или из выпадающего списка будет удаляться уже введенное значение. Спасибо.
PS Может кому пригодится: вместо разделителя можно использовать chr(10), тогда новое значение будет переноситься на новую строку в данной ячейке
Создала список с мультивыбором, все отлично работает, сохраняю книгу в формате: Лист Microsoft Office Excel с поддержкой макросов (.xlsm), при повторном открывание книги, список выпадает но наработает!!!
Подскажите, в чем может быть причина?
Большое спасибо за третий вариант!
Но мне необходимо занести, допустим, два подряд одинаковых значения
Подскажите, пожалуйста
убрал в 8 строке
Подскажите, пожалуйста, есть ли возможность объединить 3-й вариант с . Заранее благодарю!
А Вы какой программой делаете gif?
Огромное спасибо за вариант 3. Долго искал, много чего советовали... а тут так просто. Ещё раз спасибо.
Использовал вариант 3
Подскажите пожалуйста что нужно сделать, чтобы была возможность с клавиатуры дописывать к уже имеющимся вариантам свои.
Сейчас если сделать выбор одного варианта, а потом попробовать что-нибудь дописать свое, дублируется выбранный вариант еще раз.
Спасибо.
Использую 2 вариант.
Необходимо ограничить количество заполняющихся ячеек до 6.
Подскажите пожалуйста что для этого необходимо.
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 If WorksheetFunction.CountA(Target.Offset(1,0).Resize(6,1))=6 Then MsgBox "Можно выбрать максимум 6 значений!" Exit Sub End If 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Извиняюсь за вопрос. Есть список из 10 строк, он заполнен наполовину. Другие ячейки ссылаются на этот список, но при выборе значений из списка отображаются все 10 значений, из которых 5 названных, а 5 пустые. Как сделать так, чтобы в раскрывающимся списке отображались только указанные 5 позиций, а не все 10 (с пятью пустыми)?
Подскажите пожалуйста, как применить Вариант 1 к Листу 1, если сам код и расположить на Листе 2?
1. как в макросе (Вариант 3. С накоплением в той же ячейке) исправить такую ошибку: при удалении одного из элементов в одной ячейке и нажатии на Enter этот макрос не удаляет, а наоборот, клонирует и добавляет элементы в ту же строку после запятой.
2. как применить каждый из вариантов только к одному столбцу в одном листе?
3. Как правильно вставить в окно VBA одновременно несколько макросов по всем 3-м вариантам? Я пробовал, но пишет, что нужны какие-то разделительные кавычки между разными макросами
бывает, необходимо просто скопировать заполненные значения ячейки вниз на несколько строк, но при попытке копирования, все ячейки становятся пустыми, как избежать этого? Необходимо с 3 вариантом, с накоплением информации в той же ячейке.
Подскажите, пожалуйста, как решить проблемку, использую макрос вариант 3, с накоплением в той же ячейке, но стоит следующая задача, каждому значению в ячейке (а это текст) присвоить цифровое значение (1), чтобы в следующей ячейке автоматически подсчитывалось количество выбранных вариантов. При этом идут 2 ячейки подряд предположим А2 и В2 с выпадающими списками с накоплением (предположи варианты в списке а, б, в, г, д, е (в ячейках А2 и В2 одинаковый список)), нужно, чтобы в ячейке С2 выводилось количество выбранных вариантов в ячейках А2 и В2. При этом заранее не известно и не ограничено количество выбранных вариантов из списка + выбранные варианты могут повторяться (на крайний случай можно поставить ограничение 10 вариантов). Основная проблема в том, что в ячейке выбрано несколько вариантов
Заранее спасибо!
=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";"")+1
где А1 - ячейка с выпадающим списком
=ЕСЛИ(A1="";0;=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";"")+1 )
Я понимаю, что как один из вариантов список для данных можно заложить в самый низ листа, от туда они точно не удалятся ), однако редактировать не очень удобно будет. Спасибо за совет.
Есть определенный диапазон вводимых данных по строке 2 в 4 столбцах как у вас, ниже такой же скрипт с вводимым диапазоном данных другой категории, и т.д. ниже, но с обязательным условие. Чтобы строки сами добавлялись и соответственно скрипт перемещался на строку ниже от выбранных данных. Не как у вас фиксированно к примеру 6 строк с С2:С8 а одна активная, между скриптами одна разделительная строка и перемещались бы они автоматически в зависимости от колличества выбранных данных. За ранее спасибо. Пример очень поучителем.
Подскажите пожалуйста по Варианту № 3: какая строка макроса указывает на диапозон ячеек, из занчений которого составляется список
По варианту 3 - уже несколько раз писали о том, что в ячейке нельзя ничего исправлять (например, удалить один из неправильно выбранных вариантов), т.к. макрос дописывает полностью новый вариант содержания к тому содержанию, что уже было в ячейке.
Например, если в Вашем варианте выбрать "Петя, Даша, Иван", а потом в ячейке убрать "Иван" (останется "Петя, Даша"), то после ввода в ячейке будет "Петя, Даша, Иван, Петя, Даша".
Вы не подскажете можно ли как-то подправить код макроса, чтобы избежать такой ошибки?
Подскажите, пожалуйста, используя макрос вариант 3, рядом со столбцом источника, есть столбик цен. Можно сделать так, чтобы рядом с ячейкой, где накапливается выбранные пункты, возникала сумма цен выбранных элементов?
P.S. на листе 3 столбца с выпадающим списком, но срабатывает с мультивыбором только один, как сделать чтобы все работали?
Подскажите пожалуйста, возможно ли составить все три макроса в модуле одного листа. Когда я пробую сделать все так как описано, макросы не работают. Я так понимаю дело в названии, ибо когда было поставлено Private Worksheet_Change и в первом и во втором, то естественно он не вопринимает одинаково названные макросы, а если сменить название, то код не работает, не подскажите как разрешается данный вопрос? Спасибо!
Подскажите, пожалуйста, как редактировать файл с этим макросом (способ 3)? Его необходимо отключать постоянно, чтобы внести изменения или есть другой способ?
Файл постоянно дополняется данными, а этот макрос упрощает работу в 1000 раз....
Спасибо!