Надстройка: выпадающий список с поиском (комбо)

Скриншот формы ввода (выпадающий список с поиском)

Надстройка для облегчения ввода значений в ячейку Excel

Автор: nerv
Last Update: 27/03/2012

Вам часто приходится заниматься заполнением электронных таблиц, долго и муторно выбирать варианты из выпадающих списков?

А, может, иметь дело с одними теми же, но не структурированными данными?

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

Как это работает:

По нажатию Ctrl+Enter рядом с выделенной ячейкой появляется список, который позволяет не только выбирать, но и производить поиск по интересующим Вас данным.

Посмотрим, что он умеет:

 

  • Не содержит повторов (уникальный). Легко выявить однотипные данные;
  • Отсортирован по возрастанию. Возможность быстро найти то, что нужно;
  • После вызова сразу готов к поиску/выбору из списка. Лишние движения ни к чему;
  • Позволяет искать с использованием специальных подстановочных символов (*,?,~ и т.п.);
  • Осуществлять быстрый поиск по "шаблону". Если ячейка, из которой был вызван список, содержит информацию, поиск будет произведен по ней;
  • Появляется рядом с текущей/активной ячейкой и не "убегает" за пределы экрана;
  • Навигация привычными стандартными клавишами: Up [Вверх], Down [Вниз], Page Up [На страницу Вверх ], Page Down [На страницу вниз];
  • Корректная работа со всеми типами данных: строки, даты, числа;
  • Обработка ошибок формул листа. Никаких пустых строк в списке;
  • Обработка защиты ячеек листа. В защищенные ячейки ввод запрещен;
  • Информация об общем количестве списка и найденных по запросу элементах;
  • Быстрый вызов по нажатию Ctrl+Enter;
  • Быстрое закрытие: клавиша Esc;
  • Быстрый ввод клавишей Enter

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

Отличия версии 1.6 от 1.5:

  • новая, более мощная/быстрая процедура сортировки;
  • переход после ввода на следующую ячейку (в зависимости от установок Excel);

Добавлены настройки:

  • использования и формирования списка (подробнее во вложении "how to use");
  • поиска с учетом регистра и без него;
  • маски поиска;
  • заголовков.
Вложения:

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".

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

На вопросы по этой надстройке больше отвечать некому.
Надстройка не поддерживается, не дорабатывается, - если хотите её использовать, то используйте в таком виде, в каком она сейчас есть.
Если нужно что-то аналогичное, - оформляйте заказ на сайте, сделаем «с нуля»

Уважаемый Игорь!
При работе у меня был сформирован список к примеру с 500 позиций в одном столбце, но уникальных элементов 100, возможно ли скопировать или экспортировать эти 100 уникальных элементов?
Спасибо за надстройку!!!

Люди добрые! Оч полезная надстройка! Но вот у меня одна проблема когда в двух соседних столбцах расположен "автопоиск" выдает ошибку (Unknown error). Допустим автопоиск настроен на ячейку А2 и Второй на В2. записан макрос на автозапуск
Sub va()
'
' va Макрос
'

'
Application.Run "nerv_DropDownList.DropDownListShow"
End Sub

и этот макрос запускается командой
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lReply As Long

If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A2:A2000"), Target) Is Nothing Then
Application.Run "va"
End If
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B2:B2000"), Target) Is Nothing Then
Application.Run "va"

End Sub
При переходе на ячейку А2 выходит окно поиска ввожу данные нажимаю enter, происходит переход на след ячейку B2 и выскакивает ошибка Unknown error!
Помогите советом что сделать !?

>Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла
Тот же вопрос: можно ли в качестве "Шаблона Подстановки" использовать другую книгу?
Благодарю за шикарную надстройку.

ИНДЕКС() + ПОИСКПОЗ() Вам в помощь.

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

Добрый день.

Макрос прекрасен!

Подскажите пожалуйста, как его можно привязать к personal.xls или рабочему файлу со списками, чтобы избежать запуска

Здравствуйте!
А сколько строк эта надстройка может обработать?
Можно ли сделать чтобы обрабатывала 200 000

Скажите, пожалуйста, как выбрать сразу несколько значений. Чтобы в одной ячейке было "Апельсин; Лайм". Это возможно?

Здравствуйте! Могли бы Вы обновить файл nerv_DropDownList_1.6.zip? Файл скачивается, запускается excel, но ничего не открывается и ошибки не выдает. Спасибо!

Подскажите, как сделать так, чтобы можно было формировать запрос из другого файла

Отличная штука, спасибо!

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

>> /////и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).

>> Это легко можно организовать штатными методами екселя - /Данные/Проверка данных/список, указав предварительно созданный именованный динамический список из тогоже диапазона что и обсуждаемая надстройка.

Excel 2007 - не получается так задать, макрос обходит запрет

Супер. Спасибо Автору!

Сочетание Ctrl+Enter уже используется в EXCEL: "Выделите ячейки, даже несмежные, введите то, что Вам нужно и нажмите Ctrl + Enter. Такой подход работает с датами, строками, числами и даже формулами. Этот способ позволяет сэкономить время как при вводе новых данных, так и при замене уже существующих."

Добрый день. Хочу связаться с автором по поводу разработки такой же функциональности под Excel for Mac. Помогите, пожалуйста. (на оба почтовых ящика, указанных выше, отправлял письма, ответа не получил :( )

Надстройка просто супер но мне нужно чтобы работала она на excel 2003 а она не поддержуется 2003-м может кто-то посоветовать чтото подобное.

Макрос Работает! Хотел добавить что-бы ячейку с данными с лева захватывало и с права (например не просто название а "КОД" "НАЗВАНИЕ" "ЦЕНА", то есть макрос ищет товар по наименованию но при выборе нужно что бы подбирался код в ячейке левее.

Денег не просить но спрашивало пароль после удаления записей на странице "Ноутбуки" (уже не спрашивает)

Поковыряться понял что не получится )

Здравствуйте, Сергей.

Макрос работает? Работает
Денег с вас просит? Нет

с чего он вдруг платный-то стал?
а если хотите поковыряться в коде, - такой возможности нет

Зачем вам лазить в код надстройки?
Все, что вы описали, можно сделать макросами в вашем файле.

Отличная вещь, то что искал, я так понял макрос платный? Когда я хотел поуберать лишние надписи после сохранения, закрытия и открытия запросило пароль.

/////и как обеспечить проверку вводимых данных (запрет на ввод данных не из списка).

Это легко можно организовать штатными методами екселя - /Данные/Проверка данных/список, указав предварительно созданный именованный динамический список из тогоже диапазона что и обсуждаемая надстройка.

не могу понять как добавлять новые значения в список: только через лист DDLSettings? или все-таки можно как-то через форму поиска?

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

Это делается отдельным макросом (в вашем файле):
по событию изменения листа Worksheet_Change, проверяете номер столбца, в котором изменена ячейка,
и, если изменения произошли в том столбце, где выбор идет через выпадающий список, - то в ячейку справа заносите дату
при помощи строки кода

target.next = Now

Спасибо, очень полезная надстройка.
Глобальная проблема для меня: как сделать чтобы после выбора из списка и нажатия Enter, в ячейке справа АВТОМАТОМ вводилась сегодняшняя дата???
Очень нужно!

к сожалению, в текущей реализации нельзя

А можно как-то изменить высоту списка, чтобы кол-во отображаемых строк в нем было больше? Спасибо.

Изменить ширину списка можно на листе натсроек

Очень удобно! Спасибо автору.
А возможно как-то изменить размер диалогового окна? сделать его больше?
Спасибо!

Неплохо было бы ввести возможность в выпадающем списке привязать еще одну колонку, связанную с поисковой (например, рядом с наименованием выводить цену из следующей колонки) и добавить в настроечный лист диапазон, который рядом выводить, а по наименованию оставлять без цены. Как в Access: там выпадающий список может содержать несколько колонок, а вносить в поле только одно. Тем более, что это реализуется не так сложно.

> Отличная надстройка. Спасибо автору. Если возможно изменить клавишу Быстрого ввода из клавиши Enter на клавишу Tab?
Пожалуйста. В данной реализации не возможно.

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

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

Доброго времени суток
Можно, но у меня нет времени : ) Когда появится неизвестно...

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

Ну мы, вроде, сделали. Просто рекомендация к вашей реализации) Советую вам задуматься над объединением надстройки с файлом.

> Весьма неудобный функционал
никто не заставляет вас ей (надстройкой) пользоваться. Сделайте свою - удобную, хорошую и "без геморроя" )

----- примечание администратора сайта ---------
то же самое хотел написать, слово в слово, - но ждал, пока автор надстройки отзовётся)
 

Весьма неудобный функционал - макросы в отдельном файле оставляют желать лучшего. Зачем такой геморрой? Исправьте.

Вячеслав, ну написано же, как связаться...
Email автора надстройки указан в конце статьи

nerv, очень крутая надстройка!

У меня есть предложение, как с вами связаться?

Добрый день!
Это гениально. Особенно для тех, кто не очень смыслит в VBA)
Возник вопрос. Если я хочу передать свой файлик другому человеку для работы, мне также обязательно ему передавать и надстройку так?

> но могу предположить источник ошибки
скорее всего так и есть, спасибо

> Именно таким образом и было определено почему в тестовом файле всё отлично, а у меня хоть убей не работает)))
: )

>здраствуйте Nerv, подскажите пож-та, если этот механизм применить для связанного списка. что нужно сделать для того чтобы список выпадал только по критериям? спасибо
Здравствуйте, Ольга. В данной реализации скорее нет, чем да.

>Осталось одно - редактируемый лист и лист с базой сейчас должны быть в одной книге. Возможно ли разнести их по разным книгам?
в будущем, вероятно, да. В данной реализации, насколько помню, - нет.

Nerv, огромное спасибо за за Вашу работу. Надстройка прекрасно работает. Даже с базой более 8000 строк на стареньком 4-м Пентиуме. Без проблем заработали и именованные диапазоны и столбцы "умной" таблицы Excel 2010. Осталось одно - редактируемый лист и лист с базой сейчас должны быть в одной книге. Возможно ли разнести их по разным книгам? Задание имени листа в виде '[имя_книги]Лист1' не прошло.

здраствуйте Nerv, подскажите пож-та, если этот механизм применить для связанного списка. что нужно сделать для того чтобы список выпадал только по критериям? спасибо.

> В крайнем случае могу посоветовать переименовать лист
Именно таким образом и было определено почему в тестовом файле всё отлично, а у меня хоть убей не работает)))

> По идее, все должно быть хорошо

Nerv, я не смотрел твой код, но могу предположить источник ошибки.
Обычно, эта ошибка появляется,
если подставлять значение переменной без принудительного указания типа, в качестве параметра в функции Worksheet
Т.е. твой макрос ищет лист с ИНДЕКСОМ 2012, а не с НАЗВАНИЕМ 2012
(по умолчанию, функция worksheets ждёт параметр числового типа)

Например, если в текстбоксе написано 2012,
то в коде worksheets(textbox1) будет ссылаться на 2012-й лист,
а worksheets(cstr(textbox1)) - на лист с именем "2012"

> Возник небольшой вопрос. Если в имени листа "Где используется" использованы только цифры (у меня листы называются 2011, 2012 ... и т.д.), то макрос не работает. Или я что-то не так делаю?
сходу затрудняюсь ответить, т.к. писалось давно ) По идее, все должно быть хорошо. В крайнем случае могу посоветовать переименовать лист

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

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

с какой целью? Что мешает нажать горячие клавиши? Если оно так необходимо, можете нажить их програмно

Спасибо огромное за надстройку. Возник небольшой вопрос. Если в имени листа "Где используется" использованы только цифры (у меня листы называются 2011, 2012 ... и т.д.), то макрос не работает. Или я что-то не так делаю?

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

Не получается применить макрос? Не удаётся изменить код под свои нужды?

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