Выбор значения из выпадающего списка с поиском и переходом к ячейке с таким значением в Excel

Поиск и переход к ячейке выполняется при помощи макроса. Макрос, как и сама книга, формируются программно в 1С.

Описание
Подробнее

Описание

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

Выпадающий список

1. Создадим приложение и книгу Excel

ПриложениеExcel = Новый COMОбъект("Excel.Application");
ПриложениеExcel.DisplayAlerts = 0;
ПриложениеExcel.Visible = 0;

КнигаExcel = ПриложениеExcel.Workbooks.Add();

2. В созданной книге у меня один лист (ПриложениеExcel.SheetsInNewWorkbook = 1, значение можно изменить), переименуем в List

Лист1 = КнигаExcel.Sheets(1);
Лист1.Name = "List";

3. Заполним первый лист значениями, среди них и будем осуществлять поиск и переход. Может так случиться, что на присвоении Value будет появляться ошибка, можно попробовать использовать Value2

Лист1.Cells(  3, 1).Value = "Клён";
Лист1.Cells(100, 1).Value = "Липа";
Лист1.Cells(200, 1).Value = "Ясень";
Лист1.Cells(300, 1).Value = "Береза";
// добьём для красоты
Для А = 4 По 299 Цикл
	Если А = 100 Или А = 200 Тогда
		Продолжить;
	КонецЕсли;		
	Лист1.Cells(А, 1).IndentLevel = 2;
	Лист1.Cells(А, 1).Value = А;
КонецЦикла;

4. Добавим второй лист, после Лист1 (за это отвечает указанный второй параметр метода — After), переименуем в Groups; будем использовать этот лист для хранения именованного списка; этот лист можно затем скрыть для удобства

Лист2 = КнигаExcel.Sheets.Add(, Лист1);
Лист2.Name = "Groups";

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

Лист2.Cells(1, 1).FormulaLocal = "=List!R3C1";
Лист2.Cells(2, 1).FormulaLocal = "=List!R100C1";
Лист2.Cells(3, 1).FormulaLocal = "=List!R200C1";
Лист2.Cells(4, 1).FormulaLocal = "=List!R300C1";

6. Присвоим имя Search нашей группе ячеек

КнигаExcel.Names.Add("Search", "=Groups!R1C1:R4C1");

7. Добавим к ячейке (через объект Range) проверку вводимых значений с типом данных Список (первый параметр XlDVType: xlValidateList = 3) и источником Search (4-й параметр)

Лист1.Range("A1").Validation.Add(3, , , "=Search", );

Выпадающий список создан. При желании по запросу «выпадающий список excel» можно найти статьи об интерактивном создании такого списка на тематических сайтах.

Макрос

Добавим макрос обработки выбора значения и поиска первого совпадающего с ним значения в первой колонке. После выбора значения из выпадающего списка, макрос переместит выделение к первой найденной ячейке с этим значением. Здесь может случиться казус в виде ошибки «Программный доступ к проекту Visual Basic не является доверенным». Необходимо включить флаг «Доверять доступ к объектной модели проектов VBA» (справедливо для Excel 2010):

Каталог решений - Выбор значения из выпадающего списка с поиском и переходом к ячейке с таким значением в Excel