Ниже описан способ программно создать ячейку с выпадающим списком на листе книги 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" можно найти статьи об интерактивном создании такого списка на тематических сайтах.
Макрос
Книга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-2016 (xlOpenXMLWorkbookMacroEnabled, with or without macro's in 2007-2016, xlsm) или 56 для Excel 97 (xlExcel8, 97-2003 format in Excel 2007-2016, xls). После записи следует закрыть книгу и выйти из приложения
КнигаExcel.Close(Ложь);
ПриложениеExcel.DisplayAlerts = 1;
ПриложениеExcel.Quit();
