Выпадающий список с добавлением новых элементов
Категория: Выпадающие списки, просмотров: 45422, опубликовано: 27.05.2007 Скачать пример |
Итак, имеем следующую ситуацию:
Задача: сделать в ячейке 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.
Ссылки по теме
- Как создать простой выпадающий список в ячейке листа
- Как создать список из которого будут автоматически удаляться использованные элементы
- Автоматическое создание списка при помощи надстройки PLEX
- Связанные выпадающие списки (от того, что выбрано в первом - зависит содержимое второго)
- Выпадающий список для выбора изображений (фотографий товаров, сотрудников и т.д.)
Комментарии:
| Валерий 29.07.2007 |
Очень все просто, доходчиво, красиво оформлено!!! И очень полезно! Спасибо за сайт!! (начинающий...) если можно подскажите: необходимо строку с данными в ячейках скопировать и вставить на др. лист2. Далее, через опред. время я изменил на первоначальном листе данные - необходимо снова скопировать строку с измененными данными и вставить на лист2, но уже на строку ниже, чтобы не затирать первоначальную строку и т.д.... Можно с помощью кнопки и макроса.... |
| Александр 07.09.2007 |
Сортировать бы еще список по алфавиту после каждого добавления.. |
| Алексей 24.09.2007 |
Здравствуйте! Очень полезный ресурс вы создали. Очень много интересного и нового. Ну вот и у меня возник вопрос, причем достаточно срочный, спасайте: в данном примере (описаном выше) в качестве ячейки для выбора/ввода нового значения - указана одна ячейка. Как сделать так, чтобы при вводе данных в диапазон, к примеру, D2:D10, в любую из этих ячеек, происходила проверка и добавление в список. Прописать в стоорке If Target/Adress= и так далее 10 раз, можно через or, но мне нужен диапазон в 4000 строк :-). Помогите пожалуйста!!! . |
| Алексей 25.09.2007 |
Это опять я. Спасайте!!! Пожалуйста!!!. |
| Евгений 30.09.2007 |
Нет слов от восторга и восхищения, красиво !!! Сколько возможностей открылось , супер, Если можно, подскажите, пожалуйста, в объединенную ячейку вставляется по формуле текст из другой ячейки, как сделать , чтобы конечная ячейка "растягивалась" и "сужалась" по высоте в зависимости от длины текста. |
| Максим 03.10.2007 |
Очень нужная штуковина, спасибо! Подскажите всетаки как задать диапазон а не одну ячейку для этого макроса, т.е. то о чем говорит Алексей =). |
| Pavel 03.10.2007 |
а так же скажите, как удалять не нужные имена из списка. |
| Роман 10.10.2007 |
Офигеть!!! Даже я всё понял. Круто!!!. |
| tanka 09.11.2007 |
Как сделать так, чтобы при вводе данных в диапазон, в любую из ячеек, происходила проверка и добавление в список? Заменить строку: If Target.Address = "$D$2" Then на "If Target.Column = 4 Then", где 4 - порядковый номер столбца, D в данном случае. Ну и, ес-но, выпадающий список (шаг 2) создаем не только в ячейке D2, а во всем столбце D. |
| Cumbal 09.11.2007 |
2tanka спасибо большое!. |
| Наталья 15.11.2007 |
Супер сайт. В этом примере небольшая ошибка, в английской версии формулы стоят запятые, а нужны точки с запятой. Иначе эксель ругается:). |
| Barracuda 10.12.2007 |
C одним выпадающим списком все понятно. А как сделать 2 разных списка на одном листе???? Может кто подскажет? Заранее спасибо!. |
| сан 11.12.2007 |
Спасибо за помощь,я ищу возможность делать при помощи табл Excel можно делать сметы калькуляции. |
| Владимир 24.01.2008 |
Подскажите пожалуста, а как сделать то же самое только наоборот? Поясняю. То есть, чтобы в ячейке D2 была проверка на предмет несовпадения с People, и все несовпадающие значения добавлялись в People. Другими словами, нужно разрешить вносить в D2 только те значения, которых нет в People, при этом эти значения добавляются в People. Заранее спасибо. |
| Максим 06.02.2008 |
У меня почему-то все новые добавляемые значения появляются только в одной ячейке (на условиях примера в A11)и затирают предыдущее добавленное значение. Список не расширяется более чем на одно значение. Помогите пожалуйста!. |
| Сергей 13.02.2008 |
Поскажите пожалуйста в чем дело: скопированный макрос совершенно не производит никаких действий. Может быть это потому, что именованный диапозон находится на другом листе? Если так, то какие изменения нужно внести в запись макроса? У меня диапозон и я внес изменения рекомендованные Tanka (выподающий список в столбце). |
| masha 05.03.2008 |
а как быть, если добавить новое имя нужно в алфавитном порядке, пыталась написать макрос-не вышло. |
| таня 08.03.2008 |
Задача: Список находится на листе1. Выпалающий список нужно создать на листе2. И он должен находится например. в столбце "D" с 2-й по 10-ю стоку. Подскажите как это можно сделать? С созданием "Выпадающего списка с добавлением новых элементов" на одном листе и в ячейке "D2" как в примере все понятно. . |
| JR 25.06.2008 |
Как сделать что бы в выпадающем списке были гиперссылки из именованного диапозона. |
| вопрошающий 02.07.2008 |
Задача: Список находится на листе1. Выпалающий список нужно создать на листе2. И он должен находится например. в столбце "D" с 2-й по 10-ю стоку. Подскажите как это можно сделать? С созданием "Выпадающего списка с добавлением новых элементов" на одном листе и в ячейке "D2" как в примере все понятно. . |
| костян 24.08.2008 |
На один лист ввел два макроса: Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$G$5" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("нумерация_заказов"), Target) = 0 Then lReply = MsgBox("Внести Заявку №" & Target & " в архив ", vbYesNo + vbQuestion) If lReply = vbYes Then Range("нумерация_заказов").Cells(Range("нумерация_заказов").Rows.Count + 1, 1) = Target End If End If End If End Sub изменил ссылки на ячейку и диапазон: Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$O$7" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("клиентская_база"), Target) = 0 Then lReply = MsgBox("Добавить клиента " & Target & " в базу?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("клиентская_база").Cells(Range("клиентская_база").Rows.Count + 1, 1) = Target End If End If End If End Sub получаю ошибку(возврат к макросам с выделением: "Private Sub Worksheet_Change(ByVal Target As Range)" Как это понимать и что с этим делать? Направте на путь истинный. Очень надо!!! . |
| Дмитрий 02.09.2008 |
В этом коде ответы практически на все вопросы заданные здесь. Нужно только заменить пораметры на те которые вам нужны. Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = Target.Address Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Лист3").Range("Мастер"), Target) = 0 Then lReply = MsgBox("Добавить Мастера " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("Лист3").Range("Мастер").Cells(Sheets("Лист3").Range("Мастер").Rows.Count + 1, 1) = Target End If End If End If End Sub. |
| Сергей 16.09.2008 |
Так как все-таки отсортировать новый список по алфавиту после добавления нового элемента?. |
| Николай Павлов 25.10.2008 |
Сергей: только с помощью макроса. |
| Дмитрий 05.11.2008 |
В первом шаге: "начиная с А1 и вниз до конца - до последнего имени". Как же до конца, если в ссылку вводится СЧЁТЗ(Лист1!$A$1:$A$24) т.е. список может увеличиться не боле чем до 24 элементов. Поэтому и получается, что как уже здесь и писали после 24-х элементов списка все значения будут записываться в A25 и не будут отображаться в выпадающем списке. Как поменять код чтобы просматривались изменения во всём столбце?. |
| Gambo 25.02.2009 |
а у меня ничего не получилось((. |
| Maks 30.07.2009 |
Чтобы на весь столбец, вместо СЧЁТЗ(Лист1!$A$1:$A$24) написать СЧЁТЗ(Лист1!$A:$A). |
| karmasoer 07.08.2009 |
Здравствуйте. У меня при запуске макроса появляется ошибка run-time '1004' и выделяется строка If WorksheetFunction.CountIf(Range("департаменты"), Target) = 0 Then Что не так?. |
| Андрей 07.08.2009 |
Классная штука!!! А кто мне сможет помочь, мне надо чтоб список лиц находился в другом файле... как это оформить?. |
| salamon1 08.09.2009 |
ошибка run-time '1004' . |
| salamon1 08.09.2009 |
Как сделать 2 разных списка на одном листе чтобы работало добавление и в первом и во втором ????. |
| salamon1 08.09.2009 |
Я так понял ответа на вопрос о двух списках можно не ждать... Очень нуна.... |
| salamon1 08.09.2009 |
karmasoer писал: Здравствуйте. У меня при запуске макроса появляется ошибка run-time '1004' и выделяется строка If WorksheetFunction.CountIf(Range("департаменты"), Target) = 0 Then Что не так?. скорее всего ячейка с выпадающим списком находится на листе1, а сам диапазон из которого берётся список на листе2 нуна использовать приведённый выше код: Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = Target.Address Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Лист3").Range("Мастер"), Target) = 0 Then lReply = MsgBox("Добавить Мастера " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("Лист3").Range("Мастер").Cells(Sheets("Лист3").Range("Мастер").Rows.Count + 1, 1) = Target End If End If End If End Sub. |
| muravey69 27.10.2009 |
Привет всем. У меня вообще не появляется никакого окошечка с вопросом. Есть две страницы (Лист1 и Лист2). На первой странице выпадающий список на весь столбец, а на второй сам список. Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Column = 2 Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Лист2").Range("Списки"), Target) = 0 Then lReply = MsgBox("Добавить введенное наименование работ " & _ Target & " в общий список работ?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("Лист2").Range("Списки").Cells(Sheets("Лист2").Range("Списки").Rows.Count + 1, 1) = Target End If End If End If End Sub Что делаю не так?. |
| muravey69 28.10.2009 |
Разобрался - неточность первого поста, ошибку исправил - работает. Только вот как сортировать по алфавиту автоматически?. |
| Андрей 18.11.2009 |
Добавьте в конце макроса следующий код. При вводе нового имени в ячейку D2, будет отсортировано по алфавиту содержимое в столбце A и содержимое выпадающего списка. ActiveWorkbook.Worksheets("Лист1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Лист1").Sort.SortFields.Add Key:=Range("People"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Лист1").Sort .SetRange Range("People") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With. |
| Igor 29.11.2009 |
ребят, подскажите где в ексель2007 нужно вставлять формулу =OFFSET(Лист1!$A$1,0,0,COUNTA(Лист1!$A$1:$A$24),1)? чет никак немогу найти . |
| Шульц 12.12.2009 |
Андрей - касательно сортировки: Заменил "Лист1" и "People" на свои, но не работает, выдает ошибку: Compile error: Only comments may appear after End Sub, End Function, or End Property. Куда именно в конец добавлять? После End Sub, или вклинивать куда-то? Пробовал перед End Sub, тоже самое выдает ошибку в строке - ActiveWorkbook.Worksheets("Лист1").Sort.SortFields.Clear и выделяет желтым ("Лист1") А без сортировки по предыдущему примеру все ок.. |
| Влад 21.12.2009 |
Андрей, можно ли отсортировать содержимое выпадающего списка без сортировки основного диапазона в столбце А. |
| Сергей 18.01.2010 |
Шульц Код надо вставить в процедуру Private Sub Worksheet_Change(ByVal Target As Range) того листа, где находится таблица с сортируемыми элементами. Тогда всё работает.. |
| Anri 21.01.2010 |
Если нужно, чтобы проверка и добавление происходили по нескольким столбцам в таблице то: If Target.Column = "3" Or "4" Or "5" Or "6" Or "7" Or "8" Or "11" Or "12" Or "18" Then вместо: If Target.Address = Target.Address Then . |
| Алексей 22.01.2010 |
Большое человеческое спасибо за описание. первой же ссылкой в поисовике перешел на эту страницу и сразу получил ответ. благодарю.. |
| krasser 25.01.2010 |
Если вставить эти строки кода перед последней строкой(т.е. перед End Sub),то диапазон и список будут отсортированы по возрастанию. Range("People").Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlNo . |
| Дмитрий 28.01.2010 |
Здравствуйте! Где-то видел решение добавления в список (через проверку) двух диапазонов ячеек, теперь єто очень нужно, не не могу вспомнить где и как. Как их задать в "Источник:"?!. |
| Кирилл 09.02.2010 |
Добрый день! Спасибо за статью! Подскажите, возможно ли сделать так, чтобы при ответе "Нет", т.е. "не добавлять введённое значение в список", выдавало бы ошибку? В изначальном варианте, даже если ответ "Нет". то введённое значение, которого нет в списке, остаётся. Что по моим условиям недопустимо. Заранее спасибо!. |
| Сергей 10.02.2010 |
Для Кирилла: 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 'сортировка по возрастанию Range("People").Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlNo Else Target = "" End If End If End If End Sub Есть рекурсия,но в нашем случае это не смертельно:). |
| Кирилл 10.02.2010 |
Сергей, большое спасибо!!! Теперь всё работает именно так, как мне хотелось бы! Почитав комменты к статье и определив свои требования к данной возможности, постарался свести всё в единое целое и теперь выношу на ваш суд :) Для меня важно было реализовать это: а) для нескольких списков, б) для нескольких ячеек/диапазонов, в) с автоматической сортировкой, г) введённое по ошибке значение, которое мы не хотим добавлять в список, автоматически стирается из ячейки Итак: 1. Создано два листа. На листе 1 ("Списки") находятся непосредственно 2 списка "Name" ($A$5:$A) и "Surname" ($B$5:$B). На листе 2 ("Выпадающие") в двух столбцах сделаны ячейки с выпадающими списками. Столбец А (начиная с А5) - выпадающие списки "Name", столбец B (начиная с B5) - выпадающие списки "Surname" 2. В качестве диапазона для списка "Name" задана формула =СМЕЩ(Списки!$A$5;0;0;СЧЁТЗ(Списки!$A:$A);1) В качестве диапазона для "Surname" задана формула =СМЕЩ(Списки!$B$5;0;0;СЧЁТЗ(Списки!$B:$B);1) 3. Код для выпадающих списков на листе "Выпадающие" написан так, что при вводе в ячейку несуществующего значения в списке: 1) Всплывает окно "Добавить введённое имя ... в выпадающий список?" 1.1.) При ответе "Да" введённое значение добавляется в список, список сортируется по алфавиту. 1.2.) При ответе "Нет" всплывает окно "Ошибка ввода". После нажатия "ОК" введённое значение стирается. Код: Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Column = 1 And Target.Row > 4 Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Списки").Range("Name"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("Списки").Range("Name").Cells(Sheets("Списки").Range("Name").Rows.Count + 0, 1) = Target Sheets("Списки").Range("Name").Sort Key1:=Sheets("Списки").Range("A5"), _ Order1:=xlAscending, Header:=xlNo End If If lReply = vbNo Then lReply = MsgBox("Ошибка ввода.", vbOKOnly) If lReply = vbOK Then Target = "" End If End If End If End If If Target.Column = 2 And Target.Row > 4 Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Списки").Range("Surname"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("Списки").Range("Surname").Cells(Sheets("Списки").Range("Surname").Rows.Count + 0, 1) = Target Sheets("Списки").Range("Surname").Sort Key1:=Sheets("Списки").Range("B5"), _ Order1:=xlAscending, Header:=xlNo End If If lReply = vbNo Then lReply = MsgBox("Ошибка ввода.", vbOKOnly) If lReply = vbOK Then Target = "" End If End If End If End If End Sub Если есть возможность упростить этот код, сохраняя все функции, то подскажите. Буду признателен =) P.S.: Сергей, что такое "рекурсия"? :). |
| Сергей 10.02.2010 |
Для Кирилла: Для меня не совсем понятна целесообразность вывода сообщения об ошибке,но если для Вас это важно,то вот эту часть кода я немного упростил: If lReply = vbYes Then Sheets("Списки").Range("Name").Cells(Sheets("Списки") _ .Range("Name").Rows.Count + 0, 1) = Target Sheets("Списки").Range("Name").Sort Key1:=Sheets("Списки") _ .Range("A5"), Order1:=xlAscending, Header:=xlNo Else MsgBox "Ошибка ввода", vbInformation, "Внимание" Target = "" End If P.S. Рекурсия - это когда метод или как в нашем случае событие, вызывают сами себя в процессе работы.Так ,например, строка кода Target = "" приводит к повторному срабатыванию события Worksheet_Change . Зайдите в редактор VBE и через F8 Вы сможете проследить построчно выполнение кода данной процедуры. Извините за Офф топ.. |
| Максим 20.02.2010 |
Добрый день, я хотел бы узнать возможно ли связать несколько таких редактируемых раскрывающихся списка? Например необходимо ввести данные о клиенте: ФИО, адрес, телефон, но так, чтобы их можно было вытащить из базы, вызвав только ФИО, а остальная информация самостоятельно вставлялась бы в остальные ячейки, но при этом чтобы можно было вводить данные о новых клиентах? Заранее благодарю!. |
| Yulia 25.02.2010 |
Добрый день! просьба помочь в след вопросе - хочу добавить сортировку по алфавиту, но выдает ошибку Run time error '438' Данные находятся на 2ух закладках: Data (список городов) и Details (выпадающий список) вот сам макрос: Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = Target.Address Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Data").Range("City"), Target) = 0 Then lReply = MsgBox("Добавить город " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("Data").Range("City").Cells(Sheets("Data").Range("City").Rows.Count + 1, 1) = Target ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("City"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Data").Sort .SetRange Range("City") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End If End If End If End Sub макрос прописан на закладке Details, где располагается выпадающий список. в чём может быть ошибка? может надо вставить макрос по сортировке в др. место? оч надеюсь на Вашу помошь!. |
| Сергей 25.02.2010 |
Yulia ,см. ответ krasser . |
| Yulia 26.02.2010 |
Сергей, то о чём пишет krasser работает только если данные и выпадающий список находятся на одном листе, а у меня данные находятся на разных. Поэтому пытаюсь использовать код, описанный Андреем 18.11.09 ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("City"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Data").Sort .SetRange Range("City") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With где-то в этом коде и закралась ошибка.. |
| Сергей 26.02.2010 |
Yulia,адаптируйте следующий код под свои потредности: "Лист1" - это там ,где именнованый диапазон. "Лист2" - это там ,где выпадающий список,поэтому и код вставляете сюда 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(Worksheets("Лист1").Range("People"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then With Worksheets("Лист1") .Cells(Worksheets("Лист1").Range("People").Rows.Count + 1, 1) = Target 'сортировка по возрастанию .Range("People").Sort Key1:=.Range("A1"), _ Order1:=xlAscending, Header:=xlNo End With Else Target = "" End If End If End If End Sub . |
| Денис 26.02.2010 |
Добрый день. Есть строчка If Target.Address = "$D$2" Then А как добавить еще ячейки? Мне к примеру нужны еще H2 и т.п. Заранее спс за ответ.. |
| krasser 27.02.2010 |
If Target.Address = "$D$2" Or Target.Address = "$H$2" Then. |
| Yulia 27.02.2010 |
Сергей, при прописывании след. кода .Range("City").Sort Key1:=.Range("A1"), _ Order1:=xlAscending, Header:=xlNo End With появляется ошибка- Compile error:Invalid or unqualified reference. и ругается он на :=.Range("A1"). макрос вообще перестает работать. Мне же необходимо чтобы и выпадающий список и данные сортировались, а в этом коде есть ссылка только на выпадающий список.. |
| Demon 23.04.2010 |
Молодцы, что сделали такой хороший сайт, Респект!!! . |
| Demon 23.04.2010 |
Все просто и понятно. Только версии разные и паришься в поиске нужной опции. |
| Марина 27.04.2010 |
Огромное человеческое спасибо. |
| giawww 30.05.2010 |
Подскажите,пожалуйста,как в выппадающем списке(если он очень длиный)сделать так,чтобы при наборе первой буквы список перемещался в сегмент с этой заглавной буквы Email: giawww@yahoo.com. Заранее Спасибо ....очень надо!!!!!!!!!!!!!!!!!!!!.. |
| Мария 19.06.2010 |
Сайт превосходный!!! Огромный респект автору! Очень помогает этот сайт в работе. спасибо БОЛЬШОЕ!!!!!. |
| Beleckiy 12.07.2010 |
Спасибо всем гуру за готовые решения! Мне это очень помогло! Но меня очень интересует ответ на вопрос, который задал Максим: >Добрый день, я хотел бы узнать возможно ли связать несколько таких >редактируемых раскрывающихся списка? Например необходимо ввести >данные о клиенте: ФИО, адрес, телефон, но так, чтобы их можно было >вытащить из базы, вызвав только ФИО, а остальная информация >самостоятельно вставлялась бы в остальные ячейки, но при этом чтобы >можно было вводить данные о новых клиентах? Это было бы супер решение! Просто готовая клиентская база... Помогите плииииззз. Заранееблагодарен!. |
| Иван 17.07.2010 |
Здравствуйте. помогите пожалуйста( как ввести два одинаковых макроса в одном листе? повтор вопроса костян 24.08.2008 Спасибо. |
| Игнат 22.07.2010 |
Здравствуйте! я создал список выбора в каждой ячейке одного столбца. поскольку выбор магазинов довольно большой и занимает много времени поиска даже по алфавиту, появился вопрос - как можно в ячейке со списком начать вводить название, чтобы excel автоматически давал варианты заполнения ячейки? буду очччень благодарен.. |
| Павел 10.08.2010 |
Люди добрые помогите!!!!! Все бы хорошо, ноя в соем Excel 2007 не могу найти путь (Для этого идем в меню Вставка - Имя - Присвоить (Insert - Name - Define), вводим имя диапазона (допустим People) и в строку Ссылка (Reference) вводим следующую формулу: =СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A$1:$A$24);1) Если кто справился с этим, помогите плиз))))) . |
| Дмитрий 13.08.2010 |
Максим, и Beleckiy, задали вопросы. меня тоже интересует такое решение К кому можно обратиться по данному вопросу? готов оплатить работу Yandex-деньгами цитата: Добрый день, я хотел бы узнать возможно ли связать несколько таких редактируемых раскрывающихся списка? Например необходимо ввести данные о клиенте: ФИО, адрес, телефон, но так, чтобы их можно было вытащить из базы, вызвав только ФИО, а остальная информация самостоятельно вставлялась бы в остальные ячейки?. |
| Юрий 15.08.2010 |
Здравствуйте все, кто интересуется EXCEL!!! У меня, как и у Игната возникла такая же проблемка.... Можно ли сделать так, что бы когда начинаешь вводить слово в выпадающий список что бы в это выпадающем списке показывало подходящие варианты (так, что бы не искать мне его полностью, а только по начальному вводу). Ну а если во время введения названия совпадения не окажется, то как уже выше писалось.. добавлять его в общий список. Что бы потом, можно было это название найти уже в выпадающем списке при повторном вводе начальных букв. Очень благодарен буду возможности примера или описания (подробнее), как это сделать! . |
| Павел 17.08.2010 |
Дмитрий 13.08.2010 Максим, и Beleckiy, задали вопросы. меня тоже интересует такое решение К кому можно обратиться по данному вопросу? готов оплатить работу Yandex-деньгами цитата: Добрый день, я хотел бы узнать возможно ли связать несколько таких редактируемых раскрывающихся списка? Например необходимо ввести данные о клиенте: ФИО, адрес, телефон, но так, чтобы их можно было вытащить из базы, вызвав только ФИО, а остальная информация самостоятельно вставлялась бы в остальные ячейки?. А в чем проблема собственно... самый примитивный макрос написанный ручками на вба.. Пиши мне на мыло) если интересует то договоримся . |
| Олег 19.08.2010 |
прочитал весь форум, но так и не понял, как сделать выпадающий список не в ячейке "D2", а в ячейках "D2:D4000" объясните кто-нибудь. |
| Remet 19.08.2010 |
Коллеги, подскажите пожалуйста следующее: я сделал как у Кирилла (10.02.2010), но мне нужно добавить еще и ячейку "С5" и "D5", вставляю этот код, выводит ОШИБКУ 1040!! Что здесь не так!? If Target.Column = 3 And Target.Row > 4 Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("Списки").Range("Hotel"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Sheets("Списки").Range("Hotel").Cells(Sheets("Списки").Range("Hotel").Rows.Count + 0, 1) = Target Sheets("Списки").Range("Hotel").Sort Key1:=Sheets("Списки").Range("C5"), _ Order1:=xlAscending, Header:=xlNo End If If lReply = vbNo Then lReply = MsgBox("Ошибка ввода.", vbOKOnly) If lReply = vbOK Then Target = "" End If End If End If End If . |
| Сергей 27.08.2010 |
Для Павла: Здесь простым макросом не отделаешься,ведь речь идет не только об извлечении данных из базы , но и о добавлении в базу.Нужна форма. Можно,конечно и вручную заполнять лист с данными ,но как быть с сортировкой.Да и проверка на корректность ввода не помешает. Для Олега: Зачем тебе столько?. |