Как добавить значение в выпадающий список в excel

Выпадающий список в ячейке листа

У кого мало времени и нужно быстро ухватить суть — смотрим обучающее видео: Кому интересны подробности и нюансы всех описанных способов — дальше по тексту.

Способ 1. Примитивный

Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:

Способ 2. Стандартный

1. Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
2. Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить(Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).

Нажмите ОК.

Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку. Еще одним часто используемым трюком для таких списков является создание связанных выпадающих списков (когда содержимое одного списка меняется в зависимости от выбора в другом).

Способ 3. Элемент управления

Этот способ представляет собой вставку на лист нового объекта — элемента управления «поле со списком» с последующей привязкой его к диапазонам на листе. Для этого:

1. В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel— флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
2. Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком:

• Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые должны попасть в список
• Связь с ячейкой — укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
• Количество строк списка — сколько строк показывать в выпадающем списке. По умолчанию — 8, но можно больше, чего не позволяет предыдущий способ.

После нажатия на ОК списком можно пользоваться.

Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:

Способ 4. Элемент ActiveX

Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):

Механизм добавления тот же — выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.

Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):

Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:

Самые нужные и полезные свойства, которые можно и нужно настроить:

• ListFillRange — диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
• LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент
• ListRows — количество отображаемых строк
• Font — шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
• ForeColor и BackColor — цвет текста и фона, соответственно

Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)

При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:

Итоговая сравнительная таблица всех способов

Способ 1. Примитивный	Способ 2. Стандартный	Способ 3. Элемент управления	Способ 4. Элемент ActiveX
Сложность	низкая	средняя	высокая	высокая
Возможность настройки шрифта, цвета и т.д.	нет	нет	нет	да
Количество отображаемых строк	всегда 8	всегда 8	любое	любое
Быстрый поиск элемента по первым буквам	нет	нет	нет	да
Необходимость использования дополнительной функции ИНДЕКС	нет	нет	да	нет
Возможность создания связанных выпадающих списков	нет	да	нет	нет

Ссылки по теме:

• Выпадающий список с данными из другого файла
• Создание зависимых выпадающих списков
• Автоматическое создание выпадающих списков надстройкой PLEX
• Выбор фото из выпадающего списка
• Автоматическое удаление уже использованных элементов из выпадающего списка
• Выпадающий список с автоматическим добавлением новых элементов

Выпадающий список с добавлением новых элементов

Предположим, что у нас есть справочник с именами сотрудников и таблица, куда этих сотрудников нужно вносить:
Задача состоит из двух частей:

• Сделать выпадающий список, причем так, чтобы при дописывании новых людей к справочнику — они автоматически появлялись и в выпадающем списке.
• Реализовать возможность добавления новых людей в список и с другой стороны — при вводе нового имени в любую из жёлтых ячеек оно должно автоматически добавляться к справочнику (и в выпадающий список в будущем, само-собой).

Такая вот двухсторонняя связь справочника и выпадающего списка.

Шаг 1. Создаем умную таблицу


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

Для этого выделим весь справочник (ячейки A1:A7) и нажмём сочетание клавиш Ctrl + T или выберем Главная — Форматировать как таблицу (Home — Format as Table) . В следующем окне можно смело жать ОК:



Шаг 2. Создаем динамический именованный диапазон


Теперь создадим именованный диапазон, указывающий на заполненные именами ячейки в нашем справочнике. Для этого выделим в справочнике уже только имена без шапки (ячейки A2:A7) и в левой части строки формул (там будет имя таблицы) введём имя для нашего диапазона (например Люди):



После ввода имени обязательно нужно нажать на клавишу Enter — слово Люди исчезнет из этого поля, но диапазон будет создан.

Хитрость тут в том, что поскольку мы выделяли столбец уже в «умной» таблице, то и именованный диапазон у нас получился завязанным на колонку [Справочник], а не на конкретные выделенные ячейки. Убедиться в этом можно, если выбрать на вкладке Формулы команду Диспетчер имен (Formulas — Name Manager) и посмотреть куда ссылается имя Люди:



Таким образом, при дописывании новых имен к справочнику будет расширяться наша «умная» Таблица1, а за ней и наш именованный диапазон Люди.

Шаг 3. Создаем выпадающий список в ячейке

Выделяем жёлтые ячейки и жмем на вкладке Данные (Data) кнопку Проверка данных (Data Validation)

Далее выбираем из выпадающего списка Тип данных (Allow) позицию Список (List) и вводим в строку Источник (Source) ссылку на созданный на шаге 1 именованный диапазон ( не забудьте перед именем диапазона поставить знак равенства! ):



Чтобы Excel позволил нам в будущем ввести в список и новые имена, снимем галочки на вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert) и нажмем ОК. Выпадающий список готов!

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



Шаг 4. Добавляем простой макрос

Теперь вставим в нашу книгу простой макрос, который будет отслеживать ввод в жёлтые ячейки и при вводе незнакомых людей добавлять их справочнику.

Щёлкаем правой кнопкой мыши по ярлычку нашего листа и выбираем Просмотреть код (View Source) . Откроется модуль листа в редакторе Visual Basic, куда надо скопировать такой код:

Private Sub Worksheet_Change(ByVal Target As Range) Set p = Range("Люди") If Target.Cells.Count > 1 Then Exit Sub If IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D2:D10")) Is Nothing Then If WorksheetFunction.CountIf(p, Target) = 0 Then r = MsgBox("Добавить новое имя в справочник?", vbYesNo) If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target End If End If End Sub

Теперь при попытке ввести новое имя в любую из жёлтых ячеек Excel будет спрашивать:



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

Ссылки по теме

• 4 способа создать выпадающий список в ячейке листа
• Как создать список из которого будут автоматически удаляться использованные элементы
• Автоматическое создание списка при помощи надстройки PLEX
• Связанные выпадающие списки (от того, что выбрано в первом — зависит содержимое второго)
• Выпадающий список для выбора изображений (фотографий товаров, сотрудников и т.д.)

03.10.2012 19:54:27
Сортировать бы еще список по алфавиту после каждого добавления.
06.10.2012 17:47:24



Да, мысль хорошая, подумаем



12.04.2020 09:51:39



03.10.2012 19:55:54



Очень полезный ресурс вы создали. Очень много интересного и нового.
Ну вот и у меня возник вопрос, причем достаточно срочный, спасайте: в данном примере (описаном выше) в качестве ячейки для выбора/ввода нового значения — указана одна ячейка. Как сделать так, чтобы при вводе данных в диапазон, к примеру, D2:D10, в любую из этих ячеек, происходила проверка и добавление в список. Прописать в стоорке If Target.Adress= и так далее 10 раз, можно через or, но мне нужен диапазон в 4000 строк . Помогите пожалуйста.

18.01.2013 12:59:56

Александр, проблема видимо в строчке (If Target.Cells.Count > 1 Then Exit Sub).
Пробовала вводить диапазон ячеек, сохраняю изменения, но при вводе информации в ячейку, Эксель не предлагает добавить ее в список.
Изменение параметра Target.Cells.Count тоже ни к чему не приводит.
У кого-нибудь есть идеи?

03.10.2012 19:56:27
Офигеть. Даже я всё понял. Круто.
03.10.2012 19:57:17

Супер сайт.
В этом примере небольшая ошибка, в английской версии формулы стоят запятые, а нужны точки с запятой. Иначе эксель ругается:).

06.10.2012 17:44:11
Спасибо, подправил.
03.10.2012 19:57:44
Спасибо за помощь,я ищу возможность делать при помощи табл Excel можно делать сметы калькуляции.
03.10.2012 20:03:32
Сайт превосходный. Огромный респект автору! Очень помогает этот сайт в работе. спасибо БОЛЬШОЕ.
27.12.2012 19:07:34

А как сделать, чтоб открывалась форма с несколькими параметрами на данного человека? Например, день рождения,адрес, телефон и т.д. Очень классный сайт.

20.01.2013 19:21:08



Это вам полноценную базу данных нужно. Копайте в сторону Access.

29.12.2012 17:25:32

Выпадающий список с добавлением новых элементов
А у меня что-то не получается, все вроде ввел как надо в всплывающем окне список есть но когда в нем пишешь новые данные то они не добавляются в основной список .

20.01.2013 19:19:44

Защита от макросов не включена, часом?
Вкладка Разработчик — Безопасность — Низкий уровень и переоткрыть Excel.

21.01.2013 12:32:25

Да все получилось, спасибо.
А подскажите еще пожалуйста,как вписать в «исходный текст не одну а несколько ячеек «$D$2» ; «$D$5»; и т. д.

22.01.2013 17:12:50

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

22.01.2013 23:40:26
Вместо 6 строки написать:

If Not Intersect(Target, Range("D2:D10")) Is Nothing Then

05.03.2013 14:45:27

• имеется таблица (порядка 600 строк) с несколькими столбцами;
• к каждому столбцу привязаны выпадающие списки (именованные диаппазоны для списков на отдельном листе).

08.03.2013 15:28:52
А почему бы аналогично не написать:

If Not Intersect(Target, Range("D2:D100000")) Is Nothing Then

Ответ на ваш второй вопрос — нет, больше 8 строк нельзя. Только, если использовать элементы управления или элемент ActiveX для создания вып.списка ( почитайте про разные способы создания списков )

11.03.2013 14:05:34



Николай, Range(«D2:D100000») не подходит. Указывая конкретные ячейки, мы ограничиваем диапазон срабатывания макроса. Поправил 6-ю строку на «If Target.Column = 4 Then» — работает .
Но теперь не работает 8-я строка, т.к. список находится на другом листе (в 12 строке Вашего примера изменения внес).

PS: про элементы управления и ActiveX я в курсе, спасибо за ссылку.



23.01.2013 12:56:30



Николай подскажите,а как сделать так чтобы список да для нескольких ячеек работал,но с другого листа брал нужную информацию, у меня где то ошибка никак не соображу.

31.01.2013 11:07:51
В 12 строке макроса нужно дописать в начало ссылку на лист со списком, т.е.

Worksheets("Лист2").Range("People").Cells(Worksheets("Лист2").Range("People").Rows.Count + 1, 1) = Target



27.01.2017 14:11:30

Здравствуйте Николай!
Откуда можно скачать пример файла где сам список на одном листе, а ячейка для выбора из списка на другом листе?



20.05.2020 01:11:00

Пример файла



Подскажите пожааста что нужно изменить или добавить в макросе, что бы на одной странице работало две или больше ячейки , и добавляли новые данные так же в разные «умные таблицы» Напримере моего файла Сотрудники — это одна я чейка , а клиенты — другая.С одной ячейкой разобрался , но вот две не получается.
p.s. Я в программировании полный нубас , в написании макросов так же .Если
будет возможность помочь ответом, то напишите опираясь на мой файл плз.Спасибо!



12.10.2020 09:10:40

Добрый день Николай, попробовал так как вы советуете:

Private Sub Worksheet_Change(ByVal Target As Range)
Set p = Range(«ГДО»)
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range(«H12:H100») Is Nothing Then
If WorksheetFunction.CountIf(p, Target) = 0 Then
r = MsgBox(«Добавить новый пункт справочника?», vbYesNo)
If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target
End If
End If
End Sub
Worksheets(«Лист1»).Range(«ГДО»).Cells(Worksheets(«Лист1»)Range(«ГДО»).Rows.Count + 1, 1) = Target

Список с данными на «Лист1», а выпадающий список на «Лист2». Но выдает ошибку: «Compile error: Only comments may appear after End Sub, End function, оr End Property.»
Если и выпадающий список и список с данными на одном листе(Лист2) то все работает.

12.10.2020 10:00:59

Геннадий, во-первых End Sub всегда должен быть в конце — это оператор завершения любого макроса.
Во-вторых, не видя вашего файла посоветовать что-то дельное — нереально.
Пришлите на почту или создайте тему на форуме — там еще быстрее помогут, скорее всего.

24.01.2013 14:08:57

Добрый день, спасибо за интересный ЛИКБЕЗ, а как сделать, чтобы заполнять несколько разных списков из разных ячеек в одной таблице: «счет №» в один список, а «наиминование» в другой. Спасибо.

26.01.2013 04:24:07

Отличный пример. то,что надо. Но задачу решаю следующую..В Вашем примере на первом лист одна таблица с ФИО и одна с выпдающими ячейками, а в моем случае.. 140 однотипичных страниц в одной книге. на каждой странице по 4 столбца с выпадающими списками. Сами списки хранятся на отдельном листе и у каждого диапазона соответственно свое название..Вопрос, как сделать, чтобы макрос обсчитывал все 140 листов и любые изменения в ячейках соответственно заносил бы в соответствующих диапазонах, в которых хранятся списки (как я уже сказал на отдельном листе книги).. Вот несколько модифицированный пример этого макроса для одного листа и одного диапазона..И еще ячейки (4 на каждом листе в одном и том же месте, но не друг за другом) с выпадающим списком у меня не друг за другом идут, т.е. их надо по отдельности прописывать в Range..Просто блоком не получится. как в Вашем последнем примере..Range(«Range(«D2:D10»;)Вот мой пример .

Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("AI8:AI27")) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("PrintingOtherCosts").Range("sheet1"), Target) = 0 Then lReply = MsgBox("Äîáàâèòü ââåäåííîå èìÿ " & Target & " â âûïàäàþùèé ñïèñîê?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("PrintingOtherCosts";).Range("sheet1").Cells(Sheets("PrintingOtherCosts").Range("sheet1").Rows.Count + 1, 1) = Target End If End If End If End Sub

29.01.2013 01:08:53

Попробуйте использовать не событие Worksheet_Change, а Workbook_SheetChange — оно срабатывает при изменении любого листа книги. Тогда не надо будет руками вносить этот макрос в модуль каждого из 140 листов.

И проблема с несмежными диапазонам проверки решается легко — просто напишите несколько диапазонов через запятую внутри кавычек в операторе проверки:
If Not Intersect(Target, Range(«AI8:AI27,C2:C45,B25») Is Nothing Then

19.02.2013 14:42:23

Здравствуйте! Отличная пошаговая инструкция. У меня то вопрос. В этом примере на листе одна таблица с ФИО и одна с выпадающими ячейками, у меня табличка с несколькими столбцами (одному я присвоила имя Form другому Manager) и таблица со столбцами Форма собственности и Первый руководитель. Осталось понять как сделать макрос, чтоб выпадающий список был в ячейках столбца Форма собственности из Form и в ячейках столбца Первый руководитель из Manager. Макрос из вашего примера сработал для Range Form в столбце Форма собственности, немного доработав его вот так:

Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("C2:C485")) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Form"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Form").Cells(Range("Form").Rows.Count + 1, 1) = Target End If End If End If End Sub

Помогите пожалуйста из этого кода сделать так чтобы и с Range Manager была такая же песня. только вместо Range(«C2:C485» было Range(«E2:E485»
Заранее благодарю!

20.02.2013 00:15:54
Да просто добавьте второй блок проверки и действий для вашего диапазона Manager. Вот так:

Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("C2:C485")) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Form"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Form").Cells(Range("Form").Rows.Count + 1, 1) = Target End If End If End If If Not Intersect(Target, Range("E2:E485")) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Manager"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Manager").Cells(Range("Manager").Rows.Count + 1, 1) = Target End If End If End If End Sub

21.02.2013 20:10:35

Сайт—песня. Но именно в данной теме, по крайней мере у меня, получился прокол.
Всё сделал, как описано (шаг 1-3). Но вставляется в последнюю строку диапазона. И только. И при этом затирает ранее введенное (на эту же последнюю строку) значение.

21.02.2013 20:57:22

Антон, скачайте пример и посмотрите как оно работает. Предполагаю, что ошибка либо в именованном диапазоне People, либо в коде макроса.

27.02.2013 13:49:13

Добрый день. Подскажите пожалуйста как обозначить столбец «АА» либо «AB», проблема именно со столбцами у которых в название из двух букв, с одной буквой все отлично работает. Мой пример:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Const a1 = "Добавить введенное имя ", a2 = " в выпадающий список?", a3 = "Gorod" If Target.Cells.Count > 1 Then Exit Sub a = Target.Address If Left(a, 3) = "$AA$" And Val(Right(a, 2)) > 29 < 34 Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range(a3), Target) = 0 Then If MsgBox(a1 & Target & a2, vbYesNo + vbQuestion) = vbYes Then _ Range(a3).Cells(Range(a3).Rows.Count + 1, 1) = Target End If End If End Sub

28.02.2013 09:38:55

В пятой строке вашего макроса надо использовать свойства Column и Row, которые выдают номер столбца и строки для изменяемой ячейки Target:

If Target.Column=27 And Target.Column>29 and Target.Column
28.02.2013 12:37:33
Спасибо, Николай. Все работает. 

If Target.Column = 27 And Target.Row > 29 And Target.Row < 34 Then
28.02.2013 21:52:10
Всем ЗДравствуйте! У меня возникла следующая проблема. При вводе в поле ИСТОЧНИК = название именного диапазона (Например"месяц"), происходит повтор букв и формула естественно не работает. Т.е. выглядит это так: = мммееесссяяцц . Что это может быть?
03.03.2013 09:05:42

Глюк клавиатуры или руки дрожат? 
04.03.2013 14:15:08
К сожалению, Николай, причина в чем-то другом. Клавиатура не глючит, когда набираю текст на обычном листе, буквы повторяются именно при вводе в поле ИСТОЧНИК , и больше никогда (одно нажатие - дает сразу две одинаковые буквы). Проверила все галочки, сравнила с компом на работе. Всё везде одинаково, но на работе всё получается, а дома нет; точнее сказать получается и дома, просто вручную убираю лишние буквы. Возможно совпадение, возможно нет, но так стало, после того, как внезапно отключили в квартире электричество , потом естественно комп. перезагрузила, всё функционирует нормально, кроме ЭТОГО ммеессяяяцц! . Я думала есть какая-то хитрая галочка, которую я не вижу, а она мне всё портит.Ну всё равно, спасибо за ответ, продолжу поиск загадочной "галочки".
08.03.2013 15:14:41
Тогда встречный вопрос - что у вас за версия Office? Лицензионная или пиратка? Обновлялись последний раз когда? Может дело в этом?
Я бы на вашем месте, как минимум, для начала попробовал переустановить Office.
30.10.2014 12:27:18
Войдите в надстройки Excel и отключите ненужные (подозрительные) надстройки.
Мне помогло. Были включены каки-то PDF расширения.
12.03.2013 15:53:48

Спасибо за Ваш ресурс, но у меня одна проблема - на что нужно заменить "Добавить введенное имя " и " в выпадающий список?" в англ. версии Excel 2007, чтобы макрос работал. А то у меня вместо русского текста показывает вопросительные знаки. Помогите пожалуйста. 
14.03.2013 22:39:29
Сотрите вопросительные знаки и напишите этот текст еще раз по-русски - при копировании сбилась кодировка кириллицы, скорее всего.
15.03.2013 18:35:09

Случайно разобрался.) Работает с данными на другом листе. Может кому-нибудь нужно.
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$D$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Worksheets("Лист2").Range("People"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("Лист2").Range("People").Cells(Worksheets("Лист2").Range("People").Rows.Count + 1, 1) = Target End If End If End If End Sub

13.09.2020 19:25:50

не работае ваш макрос.

18.03.2013 12:02:34
Спасибо Виталий, но у меня выводит желтым следующую строку и дальше не идет
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("PrNos"), Target) = 0 Then
Мой средний ум буксует, может кто путное что подскажет.
Спасибо!
18.03.2013 12:21:02
Интересно: включил пошаговое выполнение, редактор по очереди окрасил желтим все строки кода и после этого работает без проблем - новые данные в выподающий список вставляет.

Спасибо за сайт и за помощь ВСЕМ ЗНАТОКАМ и особенно АВТОРУ 
18.03.2013 13:25:08
Еще возник вопрос у меня национальная клавиатура и настройли редактора я поменял. В окне кода сообщение отоброжаетса праильно а вот в таблице специфические буквы(ā,ī,š) ошибочно.

22.03.2013 13:33:48
Здравствуйте! Николай подскажите пожалуйста в чем ошибка (при попытке внести новое имя выскакивает макрос и выделяет цветом первую строку)? У меня офис 2010 и данные на одном листе, а таблица с ячейками куда вставлять данные на другом. И еще почему-то введя формулу =СМЕЩ(паспорта!$A$1;0;0;СЧЁТЗ(паспорта!$A$1:$A$100000);1) почему-то появляется список по порядку с другого запрашиваемого листа.
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("F2:F10") Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("клиентданные", Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("паспорта".Range("клиентданные".Cells(Range("клиентданные".Rows.Count + 1, 1) = Target End If End If End If End Sub
Заранее спасибо, ( на примере на одном листе пробовала все получалось правильно).
13.07.2013 17:45:46
Скачиваю пример, открываю, не работает – новое имя позволяет ввести, а в список не добавляет? В чем дело может быть?
24.09.2013 00:10:05
Private Sub Worksheet_Change(ByVal Target As Range)
Доброй ночи, такой вот макрос ошибку найти не могу
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub 
If Not Intersect(Target, Range("f2:f100000")) Is Nothing Then 
If IsEmpty(Target) Then Exit Sub 
If WorksheetFunction.CountIf(Sheets("списки").Range("people"), Target) = 0 Then 
lReply = MsgBox("Äîáàâèòü ââåäåííîå èìÿ " & Target & " â âûïàäàþùèé ñïèñîê?", vbYesNo + vbQuestion) 
If lReply = vbYes Then 
Sheets("списки").Range("people").Cells(Sheets("списки").Range("people").Rows.Count + 1, 1) = Target 
End If 
End If 
End If
End Sub
Не знаю что не так, новая запись в список не добавляется.Спасибо
25.11.2013 01:52:22
так как получилось у кого прикрутить к макросу упорядочить по алфавиту создаваемого диапазона 
- чтобы в выпадающем списке всегда были строчи по порядку. Поделитесь примером пожалуйста
25.11.2013 16:40:34
На форуме спрашивали как сделать так, чтобы в список не только добавлялись новые значения, но и сортировались по алфавиту, у меня это получилось с помощью следующего кода:
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("D2:D10")) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Лист2").Range("people"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("Лист2").Range("People").Cells(Worksheets("Лист2").Range("People").Rows.Count + 1, 1) = Target End If End If End If Sheets("Лист2").Range("A1:A1000").Sort Key1:=Sheets("Лист2").Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'этот код и поможет отсортировать в алфавитном порядке End Sub
надеюсь кому то поможет 8)

29.11.2013 10:18:53

Доброго времени суток! Сайт очень радует, и реально заражает Excel-ем)))
Стал активно юзать данный ресурс, и применять приемы в своей работе.
Относительно «Выпадающий список с добавлением новых элементов»:
-хотелось бы еще снабдить его очень полезным действием как интеллектуальное предложение данных из списка на вроде Т9 по первым набранным данным… Думаю выразился не очень но как то так))). Заранее СПС. 
18.12.2013 16:40:25

в формуле есть СЧЕТ3.
=СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A$1:$A$24);1)
что это?

05.01.2014 09:46:43
Добрый день!
Хочу поблагодарить за этот замечательный ресурс! Подобных в интернете еще не встречала. Очень помогает! 
Николай, у меня вопрос. Модифицировала данный макрос для работы с повторяющимся выпадающим списком в нужном мне столбце. Данные при этом находятся на другом листе. Также добавила сортировку по алфавиту (спасибо другим участникам форума за советы). Получился следующий макрос:
Private Sub Workbook_SheetChange(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub 
If Not Intersect(Target, Range("H6:H30")) Is Nothing Then 
If IsEmpty(Target) Then Exit Sub 
If WorksheetFunction.CountIf(Worksheets("hidden2").Range("stores"), Target) = 0 Then 
lReply = MsgBox("Would you like to add new store " & Target & " to your database?", vbYesNo + vbQuestion) 
If lReply = vbYes Then 
Worksheets("hidden2").Range("stores").Cells(Worksheets("hidden2").Range("stores").Rows.Count + 1, 1) = Target 
End If 
End If 
End If
Sheets("hidden2").Range("C1:C1000").Sort Key1:=Sheets("hidden2").Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal 'this cod will help to range your stores 
End Sub
Вопрос в следующем: 
У меня в книге 12 листов с повторяющимися данными и, соответственно, одинаковым выпадающим списком. Попробовала применить выше описанную замену события Worksheet_Change на Workbook_SheetChange. Не работает. Что я делаю не так? Также можно ли в макросе просто прописать названия страниц или он будет работать для всей книги в целом (у меня есть лист итогов, где мне данный макрос на тех же самых ячейках не нужен совсем)?
И еще один вопрос. В данном макросе если вводишь новое название в ячейку и нажимаешь потом не на подтверждение, а на команду отмены, введенное слово в ячейке все равно остается (в список, естественно, не добавляется). Можно ли прописать какую-то команду, чтобы в случае нажатия отмены введенное слово удалялось автоматически и ячейка оставалась пустой? (p.s. некоторые пользователи нажимают отмену, и вроде информация в таблице есть, а в списке нет).
05.01.2014 15:25:36
Анна,
код надо вставлять уже не в модуль листа, а в модуль книг, т.к. он SheetChange - это уже событие книги, а не отдельного листа. Для этого нажмите Alt+F11 и в левом верхнем углу найдите и откройте двойным щелчком модуль ЭтаКнига (ThisWorkbook) .
Туда нужно вставить вот такой подкорректированный код макроса:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub 'если выделено больше одной ячейки - выходим If Sh.Name = "Итог" Then Exit Sub 'если это лист Итог - выходим If IsEmpty(Target) Then Exit Sub 'если ячейку очистили - выходим If Not Intersect(Target, Range("H6:H30")) Is Nothing Then Application.EnableEvents = False If WorksheetFunction.CountIf(Worksheets("hidden2").Range("stores"), Target) = 0 Then lReply = MsgBox("Would you like to add new store " & Target & " to your database?", vbYesNo + vbQuestion) If lReply = vbYes Then 'если нажали ДА - добавляем новый элемент к списку Worksheets("hidden2").Range("stores").Cells(Worksheets("hidden2").Range("stores").Rows.Count + 1, 1) = Target Else 'если нажали НЕТ - очищаем ячейку Target.ClearContents End If End If End If 'сортируем список Sheets("hidden2").Range("C1:C1000").Sort Key1:=Sheets("hidden2").Range("C1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal Application.EnableEvents = True End Sub
13.01.2014 14:46:21
Николай, спасибо за полезный сайт. Создал выпадающий список по инструкции, но при его открытии, курсор списка находится ниже записей, т.е. приходится вручную подвигать курсор вверх списка, чтобы выбрать один из вариантов. В чем может быть дело ? Что сделать, чтобы список открывался с самого верха ? Спасибо.
21.01.2014 18:21:02

Здравствуйте!
Огромное спасибо, за столько много советов. У меня возникла проблема, если можно так сказать. У меня в фаиле связаные выпадаюсчие списки, которые сделаны по вашему методу с помощью формулы INDIRECT. Но вот когда я добавляю новое наименование, добавляется целая строка, а не ячейка в нужном столбце. Скажите, пожалуйста, как ето обоити?
P.S. Извините за русский, я из Риги. 
30.01.2014 11:29:00
Николай, добрый день, спасибо вам огромное за великолепный сайт и доброе отношение ко всем его посетителям независимо от их опыта и навыков. С вами постигаю VBA.
А вы можете показать на вашем коде как реализовать добавление новых элементов в форме. Т.е. есть форма, в ней ComboBox со своим RowSource. Как добавить новый элемент в этом случае? Т.е. чтобы набрать его в поле ComboBox, а он бы добавился и в RowSource потом без создания/октрытия дополнительных форм.
03.02.2014 18:12:30
Добрый вечер! Помогите пожалуйста решить задачу)) как быть, если есть 5 книг (идентичных) с выпадающими списками с возможностью добавления новых элементов, и надо, чтобы добавленный элемент в книге 1 появлялся и в списках остальных книг?

05.03.2014 15:10:02
Добрый день. Сайт действительно полезный. Сделал все как в примерах - работает! С добавлением проблем нет. Подскажите пожалуйста, какой код надо добавить чтоб еще и удалив из выпадающего списка удалялось из таблицы

01.04.2014 14:32:53
Здравствуйте Николай.
Огромное СПАСИБО за ВАШ сайт - очень много полезного!
С первого раза разобрался с данным примером. Сделал выпадающий список + добавление новых элементов+сортировка БАЗЫ элементов.
У меня получлся такой макрос:
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("D2:D100000")) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("People"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("People").Cells(Range("People").Rows.Count + 1, 1) = Target End If End If End If Range("People").Sort Key1:=Range("People"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'this cod will help to range your stores End Sub
Остался единственный вопрос.
Можно ли прикрутить к данному макросу еще и автоподстановку (автоподбор) значений по первым вводимым символам? 
Так как имеется перечень з/частей (более 100) и отбор осуществляется по значению кода з/ч (ПРИМЕР: 200.235.145.010).
Нашел в сети Надстройку, но . к ней не прикручивается выше приведенный макрос!
Заранее спасибо!
С уважением,
Сергей.
Пы.Сы. Пытался прикрутить к вышеуказанному макросу НАДСТРОЙКУ " Надстройка: выпадающий список с поиском (комбо) " (DropDownList) - Мои познания или руки оказались кривыми!

01.04.2014 20:13:53

Удалось прикрутить:D

16.04.2014 09:53:14

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

16.04.2014 11:00:52

стучи на скайп: drinkerdp

30.10.2014 17:32:21

Поделитесь радостью? ))

16.04.2014 10:33:55

жаль, что на Android этот макрос не отрабатывает 
Выпадающий список только.

16.04.2014 11:03:55

на Android у тебя стоит тоже Excel??

16.04.2014 11:18:27

KingSoft Office стоит, многие стандартные фишки Excell тянет без проблем 
Но при вводе новых данных, то есть когда отрабатывает макрос, вываливает ошибку
The value is not valid! The cell can only accept list format within effective range!
А работать с таблицей приходится в 95% случаев на телефоне

08.05.2014 10:17:12

Вот тут я не помощник - андроидов у меня ни одного в жизни еще не было, как-то не сложилось у меня с ними 
Может кто из коллег поможет - попробуйте на форуме тему озвучить.
Вообще говоря, не видел ни одного ни телефона, ни планшета, ни программы, где работали бы макросы. Только Surface и полноценный Office, но это уже полноценный компьютер, фактически.
24.04.2014 11:10:04
А почему никто не пользуется "умными таблицами"? По мне, так очень удобно. Вместо ограничения диапазона в макросе, либо назначение определенному диапазону, сделав свою таблицу "умной", использовать в качестве нужного диапазона, готовое имя столбца - вместо . Range("People"). использовать . Range("Таблица1[Столбец1]"). При этом, диапазон будет расширяться динамически, в зависимости от необходимого размера)
08.05.2014 10:12:19
Пользуемся, еще как пользуемся. У них, правда, свои ограничения (совместимость со старыми версиями и т.д.)
24.06.2014 10:57:30

Просто отличная штука! Спасибо за сайт!
В макросах не разбираюсь, просто на примерах и ваших подсказках пытаюсь подкрутить под себя. Есть вопрос - как сделать чтобы выпадало не полностью по столбцу D2: D20, а D2. D4. D6 и так далее. конечно можно копировать блок и полставлять так для каждой ячейки(D2. D4. D6), но видимо можно просто изменить как-то строку?
Влюбом случае спасибо! 
30.07.2014 09:55:45

Спасибо автору сайта и всем советчикам! Все получилось и так как нужно!

26.08.2014 05:06:55
Доброго времени. Очень понравились толковые разъяснения, помогли во многом.
Николай, возможно, Вы сможете и мне помочь: есть файл, в котором
таблица находится на одном листе, а ввожу в нее данные с другого листа (этой же книги). Excel 2003
Нужно, чтобы добавлялись несколько разных значений ФИО, возраст и т.п.
По Вашему примеру Все работает, но только если таких данных еще нет. Есть ли возможность чтобы в таблицу вносились и дублируемые данные, которые уже есть в таком столбце, т.е. ниже имеющихся сведений добавлялась следующая запись. Например возраст может быть у разных людей одинаковым, как и некоторые другие данные, или если человек обратился несколько раз - информация о нем должна быть внесена.
Но, возможно, Вы сможете посоветовать более простой способ ввода информации в таблицу, из ячеек которые находятся на другом листе?

26.10.2014 00:15:09
Помогите пожалуйста в решении следующей проблемы. В моём случае именованный диапазон ссылается на ячейки содержащие формулы. Диапазон 50 ячеек, некоторые со значением, остальные пустые. Проблема в том, когда открываешь выпадающий список, то в нём содержится весь диапазон из 50 ячеек, и со значениями, и пустые. Подскажите, как избавиться от показа пустых ячеек в выпадающем списке?

05.08.2015 19:30:04
Николай добрый вечер!
Помогите!
Выскакивает ошибка на строке 
If Not Intersect(Target, Range("C2:C24")) Is Nothing Then
В чем может быть проблема?

02.11.2015 19:35:33
Здравствуйте! Можно ли изменить код, что бы можно было не только добавлять, но и удалять уже введённый значения задавая при этом контрольный вопрос? Спасибо.

09.02.2016 10:30:13
День добрый! Помогите пожалуйста сделать в файле выпадающие списки с добавлением новых элементов в ячейках F2,F3, F4, F5, K7, K8, K9, K10, Z23, Z24, I29, K29, M29, O29, . Пробовал сделать с помощью макроса, получается только один сделать, дальше не выходит.

11.02.2016 17:20:33

Разобрался наконец-то )))

24.02.2016 17:33:45
Добрый день!
Здесь представлен пример для одной ячейки, а как будет выглядеть макрос для нескольких ячеек со своими разными списками?

01.03.2016 15:42:52

Всем спасибо за активность . хоть кто-нибудь ткнул см. выше. Разобрался сам, всем спасибо.

27.07.2018 17:45:17
Добрый день! Подскажите, пожалуйста, как вам удалось создать макрос для нескольких ячеек со своими разными списками?

28.02.2016 18:51:41
Добрый день ! подскажите пожалуйста как реализовать несколько выпадающих списков с добавлением новых элементов. допустим на одном листе есть 4-5 выпадающих списков на втором листе есть именованные диапазоны. с одним списком получилось сделать так как здесь было показано. но не получается применить это к другим спискам.
Вот как реализован с одним выпадающим
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub 
If Not Intersect(Target, Range("C2")) Is Nothing Then 
If IsEmpty(Target) Then Exit Sub 
If WorksheetFunction.CountIf(Sheets("ДЛЯ УТС ФОРМУЛА").Range("Марки"), Target) = 0 Then 
lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) 
If lReply = vbYes Then 
Worksheets("ДЛЯ УТС ФОРМУЛА").Range("Марки").Cells(Worksheets("ДЛЯ УТС ФОРМУЛА").Range("Марки").Rows.Count + 1, 1) = Target 
End If 
End If 
End If
Sheets("ДЛЯ УТС ФОРМУЛА").Range("D2:D1000").Sort Key1:=Sheets("ДЛЯ УТС ФОРМУЛА").Range("D2"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal
End Sub
Что и как нужно добавить что бы это работало на нескольких выпадающих.
И еще - что нужно добавить что бы при вводе первых букв в выпадающий список происходил поиск данных в выпадающем списке!

10.03.2016 15:25:20
Николай, спасибо за ресурс! Интересные вещи нашел для себя именно тут. У меня следующий вопрос. Как на одном листе в одной книги объединить два события: выпадающий список с мультивыбором и выпадающий список с добавлением новых элементов? Дело в том, что VBA ругается при обнаружении двух одинаковых событий Private Sub Worksheet_Change(ByVal Target As Range). как же их объединить.

21.03.2016 09:06:55
Добрый день!
А подскажите, возможна ли доработка этого макроса для того, чтобы он заносил новые данные не постоянно в один список, а в список, имя которго введено в ячейке А1, например?
На листе находятся 10 именованных списков, в ячейке А1 выпадающий список с именами этих списков, в ячейках А2:Е2 собственно, данные, которые нужно занести в список, имя которого выбрано в А1.
Спасибо!

10.04.2016 01:46:53
Всем привет. Подскажите как изменить выше указанный макрос. Задача такая. Оговорюсь сразу что список связанный.
Существует лист со списком, где перечислены все основные компоненты. И так же создан 2 Лист где происходит основная работа. Вводим данные списка, и если у нас нет значение списка, то макрос автоматически первым делом ссылался на предыдущую ячейку, чтобы узнать куда нужно добавить новое значение на первый лист. Пример
Выводим из списка первое значение -"Список1_Имя", далее во второй ячейке вводим значение которое бы хотели добавить в таблицу Листа1 - "Список1_Имя". Если в первой ячейке выбрано -"Список2_Имя", то макрос бы искал в какую таблицу добавить значение.

14.04.2016 20:44:44
Прошу помощи. А если применительно к Вашему примеру, каждому имени соответствуют какие-то значения находящееся, например, в следующих двух столбцах. Пусть это будет номер телефона и e-mail, к примеру. Можно ли сделать, что бы при добавлении нового имени предлагалось ввести также номер телефона и почту этого человека?

04.07.2016 15:19:38
Здравствуйте! Благодарю за сайт, и хочу задать вопрос. Как сделать выпадающий список, но что бы в соседних ячейках менялось значение, например Артикул, цена, Страна производитель. и т.д. ? То-есть менялось значение нескольких ячеек, данные из прайс-листа. Надо для облегчения расчета Спецификации Заказа.
Пробовал по схеме Выбор фото из выпадающего списка , но не получается почему то.

20.07.2016 07:52:31

Товарищи! Все работает, новые данные добавляет, но только в одну и ту же ячейку. то есть один раз добавляет "МАША", другое имя, напр. "СВЕТА", добавляется сюда же, но взамен "МАША". Как бы сделать, чтоб добавлял новую ячейку и уже туда добавлял новое имя?
30.01.2022 14:40:59
в макросе надо указать имя которое вы задали в диспетчере имен, он на нее ссылается и добавляет в новую ячейку

22.07.2016 09:52:12
Подскажите, пожалуйста. Можно ли сделать так, что при выборе какого-либо объекта из списка автоматически (ну или как-то ещё) открывался другой заданный лист
30.01.2022 14:39:22

может просто активировать лист
ActiveSheet.Activate
или с именем 
Sheets("db").Activate

13.09.2016 12:21:36
Так же как и многие пытаюсь усовершенствовать код под свою задачу. с примером разобрался все работает, В моем случае нужно при добавлении нового элемента, сделать ввод данных в соседнюю ячейку посредством InputBox. данные.
Подробнее о задумке, динамический список с маршрутами на первом листе. на втором листе таблица с данными. маршрут и расстояние. 
На первом листе из списка выбираем "расстояние", в соседнюю ячейку на первом листе заносятся данные из столбца "расстояние" второго листа. (через функцию "ВПР"). Другими словами обеспечить наполнение таблицы с данными не только названием, а и другими данными. 
надеюсь не запутал вас.

01.10.2016 21:26:21
Невыходит с макросом если, выпадающая ячейка и список находится на разных листах. Кто нибудь знает как макрос подправить?
30.01.2022 14:34:27

=ДВССЫЛ("db!Изделие1";)
=> проверка ввод данных

02.11.2016 16:46:58
Помогите, пожалуйста, 
не удаётся ни выбрать значения из списка значений, ни добавить новое
ошибка 1004 
и выделяет жёлтым строку If WorksheetFunction.CountIf(Range("koeffb"), Target) = 0 Then

28.11.2016 10:00:58
Всем доброго времени суток. 
Помогите, пожалуйста. Какие изменения нужно сделать (в макросе или ещё где), чтобы вся конструкция "
Выпадающий список с добавлением новых элементов", при нахождении списка и ячейки ввода на разных страницах, работала.

24.01.2017 16:31:24

Огромное спасибо! Очень полезная вещь!

25.01.2017 18:04:07
Очень классный пример. 
Подскажите пожалуйста, а как можно реализовать этот пример, без привязки в коде к области справочника
If WorksheetFunction.CountIf(Range("People"), Target) = 0 Then
Господа помогите, пожалуйста.
Не могу понять как написать макрос, который можно применить в этом куске кода (это часть макроса, которая указывает в каком списке искать новое значение, и в какой список его добавлять). Проблема в указании имении списка.
Необходимо искать имя списка, которое храниться в диапазоне "Searchlist" (два столбца:наименование и имя списка), в ячейке слева от выпадающего списка. В источнике выпадающего списка пишу такую формулу:
=ВПР(ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1);ИСТИНА);SearchList;2;0)
список вызывается, но не осуществляется проверка и добавление нового эелемента.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub 
If Not Intersect(Target, Range("Table.Report[[Столбец 1]:[Столбец 3]]";)) Is Nothing Then 
If IsEmpty(Target) Then Exit Sub 
If WorksheetFunction.CountIf(Worksheets("DBLists";).Range("ВПР(ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1);ИСТИНА);SearchList;2;0)";), Target) = 0 Then 
lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) 
If lReply = vbYes Then 
Worksheets("DBLists";).Range("List№1";).Cells(Worksheets("DBLists";).Range("ВПР(ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1);ИСТИНА);SearchList;2;0)";).Rows.Count + 1, 1) = Target 
End If 
End If 
End If
End Sub

22.02.2017 22:55:38
Добрый вечер всем, подскажите, как к этому выпадающему списку, добавить поиск по вводимым буквам, к примеру, по первым 2-3 буквам (предлагал из имеющегося списка варианты),а новые чтобы добавлял с авто сортировкой по алфавиту. спасибо

01.04.2017 20:09:47

в колонке E список "Класс"
в колонках G, H, I, J, K, список "Осн"
в колонках L, M, N, O, P, список "Втор"
в колонках Q, R, S список "Гнез"


26.09.2017 14:13:48
Добрый день!
У меня следующая задача: В ячейке В2 организован выпадающий список, на основе двух именованных диапазонов "Хлеб" и "Сыр", которые выбираются в зависимости от значения в ячейке Е4 (1 или 2). Необходимо модифицировать имеющийся макрос (см. ниже), который бы позволял добавлять новые элементы в "Хлеб" или "Сыр" в зависимости от значения ячейки Е4. Данные макрос добавляет элементы только в один диапазон.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub 
If Target.Address = "$B$2" Then 
If IsEmpty(Target) Then Exit Sub 
If WorksheetFunction.CountIf(Sheets(2).Range("Хлеб", Target) = 0 Then 
lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) 
If lReply = vbYes Then 
Sheets("Список".Range("Хлеб".Cells(Sheets("Список".Range("Хлеб".Rows.Count + 1, 1) = Target 
End If 
End If 
End If
End Sub
Заранее благодарен за помощь.

26.10.2017 23:20:36

подскажите пожалуйста, а как можно прописать этот пример на combobox?

13.11.2017 08:37:06
есть даные на 1 и 2 лист Excel правери их если есть одинакивае даны то ответь паказат на 3 листь как это делаеть. Пажалуста ответе скарй.

04.01.2018 07:50:26
Здравствуйте! Спасибо большое за информацию. Подскажите пожалуйста как делать зависимый выпадающий список таким же как и у вас, с возможностью вносить изменения. Заранее благодарю!

29.03.2018 18:32:48
Здравствуйте! Помогите пожалуйста. Есть список на одном листе, ячейка с выпадающем списком для выбора на другом листе. В списке порядка 200 строк, активно пользуются примерно 50, как скрыть временно не нужные строки в списке, что бы они не мешались при выборе. Если скрыть в списке строки любым путем, они все равно видны при выборе.

06.04.2018 12:13:35
Добрый день, форумчане!
Помогите, пожалуйста, объединить два макроса в один
Private Sub Worksheet_Change( ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub 
If Target.Address = "$D$2" Then 
If IsEmpty(Target) Then Exit Sub 
If WorksheetFunction.CountIf(Range("People", Target) = 0 Then 
lReply = MsgBox("Добавить введенное имя " & _ 
Target & " в выпадающий список?", vbYesNo + vbQuestion) 
If lReply = vbYes Then 
Range("People".Cells(Range("People".Rows.Count + 1, 1) = Target 
End If 
End If 
End If 
End Sub 
Private Sub Worksheet_Change(ByVal Target As Range) 
For Each cell In Target ' 
If Not Intersect(cell, Range("A3:A100" Is Nothing Then ' 
With cell.Offset(0, 1) ' 
Application.Calculation = xlManual 
.Value = Now 
Application.Calculation = xlAutomatic 
End With 
End If 
Next cell 
For Each cell In Target ' 
If Not Intersect(cell, Range("F3:F100" Is Nothing And cell Like "Устранено*" Then ' 
With cell.Offset(0, 1) ' 
Application.Calculation = xlManual 
.Value = Now 
Application.Calculation = xlAutomatic 
End With 
End If 
Next cell
End Sub

06.06.2018 23:50:41
Запилил макрос с список с поиском по буквам и вводом новых значений на другой лист
Option Explicit Option Compare Text Dim bu As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Row = 1 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub If Target.Column = 3 Then ' номер столбца, в который вносим значения bu = True With Me.TextBox1 .Top = Target.Top: .Text = Target.Value: .Activate End With With Me.ListBox1 .Top = Target.Top + 5 If (.Top + .Height + ActiveWindow.PointsToScreenPixelsY(0) * Application.InchesToPoints(1) * 15 / 1440) > _ (ActiveWindow.Application.Height + ActiveWindow.Application.Top) Then _ .Top = .Top - .Height + Target.Height '* ActiveWindow.Zoom / 100 .Clear End With bu = False Me.TextBox1.Visible = True: Me.ListBox1.Visible = True Else Me.TextBox1.Visible = False: Me.ListBox1.Visible = False End If End Sub Private Sub TextBox1_Change() If Len(TextBox1.Text) = 0 Or bu Then Exit Sub 'при отсутствии символов для поиска - выход Dim x, i As Long, txt As String, lt As Long, s As String txt = TextBox1.Text: lt = Len(TextBox1.Text) 'Где ищем значения x = Sheets("номенклатура";).Columns(1).SpecialCells(2).Offset(1).Value ' For i = 1 To UBound(x, 1) ' поиск по первым буквам 'If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" For i = 1 To UBound(x, 1) 'поиск по любому вхождению If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1) Next i ListBox1.List = Split(s, "~";) End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Or KeyCode = 9 Then With Me.TextBox1 ActiveCell.Value = .Value .Visible = False: ListBox1.Visible = False End With ActiveCell(2, 1).Select End If End Sub Private Sub ListBox1_Click() If ListBox1.ListIndex = -1 Then Exit Sub Application.EnableEvents = False bu = True With Me.ListBox1 ActiveCell.Value = .Value Me.TextBox1.Text = .Value Me.TextBox1.Visible = False: .Visible = False End With Application.EnableEvents = True bu = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Column = 2 Then Exit Sub If Not Intersect(Target, Range("C2:C100000";)) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("номенклатура";).Columns(1), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("номенклатура";).Range("номенклатура";).Cells(Worksheets("номенклатура";).Range("номенклатура";).Rows.Count + 1, 1) = Target End If End If End If Sheets("номенклатура";).Range("номенклатура";).Sort Key1:=Sheets("номенклатура";).Range("A1";), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'этот код и поможет отсортировать в алфавитном порядке' End Sub

01.07.2018 11:54:41
Ничего не получается. У меня чуть чуть отличается таблица - и всё, уже ничего не работает. Жаль что нету пошагового видео на эту тему.

22.02.2019 20:34:28

Добрый вечер. Помогите, пожалуйста. Был создан выпадающий список и все было прекрасно. Но сейчас, по какой-то причине, я не могу добавить новое значение. Встаю на ячейку, куда необходимо внести новое значение, добавляю в Источник через ;, Ставлю Галку Распространить изменения на другие ячейки и ничего не происходит Выделения всего столбца не происходит. Новое значение в списке не появляется. В других столбцах, где тоже есть списки, все работает. Что я не так делаю? Спасибо!

14.06.2019 18:47:34
Добрый вечер.
Помогите написать правильно в макрос формулу ВПР это для того, чтобы при выборе в выпадающем списке "формула" происходило вычисление по заданной формуле.
У меня все таблицы с данными:
- Искомое значение в ВПР находиться на том же листе что и ячейка с выпадающим списком F2, то есть "Заявка" столбец "Серийный номер компонента" в нем первое значение "E2"
- Таблица находиться на другом листе "ActiveComponentDetails" диапазон A:BA
- Номер столбца на том же листе - 37
В данном виде выделяет данную строчку желтым.
Спасибо большое.
Private Sub Worksheet_Change(ByVal Target As Range) 
If Target <> [F2] Then Exit Sub 
If Target.Value = "формула" Then Target.Formula = "=ЕСЛИОШИБКА(ВПР([@[Серийный номер компонента]];ActiveComponentDetails!A:BA;37;0);"""
End Sub
27.09.2019 22:02:41
пару месяцев назад тут писал вариант выпадающего списка с добавлением новых элементов с помощью UDF, было бы неплохо дополнить статью данным приемом

21.12.2019 18:38:47
Добрый день, 
можно ли сделать на одном листе несколько раскрывающихся списков и под каждый из списков будет свой справочник на том же листе?
что надо в макросе менять (кроме ссылок на диапазоны)?

14.01.2020 07:57:00
Очень полезный урок! Но что если "справочник" находится на одном листе, а рабочая таблица на другом? 
Мне данная кодировка не помогает. Что там нужно менять? Поможете?

13.02.2020 12:30:26
Добрый день. А можно ли чтобы Справочник находился в другом файле(книге) на листе. Чтобы динамический диапазон и справочник находился в разных файлах.Как изменится макрос. Если не трудно написать макрос в этой теме
Как добавить выпадающий список в ячейку Excel

В различных случаях вам может пригодится умение добавлять выпадающие списки в ячейки Excel, и в сегодняшней статье рассмотрим как это делать.
1.Откройте таблицу Excel, в которой хотите сделать ячейки с выпадающими списками. Перечень значений для выпадающего списка сначала нужно где-то записать, можно на этом же листе, но далеко внизу или создать дополнительный лист (как мы делаем в нашем примере) и туда записать значения для создания выпадающего списка. Нажимаем на” +” внизу, чтобы добавить еще один лист.

2.Открываем созданный лист (нажатием по нему левой клавишей мыши) => в любом месте в столбик начинаем вводить значения, которые нужны будут в списке. К примеру, мы в первом столбике начиная с первой строки (можно делать в любом столбике и начиная с любой строки) вводим в каждой ячейке нужные значения (которые будут отображаться в выпадающем списке).

3.Когда введете все значения, выделите ячейки с данными (используя мышь или удерживая клавишу shift и двигаясь стрелками в нужном направлении). Когда ячейки с данными выделите – выше введите имя для диапазона выбранных ячеек и нажмите Enter (смотрите рисунок). В нашем примере мы пишем имя “Цена” и нажимаем Enter.

4.Откройте лист и выберите ячейку в которую вы хотите добавить выпадающий список => на верхней панели выберите “Данные” => откройте левой клавишей мыши “Проверка данных”.

5.В поле “Тип данных” из выпадающего списка выберите “Список”

6.Снимите галочку с “Игнорировать пустые ячейки” и в поле “Источник” напишите = и имя, которое вы присвоили диапазону ячеек в 3ем пункте. Мы, в данном примере, диапазону ячеек присвоили имя “Цена”, по этому в поле “Источник” нам нужно написать =Цена 

7. Перейдите во вкладку “Сообщения для ввода”, здесь вы можете написать сообщение, которое будет отображаться при выборе ячейки со списком. После ввода нужного сообщения нажмите “ОК”.

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

Нажав на стрелку рядом с ячейкой вы увидите список, который создали в 2ом пункте и сможете выбрать нужное.

Если в будущем у вас возникнет надобность убрать выпадающий список из какой-то ячейки: выберите ячейку с выпадающим списком => на верхней панели выберите “Данные” => откройте левой клавишей мыши “Проверка данных” (описывалось и изображение было в 4ом пункте).
Нажмите левой клавишей мыши на “Очистить всё”.


Пользуясь инструкцией выше, вы можете создать различные выпадающие списки и добавить их в нужные ячейки. На сегодня всё, если у вас есть дополнения – пишите комментарии! Удачи Вам ��
Высококвалифицированный бухгалтер! Хороший специалист в своей области, и просто очень хороший человечек 🙂
Двухуровневый выпадающий список в Excel
Создание двухуровнего выпадающего списка - задача довольно простая если знать, как к ней приступить и требует всего нескольких минут.
Имеется несколько способов создания выпадающего списка. Выбор одного из них зависит от структуры имеющихся у вас данных.
Первый способ создания двухуровнего списка
Первый способ основывается на создание «умной» таблицы, заголовок которой содержит значения первого выпадающего списка (группы), а строки таблицы соответствуют значениям второго выпадающего списка (подгруппы). Значения элементов подгруппы должны располагаться в соответствующем столбце группы, как на рисунке ниже.
Теперь приступим к созданию первого выпадающего списка группы (в моем случае - список стран):

Выберите ячейку, в которую будете вставлять выпадающий список;
Переходим на вкладку ленты Данные;
Выбираем команду Проверка данных;
В выпадающем списке выбираем значение Список;
В поле Источник указываем следующую формулу =ДВССЫЛ("Таблица1[#Заголовки]").

Формула ДВССЫЛ возвращает ссылку на диапазон заголовков «умной» таблицы. Преимущество использования такой таблицы в том, что при добавлении столбцов, выпадающий список будет автоматически расширен.
Осталось создать второй зависимый выпадающий список – список подгрупп.
Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ("Таблица1["&F2&"]"). Ячейка F2 в данном случае - значение первого выпадающего списка.
Можно использовать и обычную «неумную» таблицу, однако в этом случае вам придется вручную изменять диапазоны заголовков и строк. В рассмотренном примере это происходит автоматически.
Второй способ создания двухуровнего списка
Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором – подгруппы.
ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.
Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.
Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.
Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями.
Теперь самая сложная часть - указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

Ссылка в нашем случае - $A$1 - верхний левый угол исходной таблицы;
Смещ_по_строкам - ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 - номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
Cмещ_по_столбцам - 1 - так как нам необходим столбец с подгруппами (городами);
[Высота] - СЧЁТЕСЛИ($A$1:$A$67;F3) - количество подгрупп в искомой группе (количество городов в стране F3);
[Ширина] - 1 - так как это ширина нашего столбца с подгруппами.

Читать:
Как улучшить качество печати принтера canon