Выпадающий список с мультивыбором
Классический выпадающий список на листе 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-й строке кода на свой (например, пробел или точку с запятой).
У меня вопрос в следующем:
"Вариант 3. С накоплением в той же ячейке"-используется для составления списка запчастей в ячейке I3, выпадающий список берет названия из определенного прайс листа ( сделанном на отдельном листе EXEL).
В прайс листе есть соответственно ячейка с названием, артикулом и ценой
Как теперь сделать чтобы в ячейке К3 производилось суммирование цен на эти запчасти взятые из прайс листа.
Буду очень благодарен совету и помощи в реализации.
P.S. В перспективе надеюсь сделать общую форму в которой будут прописываться артикулы названия и цены для заказа запчастей на производстве.
Заранее большое спасибо!
Как создать выпадающий список из всех значений А1 по всем листам из другой книги.
Вот я на Вашем сайте нашел очень много полезного для себя.
И тут совершенно недавно передо мной встала проблема - мне необходимо сделать "мини калькулятор"
Объясню:
Такая проблема - мне необходимо сделать выпадающий список, как только я в этом списке выбираю ингредиент - то хочу что бы в соседней колонке сразу прописывалась его цена. Соответственно затем уже эта цена уже работала с другой формулой.
Такое возможно?
По работе столкнулся со следующей необходимостью - необходимо в выпадающем списке выбирать дату, при этом при выборе значения (даты) необходим переход к необходимой ячейке (гиперссылка).
Как это реализовать?
При использовании второго примера "Вертикальный список" столкнулся с такой проблемой. Если ячейка выбора значений является объединенной из двух макрос не работает. И еще одна просьба. Возможно ли ячейку выбора списка совместить с "шапкой" столбца. Что бы она была не пустой, а допустим с надписью "Фамилия" или любой другой. Это было бы полезно. Спасибо.
- в таблице файла несколько столбцов со списками и все они стали работать по варианту "С накоплением в той же ячейке", не только "F" и "I";
- в случае, если что-то забыл набрать и требуется вписать строку таблицы между двумя существующими строками, то чистая строка не вставляется (как это работает без кода VBA);
- при попытке скопировать строку таблицы, чтобы её затем вставить ниже, скопированная строка таблицы не вставляется;
- при попытке вырезать строку таблицы, строка принимает очертания скопированной области (но остаётся видимой), а при операции "вставить" просто удаляется из таблицы (информация, содержащаяся в строке удаляется полностью);
- стрелка "Отменить ввод" после каждого ввода становиться неактивной (отменить операцию невозможно).
Вопросы.
1. Как сделать так, чтобы файл позволял вставлять строку между уже набранными строками таблицы, не удаляя существующую в таблице информацию?
2. Как сделать так, чтобы файл позволял копировать (вырезать), а затем вставлять скопированную строку в нужном месте таблицы без потери информации?
3. Как сделать так, чтобы файл позволял отменять операции?
4. Как сделать так, чтобы файл распространял код VBA только на указанные столбцы, а не на всю таблицу?
Приобрела вашу книгу, пока тестирую).
Пытаюсь создать связанные выпадающие списки с помощью формулы СМЕЩ(..) .
В вашем примере на стр.22 поняла как вы исключаете дублирование наименований для марки авто (1уровень), то есть диапазон списка составлен из уникальных наименований марок.
А что если уровней несколько и для последующих необходимо сделать то же самое.
Как сделать так, чтобы в выпадающем списке наименования не дублировались: например, выбрав toyota в следующем уровне чтобы corolla camry carina, а не corolla corolla corolla camry camry camry и т.д.
Спасибо заранее
спасибо
Подскажите пожалуйста как создать выпадающий список заполняющий сразу несколько ячеек и с определённым периодом в 2-3 ячейки.
Хотел поинтересоваться, можно ли совместить элемент ActiveX (поле со списком) и второй вариант (добавление выбранных значений вниз от указанной ячейки)?
Сделала выпадающий список с мультивыбором по варианту № 3 .
Но не могу редактировать ячейку, если выбор сделан неверный - захожу в ячейку, удаляю лишнее, нажимаю Ввод - все удаленное восстанавливается в двойном виде (защита от введения данных не из предустановленного списка снята).
Спасибо за информативный и очень полезный материал!
Возникли вопросы:
1. Можно ли использовать данный прием (в частности по 1-му способу) с выпадающим списком ActiveX ?. Я попробовал, но в указанную в поле LincedCell ячейку просто записывается значение из списка... Обработчик не "срабатывает", вероятно событие не происходит? Это удобно, например, если использовать в отображении выпадающего списка несколько столбцов (чтобы правильно выбрать), а выводить в ячейку - результирующее (нужное) значение. Как пример - сотрудник и его табельный номер. В выпадающем списке отображается Фамилия и Табельный номер, а в ячейку заносится табельный номер. Это нельзя реализовать с помощью списка из Проверки данных, но из элемента ActiveX - можно.
2. Как можно сделать (в том же 1-м способе - горизонтальном), чтобы значение добавлялось не в ячейку, что идет сразу за "целевой", а например, через заданное кол-во ячеек? Т.е. например, список находится в ячейке С1, а мне нужно, чтобы значения выводились в строке не начиная со следующей ячейки D1, а с ячейки F1, и т.д.
3. Тут, если можно так сделать: хотелось бы, чтобы ячейка для введения значений из списка находилась на одном листе, а макрос с формирующимися значениями - на другом листе. Это, например, нужно, чтобы на "рабочем" листе не формировать таких списков. Т.е. для формирования "диапазона данных" как бы использовать "промежуточный лист", чтобы не "захламлять" рабочий лист вычислениями и данными. Просто выбор производится на одном листе, а "вычисления" - на другом.
Буду благодарен за ответы.
Мне интересен 3-й вариант, но почему-то он у меня не работает правильно. Сделал все как описано, список выпадает, но значения только заменяются, а не накапливаются через "," как должно быть. Разрешение в настройках на макросы есть. В чем может быть причина? Подобный комментарий уже видел. Файл примера также не работает правильно.
Спасибо!
Подскажите пожалуйста по примеру №3.
Как ограничить максимальное количество выборов (например не больше 10)? В таком случае, как сделать так, чтобы после каждого слова добавлялась запятая, (не только между словами), а после последнего возможного (т.е. 10-го значения) точка?
Если нужно могу сбросить файлик.
Спасибо.