Выбор значения из выпадающего списка с поиском и переходом к ячейке с таким значением в 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):

Панель «Разработчик» -> группа «Код» -> Безопасность макросов -> меню «Параметры макросов» -> поле «Параметры макросов для разработчика».

Здесь же выбрать «Отключить все макросы с уведомлением «(разрешать запуск с уведомлением «Включить содержимое») или «Включить все макросы». Другой вариант доступа, если вдруг у вас не включена закладка Разработчик:

Файл -> Параметры -> Центр управления безопсностью -> Параметры центра управления безопсностью… -> Параметры макросов.

Но не всегда эти настойки «держатся», надежнее это исправить в реестре:

HKEY_CURRENT_USERSOFTWAREMicrosoftOffice1x.0ExcelSecurity,

параметр AccessVBOM = 1, параметр VBAWarnings установите 1 (Включить все макросы) или 2 (Отключить все макросы с уведомлением).

Собственно, текст макроса вставляется так

КнигаExcel.VBProject.VBComponents("Лист1").CodeModule.InsertLines(1, ТекстМакроса);

Параметр в VBComponents должен быть именно таким Лист1. Вставляем строку в модуль, начиная с первой строки. В переменную ТекстМакроса следует поместить

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iRange As Range

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A1")) Is Nothing Then
If Not IsEmpty(Target) Then

Set iRange = Range("A2:A65536").Find(What:=Target)

If iRange Is Nothing Then
Exit Sub
Else
iRange.Select
End If

End If
End If

End Sub

Это обработчик события изменения листа. В качестве параметра выступает объект Range. A1 — ячейка (точнее объект Range), изменение в которой мы отслеживаем, в ней содержится наш выпадающий список. A2:A65536 — диапазон, где будет осуществляться поиск (65536 — пережиток Excel 97), если ячейки объединены, то надо указать весь диапазон. то есть, если ячейки А и B объединены, то диапазон будет A2:B65536. Комментировать весь код на VBA я не стану, но его праобраз можно найти на одном из тематических сайтов (где главная тема — Excel).

«Заморозим» первую строку на листе List, чтобы после выделения найденной ячейки, ячейка с выпадающим списком не уходила за пределы экрана

КнигаExcel.Sheets(1).Activate();
ПриложениеExcel.ActiveWindow.SplitRow = 1;
ПриложениеExcel.ActiveWindow.FreezePanes = Истина;

Т.к. «замораживание» возможно только у объекта Window (это набор некоторых элементов управления листа), надо активизировать первый лист List.

Теперь можно открыть книгу сделав приложение видимым, но только если код выполнялся на клиенте

ПриложениеExcel.Visible = 0;

Или записать книгу и передать на клиента/отправить по почте и т.д., если код выполнялся на сервере,

КнигаExcel.SaveAs(ИмяФайла, ФорматФайла);

Для книги с макросами параметр ФорматФайла должен быть равен 52 для Excel 2007-2024 (xlOpenXMLWorkbookMacroEnabled, with or without macro’s in 2007-2024, xlsm) или 56 для Excel 97 (xlExcel8, 97-2003 format in Excel 2007-2024, xls). После записи следует закрыть книгу и выйти из приложения

КнигаExcel.Close(Ложь);

ПриложениеExcel.DisplayAlerts = 1;
ПриложениеExcel.Quit();

Параметр у Close — записывать изменения книги или нет.

У этого метода есть один недостаток: выбрав значение из списка, мы модифицируем книгу. Но, например, элемент управления ComboBox также её модифицирует. Способа от этого избавиться я пока не нашёл.

Если что-то неясно, обработка приложена.