Выбор из множества значений в выпадающем списке – это вопрос, которым, пожалуй, задавался любой пользователь.
В продолжение темы предыдущего обзора предлагаем вашему вниманию 2 (два) варианта решения позволяющие создать выпадающий список с множественным выбором:
Первый вариант решения позволит добавлять выбранные из выпадающего списка значения в одну ячейку, а второй - удалять значения при их повторном выборе.
Таким образом, мы получаем возможность динамического обновления содержимого ячейки в соответствии с множественным выбором, делая процесс работы более удобным и эффективным.
📢 Файл с примером размещен в конце статьи 🔽
Предложенный вариант решения является базовым, и безусловно может быть дополнен и изменен в зависимости от условий поставленной задачи.
▶️ Подготовка
Для целей данного примера создадим отдельный справочник и назначим именованный диапазон "Товар":
Именно значения из данного диапазон будем выбирать из выпадающего списка.
Чтобы в конечном итоге добиться поставленной цели для удобства мы разбили этапы решения на отдельные процедуры, которые поэтапно и рассмотрим.
▶️ Процедура создания выпадающего списка
Так как с именем диапазона для выпадающего списка мы определились выше назначим процедуру которая позволит формировать выпадающий список на листе.
🔘 Шаг 1. Простой выпадающий список
⚙️ Краткое описание:
- Представленный макрос добавляет проверку данных к диапазону ячеек в столбце B (от ячейки B2 до последней ячейки в столбце B) на активном листе.
- Формула проверки имеет значение "=Товар", которая определяет содержимое списка. Напомним, что список допустимых входных данных получен из ранее созданного именованного диапазона "Товар".
- Стиль предупреждения установлен в "xlValidAlertStop", что означает, что будет отображаться предупреждение, запрещающее пользователям вводить данные в ячейку, нарушающую правило проверки.
🔔 Обратите внимание! пример кода обрабатывает проверку для ячеек в столбце B, начиная со строки 2. Если ваши требования отличаются, то не забудьте скорректировать код.
🔘 Шаг 2. Проверка значений в столбце А
Представленная ниже процедура проверяет, было ли изменено значение в столбце A и, если да, очищает соответствующую ячейку в столбце B.
Представленная процедура принимает два аргумента: ws, который является ссылкой на рабочий лист, где применяется этот код, и targetCell который представляет ячейку, которая была изменена.
⚙️ Краткое описание:
- Оператор If Not Intersect(targetCell, ws.Range("A2:A" & LastRowB)) Is Nothing Then проверяет, находится ли targetCell в диапазоне от ячейки A2 до ячейки A в последней используемой строке в столбце B (ws.Range("A2:A" & LastRowB)).
- Если targetCell не находится в этом диапазоне, он завершает работу с инструкцией if и завершает процедуру.
- Если targetCell попадает в этот диапазон, запускается For Each цикл для перебора всех ячеек на пересечении targetCell и диапазона в столбце A.
- Каждая ячейка проверяется, является ли ее значение пустой строкой (""). Если это так, то содержимое ячейки, расположенной на один столбец справа от нее (при использовании cell.Offset(0, 1) которой ссылка сдвигается на 0 строк вниз и на 1 столбец вправо), очищается (.Resize(1, 1).ClearContents), а проверка данных удаляется (.Resize(1, 1).Validation.Delete).
- Процесс продолжается до тех пор, пока не будет проверена каждая ячейка.
📝 Примечание автора:
Мы заведомо внесли данное ограничение чтобы пользователь не мог осуществлять выбор при отсутствии данных в столбце А. На наш взгляд такой подход является приемлемым и удобным решением для поддержания консистентности данных между столбцами A и B при изменении значений.
🔘 Шаг 3. Вызов макроса
Данная процедура предназначена для использования вместе с событием изменения листа Excel (Worksheet_Change).
🔔 Обратите внимание, что данная процедуру включает два рассмотренных выше макроса.
Такой подход позволит не только создать нужный выпадающий список, но и динамически управлять обновлением списка отражая текущее состояние данных:
Также отметим, что использование отдельных процедур позволит более гибко изменять логику работы без необходимости изменения всего кода.
Но так как конечная цель все же состоит в в реализации множественного выбора рассмотрим два основных блока, которые и позволяют реализовать поставленную задачу.
▶️ Список с множественным выбором (Вариант 1)
Данный вариант решения позволит добавлять выбранные из выпадающего списка значения в одну ячейку без дублирования ранее внесенных значений.
В рамках данной процедуры осуществляется проверки того, что изменение произошло именно в одной ячейке и что данная ячейка является частью области с валидацией в документе.
В случае обнаружения изменений, она автоматически обрабатывает множественный выбор, дополняя существующий список новыми значениями в данной ячейке.
Весь процесс выполняется с отключением и включением события EnableEvents для избежания рекурсии.
▶️ Список с множественным выбором (Вариант 2)
Рассмотренный ниже вариант является альтернативным решением и позволяет удалять ранее добавленные значения при их повторном выборе.
Представленная процедура реагирует на изменения в ячейке, содержащей список с вариантами выбора.
Если в ячейке разрешено множественное значение, процедура автоматически обрабатывает изменения, обеспечивая корректное управление списком значений.
В случае, если происходит добавление нового значения, оно объединяется с существующими значениями в ячейке, поддерживая правильный формат и избегая дублирования разделителей. При этом обеспечивается визуальная оптимизация, исключая избыточные разделители в конце и в начале списка*.
* 📝 Примечание автора:
В рамках процедуры предусмотрена обработка значений, разделенных точкой с запятой внутри ячейки. Например, если одно и то же значение выбирается из раскрывающегося списка дважды, в ячейке сохраняется только один экземпляр этого значения. Если значение в ячейке заканчивается точкой с запятой, эта точка с запятой удаляется.
Также процедурой предусмотрен подсчет количество точек с запятой в значении, и если это число равно 1, то удаляем точку с запятой из значения ячейки, а также удаляет все ненужные пробелы.
▶️ Обработка множественного выбора из выпадающего списка
Теперь, когда все процедуры рассмотрены можно назначить для каждого листа событие Worksheet.Change, чтобы увидеть полученный результат.
Обращаем внимание, что код должен быть добавлен в модуль листа!
🔘 Вариант 1
➡️ Результат:
В данном случае каждый новой выбранный товар будет помещен в ячейку
🔘 Вариант 2.
➡️ Результат:
В данном случае, при повторном выборе товара, он будет исключен.
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽