|
Выпадающий список с добавлением новых элементов |
  
|
| скрыть/показать скрытый текст |
|
Выпадающий список с добавлением новых элементов |
|
| скрыть/показать скрытый текст |
Итак, имеем следующую ситуацию:
Задача: сделать в ячейке 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.
|
|
 |
