Приветствую всех планетян.

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

В UserForm подобные задачи делал, но на листе не приходилось. Не смог придумать ничего другого как использовать ActiveX ComboBox прорисовывая его в границах выбранной ячейки. Пришлось немного помучаться с объединёнными ячейками - если они объединены в пределах столбца, то выпадающий список нужен, есь объединение затрагивает другие столбцы, то нет.
В принципе всё получилось. При вводе символа в ComboBox в списке остаются только допустимые значения. При нажатии Enter если в исходном списке нет введённого значения появляется запрос на добавление этого значения в список. При нажатии Delete - запрос на удаление значения из списка.

Но есть несколько неприятных моментов.
1. В процедуре ComboBox1_Change если убрать On Error Resume Next бывает возникает ошибка в строке Range(SelCel).Rows.AutoFit, при этом значение переменной SelCel правильное. И не смотря на ошибку AutoFit отрабатывает. Мне так и не удалось понять почему возникает ошибка, вот и  добавил On Error... У
2. Если ввести значение, но не выбрать позицию из оставшегося списка, а просто выделить другую ячейку, то остаётся введённое значение.
3. Если ввести значение у которого нет совпадения в списке и выделить другую ячейку, то введённое значение поменяется на первую позицию списка.
Если первая проблема в принципе не напрягает, мне просто интересно почему возникает ошибка - никакой системы я не обнаружил. Бывало десяток раз щёлкал по соседним ячейкам и ничего, а при следующем переключении ячеек ошибка выскакивала.

То вот вторая и третья проблемы просто неприятны. Была мысль при срабатывании Worksheet_SelectionChange делать Undo, анализировать оставшееся значение, при необходимости очищать и возвращаться в выбранную перед Undo ячейку, но как-то такой вариант не нравится.

Будут ли у кого-нибудь какие-нибудь идеи по решению вышеназванных проблем?
И ещё - т.к. задача была не срочной, то занимался урывками, бывало по несколько дней не открывал файл, то код получился какой-то корявый и я буду рад советам по оптимизации.

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

В пятницу экспериментировал с разными вариантами создания своего меню, запускал разные коды стянутые с разных сайтов, а сегодня обнаружил, что пропало контекстное меню - никакой реакции на правую кнопку. Если запустить код:то всё восстанавливается. Но стоит закрыть Excel и открыть новый файл, то опять исчезают все контекстные меню.
Самое обидное, что не знаю после какого кода оно пропало.
Подскажите пожалуйста как вернуть контекстные меню.

Всё что находил в поиске создаёт меню На ленте на вкладке Настройки. А я ленту полностью убираю - на ней уж очень много ненужных пользователю кнопочек
Самое обидное, что у меня есть файл Access который делает такое меню:
На картинке Access 2010.
Вот меня интересует возможно ли сделать подобное в Excel? К сожалению автор акцессного файла уволился и проконсультироваться у него я не могу. И посмотреть не получается. Хотя если запустить access, открыть редактор VBA, а потом открыть файл то можно посмотреть код, правда вероятно не весь, т.к. такие и подобные строки встречаются:но вот где и как создаются эти "Menu_Zagr" не нашел.
Правда эта программа использует дополнительные библиотеки: mscomct2.ocx, comctl32.ocx, mscomctl.ocx, scrrun.dll - может они нужны для создания такого меню?
Кто-нибудь может подсказать как мне сделать подобное меню в excel?

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

Что-то я затупил - не могу придумать как организовать более удобный пользователю ввод данных.
Существует таблица в которой значения заносятся из списка проверки данных. Но эти значения являются кодами и пользователю не совсем удобно помнить что какой код обозначает. Есть лист Справочник где каждому коду соответствует расшифровка. Можно ли организовать ввод данных так чтобы в выпадающем списке был код и его расшифровка, а при выборе в ячейку заносился только код. Или же список только из расшифровки кодов, а при выборе в ячейку заносился код.
В UserForm в ComboBox можно задавать несколько столбцов, а вот как это организовать на листе сразу во многих ячейках не представляю.

Столкнулся с ситуацией когда целую часть числа нужно обрабатывать от дробной, написал функцию и заметил, что результат получается с кучей знаков после точки, т.е. вместо 6.1 получается 6.09999990463256 напихал в функцию Round где нужно и где не нужно - результат тот же. Так и не могу понять где собака порылась.
Набросал пример функции где сначала складываются целые части чисел, потом дробные только два знака преобразуются в целые (умножением на 100), складываются между собой, результат делится на сто и прибавляется к сумме целых частей. Получилась такая же фигня. В примере просто складывал число с одним знаком после точки с нулём и смотрите что получилось.

Что-то никак не могу сообразить можно ли задать такой формат ячейки, чтобы в случае дробного числа отображалось всегда два десятичных разряда, но при этом в случае целого числа десятичные разряды не отображались совсем, например:
10.05 - 10.05
10.5 . 10.50
10 - 10
Похоже на общий, но он 10.5 напишет как 10.5, а нужно 10.50. Задать числовой с двумя знаками, так он 10 напишет как 10.00
Конечно можно написать макрос который будет отслеживать изменение ячейки и менять её формат в зависимости от того целое или дробное число в ней, но может существует более простой способ?

Многие функции листа присутствуют в VBA и соответственно наоборот. Но есть много функций на листе у которых нет аналога в VBA и в VBA есть функции у которых нет аналога на листе. И если для листа ещё можно написать пользовательскую функцию, включить в неё необходимую функцию VBA и использовать на листе, то в VBA приходится уже изобретать. Так может существует какой-нибудь способ вызывать из кода VBA функции листа, а я о нём просто не знаю? Например на листе есть удобная функция СЖПРОБЕЛЫ, а в VBA приходится писать специальную функцию из нескольких строк для получения такого же результата...

Когда-то давно я написал небольшую программку ещё на 2003 офисе. Потом вносил небольшие корректировки но уже на 2007. Сейчас возникла ситуация когда эта программа перестала работать на 2003. Проблема в том, что пользователю я передаю файл с запароленным кодом. У него при запуске выскакивает ошибка, но где именно непонятно. Глазами сравнил версию работающую под 2003 и версию с ошибкой - никакого криминала не нашел: никаких новых функций и т.п.
Передавать пользователю программу с открытым кодом не хочется. Можно ли сделать обработчик ошибок который напишет именно строку кода в которой возникает ошибка?

Уважаемый Николай, в нашей корпоративной сети выложили Вашу надстройку. Правда версия 2.0, но полностью открытая.
К сожалению я слишком поздно обратил на это внимание. Сейчас файлы удалены. И выкладывателю вынесено устное предупреждение (на большее у меня нет прав).  

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