Как сделать чтобы в excel предлагал слово при вводе первых букв

Включить или отключить автоматическое завершение записей в ячейках

Microsoft Office Excel записи текста, которые вы начинаете ввести в столбец данных, если первые несколько введенных букв соответствуют существующей записи в этом столбце. Если вы хотите прекратить автоматическое завершение, этот параметр можно отключить.

1. Выберите Файл >Параметры.
2. Нажмите кнопку Дополнительныепараметры, а затем в области Параметры правки ,чтобы включить или отключить этот параметр, выберите или отключите параметр Включить автозаполнение для значений ячеок.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как сделать чтобы в excel предлагал слово при вводе первых букв

В принципе вопрос в теме. При наборе в ячейке текста, в моем случае предприятий и реквизитов, Эксель предлагает выбрать, т.е. если сочетание первых букв встречается в этом же столбце, то он предлагает.
А может ли он предложить не из этого столбца, а из другого?
А может ли он предложить не из этого столбца, и даже не из этого листа?
А может ли он предложить не из этого столбца, и даже не из этого листа, и даже не из этого файла?

17.10.2009 14:58:09

С другим листом поэкспериментировал, а вот с другим файлом.

Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 16:14:24

http://www.planetaexcel.ru/tip.php?aid=177
С другим листом поэкспериментировал, а вот с другим файлом.
Нет. Это немножко не то.
Вот в том же примере, если в столбце с фруктами в первой пустой ячейке начать вводить «б», то Эксель предложит, т.е. сразу введет в ячейку слово «банан» и «анан» будет выделено черным выделением. Я про это говорю.
Но это стандартная Экселевская функция (правда не знаю, как она называется).
А вопрос звучит так: можно ли чтобы такая же функция срабатывала, только диапазон предлагаемых значений был не из ячеек, которые выше, а был из другого листа?

17.10.2009 16:28:11
Возможно это поможет
http://www.planetaexcel.ru/forum.php?thread_id=8768
Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 20:07:10
Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 21:25:29
Так возможно такое или не возможно?
Пользователь
Сообщений: 1716 Регистрация: 01.01.1970
17.10.2009 21:29:57

Мультипликатор, Вы наверное имеете ввиду автозаполнение? Или ComboBox? Если Автозамена то это Сервис-Параметры-Орфография, если ComboBox, то можно и с другого листа и с другой книги, если она открыта.

Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 21:38:56

Мультипликатор, Вы наверное имеете ввиду автозаполнение? Или ComboBox? Если Автозамена то это Сервис-Параметры-Орфография, если ComboBox, то можно и с другого листа и с другой книги, если она открыта.
Вполне возможно, что это называется автозаполнением, по сути очень похоже. С ComboBox проблем нет — это я уже использую вовсю. Вопрос про ячейках на листах. Автозамена — это исправление ошибок, так ведь?

Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 21:47:54

Если Автозамена то это Сервис-Параметры-Орфография,
Нет. Это не Автозамена. Автозамена здесь не при чем. Скорее это автозаполнение.
Так как сделать, чтобы «автозаполнялось» значениями с другого листа?

Пользователь
Сообщений: 1716 Регистрация: 01.01.1970
17.10.2009 21:51:50

Мультипликатор, Вы наверное имеете ввиду автозаполнение? Или ComboBox? Если Автозамена то это Сервис-Параметры-Орфография, если ComboBox, то можно и с другого листа и с другой книги, если она открыта.
Вполне возможно, что это называется автозаполнением, по сути очень похоже. С ComboBox проблем нет — это я уже использую вовсю. Вопрос про ячейках на листах. Автозамена — это исправление ошибок, так ведь?

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

Господин Мультипликатор как Вас величать — уж дюже ник у Вас труднопечатаемый.

Сообщений: 60952 Регистрация: 14.09.2012
Контакты см. в профиле
17.10.2009 21:54:55

Мультипликатор, если моя память мне с кем-нибудь не изменяет, то именно этот вопрос уже поднимался. И решение (кажется) было найдено. Попробуйте различные запросы в поиске. Если тема не пропала после атаки.

Пользователь
Сообщений: 1716 Регистрация: 01.01.1970
17.10.2009 22:00:42

Не дохдался ответа. Вы бы задачу обрисовали, а то как-то я не могу понять, что вы желаете со второго листа видеть на первом. В Excel есть замечательная возможность обрабатывать события. Но для получения желаемого результата нужен четкий АЛГОРИТМ. То есть отсюда берем, сюда кладем, если случается вот ЭТО. К примеру я, образно. Поэтому не темните, давайте конкретнее.

Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 22:01:40

Господин Мультипликатор как Вас величать — уж дюже ник у Вас труднопечатаемый.
1. Вот вы говорили про КомбоБокс, мол, там все возможно и с другого листа тоже. Так это я знаю, что возможно. Автозаполнение — (я так понимаю) встроенная функция и она берёт диапазон в столбце выше вводимого. Причем, если выше есть пустые ячейки, то автозаполнения не происходит.
Но мой вопрос очень прост: возможно ли такое автозаполнение, только диапазон поставить собственный, с другого листа или файла? Если да, то как? Если нет — то вопрос закрыт.
2. Уж если я назвался Мультипликатором, то так меня и следует величать. Но если уж очень сложно, то имя моё — Евгений.

Сообщений: 60952 Регистрация: 14.09.2012
Контакты см. в профиле
17.10.2009 22:06:52

Вы бы задачу обрисовали, а то как-то я не могу понять, что вы желаете со второго листа видеть на первом.
Влад, нужно вот что: Введи в А1 слово Да, В А2 — слово Нет. Затем введи в А3 букву д 🙂

Пользователь
Сообщений: 1716 Регистрация: 01.01.1970
17.10.2009 22:07:25

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

Сообщений: 60952 Регистрация: 14.09.2012
Контакты см. в профиле
17.10.2009 22:15:28
Влад, я проиллюстрировал чуть выше 🙂
Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 22:20:52

Поэтому будьте любезны проиллюстрируйте свои слова на примере.
Пожалуйста, но чувствую шестым своим чувством, не будет в этом толку, раз Гуру молчат.

Прикрепленные файлы

• post_69087.xls (19 КБ)

Пользователь
Сообщений: 1716 Регистрация: 01.01.1970
17.10.2009 22:25:10

Вы бы задачу обрисовали, а то как-то я не могу понять, что вы желаете со второго листа видеть на первом.
Влад, нужно вот что: Введи в А1 слово Да, В А2 — слово Нет. Затем введи в А3 букву д 🙂

Ах вон оно что. Эту приблуду я только в Word отмечал. Там она сохраняет шаблоны частоповторяемых фраз в стеке памяти. Если один раз отказать перебивкой слова то потом и не предлагает. В Excel такое вижу редко (наверное потому что не пишу больших текстов) но думаю работает ТАК же. Поэтому Евгений не заблуждайтесь Excel предлагает слова не с листа а из стеков памяти, куда он эти слова загоняет по какому-то ему известному алгоритму. В доказательство — внес Да, потом нет, потом д — предлагает да, исправил на до, ввел н — предлагает нет, исправил на нит, ввел д — и УЖЕ не предлагает.

Евгений не томите, высказывайте свою идею.

Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 22:32:12
Владимир, какую идею?
Что-то не понимаю я. уже ничего. Или меня вы не понимаете.
Пользователь
Сообщений: 1716 Регистрация: 01.01.1970
17.10.2009 22:34:07

Повторюсь:
Введите вместо апельсин — ананас и все погибнет на корню.
А с другого листа — можно тупо назначить значения =Лист1!A1 и результат будет тот же. Но возникает вопрос — ЗАЧЕМ. Если есть понимание, вместе сможем найти альтернативу заполнению.

Сообщений: 60952 Регистрация: 14.09.2012
Контакты см. в профиле
17.10.2009 22:37:27

Влад, оно не то, чтобы всё погибнет, просто будет срабатывать уже после ввода следующего (в данном случае второго) символа.

Пользователь
Сообщений: 1716 Регистрация: 01.01.1970
17.10.2009 22:48:04

Все понял. Такую приблуду довольно не сложно реализовать. Евгений с какого диапазона на другом листе вы желаете проверять слова? Будет использоваться метод Find, если проверять весь лист — будет работать медленно.

Пользователь
Сообщений: 329 Регистрация: 28.07.2009
17.10.2009 22:59:15

Владимир, Юрий совершенно верно сказал: дальше идет срабатывание по следующим буквам. Если у вас стоят в столбце ананас и апперитив, то при вводе буквы «а» ничего не выдаст. А вот если вы введете после «а» «н», то выдаст(предложит) ан»анас», а если введете после «а» «п», то выдаст(предложит) ап»перитив». Такая вот функция.
А идея моя следующая.
Мне просто не интересно использовать раскрывающиеся списки, потому что неэффектно.
Я хочу при вводе в ячейку получить предложение ввести уже имеющиеся (на другом листе) данные. Например, я ввожу «г» в ячейку. Но название этой ячейки — должность руководителя предприятия. Т.е. хотелось бы, чтобы Эксель мне предложил из диапазона на другом листе (где есть и генеральный директор, и директор и исполнительный директор) г»енеральный директор».
Я бы тогда нажал ввод и у меня бы появилось в моей ячейке «генеральный директор». А в следующей ячейке, он бы мне предложил город и так далее. Вот какая идея.
Почему не подходят раскрывающиеся списки? Потому что именно на этом листе вставляются данные, т.е. если я сейчас там вставлю раскрывающийся список, то он исчезнет при следющем запуске макроса, потому что произойдет вставка значения.
Такие вот дела.
Так это возможно или нет?

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

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

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

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

Шаг 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
Добрый день. А можно ли чтобы Справочник находился в другом файле(книге) на листе. Чтобы динамический диапазон и справочник находился в разных файлах.Как изменится макрос. Если не трудно написать макрос в этой теме
Читать:
как сохранить самоуничтожающиеся фото в телеграмме