Задача: сделать в ячейке D2 выпадающий список,
чтобы пользователь мог выбирать имена из списка (столбец
А). Если нужного имени нет в списке, то пользователь
может ввести новое имя прямо в ячейку D2 - оно
автоматически добавится к столбцу А и начнет
отображаться в выпадающем списке в будущем.
Шаг 1. Создаем именованный диапазон
Сначала создадим
именованный диапазон, указывающий на заполненные
именами ячейки в столбце А - сколько бы имен в списке не
находилось. Для этого идем в меню Вставка - Имя -
Присвоить (Insert - Name - Define),
вводим имя диапазона (допустим
People) и в строку Ссылка
(Reference) вводим
следующую формулу:
=СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A$1:$A$24);1)
в английской версии Excel это
будет:
=OFFSET(Лист1!$A$1,0,0,COUNTA(Лист1!$A$1:$A$24),1)
Эта формула ссылается на все заполненные ячейки в
столбце А, начиная с А1 и вниз до конца - до последнего
имени.
Шаг 2. Создаем выпадающий список в ячейке
Выделяем ячейку D2 и открываем меню Данные -
Проверка (Data - Validation).
Далее выбираем из выпадающего списка Тип (Allow)
позицию Список (List) и
вводим в строку Источник
(Source) ссылку на созданный на шаге 1
именованный диапазон:
Чтобы Excel позволил нам в будущем ввести в список и
новые имена, снимем галочки на вкладках Сообщение для
ввода (Input Message) и
Сообщение об ошибке (Error
Alert) и нажмем ОК. Теперь у нас есть
выпадающий список в ячейке D2. Причем, если, например,
вручную дописать новое имя в столбце А, то оно
автоматически появится в выпадающем списке в ячейке D2,
поскольку имена берутся из динамического диапазона
People, который автоматически отслеживает изменения в
столбце А.
Шаг 3. Добавляем простой макрос
Щелкаем правой кнопкой мыши по ярлычку нашего листа и
выбираем Исходный текст (View
Source). Откроется редактор Visual Basic,
куда надо скопировать такой код:
|
Private Sub Worksheet_Change(ByVal Target As
Range) Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$D$2" Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Range("People"),
Target) = 0 Then
lReply = MsgBox("Добавить введенное имя "
& _
Target & " в выпадающий
список?", vbYesNo + vbQuestion)
If lReply = vbYes Then
Range("People").Cells(Range("People").Rows.Count +
1, 1) = Target
End If
End If
End If
End Sub |
Если Ваш выпадающий список находится не в ячейке D2
или Вы назвали диапазон с именами не People, а
как-то еще, то подправьте эти параметры в макросе на
свои.
Всё! Теперь при попытке ввести новое имя в ячейку D2
Excel будет спрашивать
... и при утвердительном ответе пользователя
автоматически добавлять новое имя к списку в столбце А и
в выпадающий список в ячейку D2.
Источник: www.planetaexcel.ru
