programm-school.ru
Создаем расширенный список выбора в Excel с помощью VBA
Автор: Deys
6-8 минут
В статье "Создание списка выбора в Excel 2003" были рассмотрены способы создания списков выбора, но в этих способах есть небольшое неудобство при работе с большими списками, а именно поиск нужного значения. В этой статье будет описан способ создания списков в Excel с помощью средств языка VBA, более универсальный, с реализованной возможностью быстрого поиска.
Задача: Создать форму выбора значений из списка и их вставка в ячейку. Вызов формы осуществляется только на определенных столбцах, при этом на каждый столбец отображается свой набор значений. Форма вызывается правой кнопкой мыши(ПКМ).
Приступим к реализации. Для начала нам необходимо создать списки значений на отдельном листе. Назовем этот лист "Списки".
Начиная с первого столбца, первой строки, создадим списки следующим образом:
где, первая строка - наименование списка.
Лист, в котором необходимо заполнение и выбор значений, назовем реестр и выглядеть будет так:
т.е. нам необходимо с помощью выбора из списка подставлять значения в столбцы : B, C, D, F.
Открываем редактор VB (Alt+F11)
В редакторе зададим нашим листам в окне "Properties" следующие имена:
Листу с реестром зададим имя - reestr.
Лист со списками же, получит имя - spisok.
Далее, создадим форму с именем (name) "SelectList", добавим на нее элементы Listbox и TextBox.
Имена для этих элементов следующие:
Listbox - List
TextBox - SearchText
В итоге получится форма следующего вида:
Теперь необходимо прописать процедуры в код нашей формы. Для этого выделите форму и нажмите F7, после чего вставьте следующий код:
'глобальные переменные
Public R, C, count As Long
Public SheetOut As String
Public numList As Byte
'Вставка значения двойным кликом в общем листе
Private Sub List_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If List.Text <> "" Then
Sheets(SheetOut).Cells(R, C) = List.Text
SelectList.Hide
End If
End Sub
'Вставка значения нажатием Enter в листе Результата поиска
Private Sub List_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 13 Then
If List.Text <> "" Then
Sheets(SheetOut).Cells(R, C) = List.Text
SelectList.Hide
End If
End If
End Sub
Private Sub SearchText_Change() 'Процедура поиска и отображения результатов
count = 2
List.Clear
While Trim(spisok.Cells(count, numList)) <> ""
If InStr(1, LCase(spisok.Cells(count, numList)), LCase(SearchText.Text)) > 0 Then
List.AddItem spisok.Cells(count, numList)
End If
count = count + 1
Wend
End Sub
Public Sub LoadList() 'Процедура загрузки списка
count = 2
List.Clear 'очищаем список
SearchText.Text = ""
While Trim(spisok.Cells(count, numList)) <> ""
List.AddItem spisok.Cells(count, numList)
count = count + 1
Wend
SelectList.Caption = "Выберите - " & spisok.Cells(1, numList)
End Sub
Все. Элементы формы запрограммированы. Немного о процедурах по порядку:
- List_DblClick - процедура вызываемая двойным кликом мыши по выбранному в списке значению. При выполнении, происходит вставка значения в ячейку листа "Реестр" координаты которой переданы в глобальных переменных R(строка), C(столбец);
- List_KeyPress - процедура аналогична по действию с процедурой List_DblClick, только вставка осуществляется при нажатии клавиши Enter;
- SearchText_Change - процедура, вызываемая при вводе символов в строку поиска. В результате работы данной процедуры, происходит обновление списка, значения которого содержат введенные символы в поле SearchText. Ввести достаточно часть названия и будут отображены значения(значение) содержащие эту часть названия;
- LoadList - дополнительная процедура, которая загружает список в элемент List. Вызов производится в следующей части кода.
Для того, чтобы произвести вызов формы с нужным нам списком при нажатии правой кнопки мыши по ячейке листа Excel, необходимо в объект reestr (лист с реестром) добавить следующий код:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'вызов списка по щелчку ПКМ
Select Case Target.column
Case 2: loadform Target, 1, Cancel
Case 3: loadform Target, 2, Cancel
Case 4: loadform Target, 3, Cancel
Case 6: loadform Target, 4, Cancel
'....
End Select
End Sub
Private Sub loadform(ByVal Target As Range, numList As Byte, Cancel As Boolean)
Cancel = True 'отключает показ контекста после закрытия формы
With SelectList ' отправляем параметры форме, загружаем список и откр. форму
.R = Target.Row
.C = Target.column
.numList = numList
.LoadList
.SearchText.SetFocus
.SheetOut = reestr.Name
.Show
End With
End Sub
Процедура:
- loadform - загружает форму со списком, передает координаты ячейки, в которой произошел правый клик (глобальные переменные С и R) и вызывает процедуру LoadList для загрузки списка;
- Worksheet_BeforeRightClick - процедура, которая вызывается по событию нажатия ПКМ на листе. Собственно это наша ключевая процедура в которой происходит определение столбца, в котором необходимо использовать форму выбора из списка и задается номер загружаемого списка. Все это происходит в операторе Select Case.
Логика работы!
В Target.column возвращается номер столбца, в котором произошел клик ПКМ. Этот номер ищется в списке Select , если номер найден, то запускается процедура loadform с загрузкой соответствующего списка.
Например, Case 2: loadform Target, 1, Cancel, если кликнули ПКМ по второму столбцу (Case 2), то вызываем процедуру loadform, передаем ей координаты ячейки, которые находятся в Target, затем, указываем номер столбца списка (лист "Списки"), который необходимо загрузить.
Соответственно, для столбца в реестре:
2 загружаем список 1 (Месяц)
3 загружаем список 2 (Склад)
4 загружаем список 3 (НаимТовара)
6 загружаем список 4 (НаимКА)
На другие столбцы реакции не произойдет, будет вызвано контекстное меню. Этот список можно сократить (достаточно убрать Case 2, 3 ..) или добавить.
Снимок работающего списка
Вот и все. Если возникли вопросы, пишите.
П.С.: Данный вариант реализации списков отлично работает в Excel 2003-2007-2010
Прикрепленный файл: lists_vba.zip
