Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel поле со списком

Добавление списка или поля со списком на лист в Excel

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Если вам нужно отобразить список значений, которые сможет выбирать пользователь, добавьте на лист список.

Добавление списка на лист

Создайте перечень элементов, которые должны отображаться в списке, как показано на рисунке.

На вкладке Разработчик нажмите кнопку Вставить.

Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.

В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).

Щелкните ячейку, в которой нужно создать список.

Нажмите кнопку Свойства и на вкладке Элемент управления задайте необходимые свойства:

В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.

Примечание: Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.

В поле Связь с ячейкой введите ссылку на ячейку.

Совет: Выбираемая ячейка содержит число, связанное с элементом, выбранным в списке. Его можно использовать в формуле для получения фактического элемента из входного диапазона.

В группе Возможен выбор установите переключатель одинарного значения и нажмите кнопку ОК.

Примечание: Если вы хотите выбрать параметр набора значений или списка значений, подумайте о том, чтобы использовать элемент ActiveX «Список».

Добавление поля со списком на лист

Упростите ввод данных для пользователей, позволив им выбирать значение из поля со списком. Поле со списком состоит из текстового поля и списка, которые вместе образуют раскрывающийся список.

Можно добавить поле со списком одного из двух типов: элемент управления формы или элемент ActiveX. Если необходимо создать поле со списком, в котором пользователь сможет изменять текст в текстовом поле, рассмотрите возможность использования элемента ActiveX «Поле со списком». Элемент ActiveX «Поле со списком» более универсален: вы можете изменить свойства шрифта, чтобы текст было легче читать на листе с измененным масштабом. Кроме того, такое поле со списком можно программно разместить в ячейках, содержащих список проверки данных.

Выберите столбец, который можно скрыть на листе, и создайте список, введя по одному значению в ячейки.

Примечание: Можно также создать список на другом листе той же книги.

На вкладке Разработчик нажмите кнопку Вставить.

Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.

Выберите тип поля со списком, которое нужно добавить:

в разделе Элементы управления формы выберите элемент управления Поле со списком (элемент управления формы);

в разделе Элементы ActiveX выберите элемент управления Поле со списком (элемент ActiveX).

Щелкните ячейку, в которую нужно добавить поле со списком, и нарисуйте его с помощью перетаскивания.

Чтобы изменить размер поля, наведите указатель мыши на один из маркеров изменения размера и перетащите границу элемента управления до достижения нужной высоты и ширины.

Чтобы переместить поле со списком на листе, выделите его и перетащите в нужное место.

Форматирование элемента управления формы «Поле со списком»

Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.

Откройте вкладку Элемент управления и настройте следующие параметры.

Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.

Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.

Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.

Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст «Фруктовое мороженое».

Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.

Нажмите кнопку ОК.

Форматирование элемента ActiveX «Поле со списком»

На вкладке Разработчик нажмите кнопку Режим конструктора.

Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.

Вот как можно настроить свойства поля со списком на этом рисунке:

Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.

Тип, начертание или размер шрифта

Щелкните свойство Font (Шрифт), нажмите кнопку . и выберите тип, размер или начертание шрифта.

Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.

Связь с ячейкой для отображения значения, выбранного в списке

Щелкните свойство LinkedCell (Связанная ячейка).

Читать еще:  Посчитать кол во строк excel

Связывание поля со списком и списка элементов

Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.

Изменение количества отображаемых элементов списка

Щелкните поле ListRows и введите число элементов.

Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.

Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Добавление выпадающего списка к ячейке

Всем, привет!
Ситуация: программно создается несколько книг. Необходимо что бы в ячйках A1, A2,A3 созданных книг появлялся выпадающий список известных значений(значения текстовые).

Помогите с реализацией!

31.08.2012, 10:25

Скопировать строку Excel, за текущей строкой если из выпадающего списка выбрать второе значение в ячейке
Добрый день, никак не получается совместить макрос который я пытаюсь совместить Есть макрос.

Создание выпадающего списка
Нужно создать выпадающий список в ячейке средствами VBA, который ссылался бы не на диапазон ячеек.

Изменение выпадающего списка
Подскажите пожалуйста, второй день пытаюсь изменить выпадающий список созданный обычной функцией в.

Наполнение выпадающего списка ComboBox
Здравствуйте. Подскажите, как можно наполнить ComboBox, который находится на одном листе (может .

31.08.2012, 10:352
Visual Basic
31.08.2012, 11:30 [ТС]3
31.08.2012, 11:394
31.08.2012, 16:225
31.08.2012, 17:31 [ТС]6
31.08.2012, 17:457
Ексель дает возможность задать возможные значения ячейки. Дальше либо выбрать из списка либо ввести вручную, но только из списка!
Красиво разжевано на

Комментарий модератора
planetaexcel — ссылка удалена, читайте правила!

Макрос собственно задал список, можно указать диапазон. Тогда значения берутся из области на листе.
Заполни чемто диапазон C3:C10 и запусти макрос. Потом кликни на А1, справа появится триугольник комбабокса. Попробуй вручную ввести неверное значение.

Visual Basic
31.08.2012, 17:548
31.08.2012, 18:049

Эта конструкция задает параметры функции проверки значений, пишется одной строчкой: Cells(1, 1).Val .

Просто принято делить. Использовать With нужно когда еще чтото делается над тем же объектом
Нижнее подчеркивание — знак переноса для VBA.
Нуже два примера работают одинаково, но читать удобнее первый
Делает она то что на скрине.

Visual Basic
Visual Basic
31.08.2012, 18:14

Казанский, Не стоит сразу посылать маны курить. Все когда-то начинали. Порой пара толковых объяснений дает больше толку и хороший пинок что разобраться дальше нежели прочитанный талмуд.
До прошлой недели сам использовал элементы управления (третий способ в ссылке на «планету»), у коллег машина ругается на наличие макроса со всеми вытекающими.

Меню пользователя @ Formanter
31.08.2012, 18:31

Formanter,
а у Вас вся спина белая в посте недопустимая ссылка, см. https://www.cyberforum.ru/announcement.php?a=3 , п.5.8. Щас модеры придут.

Меню пользователя @ Казанский
01.09.2012, 01:4612
01.09.2012, 12:39

Formanter, оч. хочется посмотреть на Ваше отношение к «отсыланию в маны» постов этак через 2000. да даже хотя бы через 500

Меню пользователя @ ikki
Читать блог
01.09.2012, 19:4414
26.06.2013, 11:3715

У меня такая проблема. Хочу в выпадающий список в ячейке вставить массив данных.
Пробовала в формулу писать имя массива, не выходит..

Конечно есть вариант написать массив на скрытый лист, потом сослаться в формуле на диапозон.
Но хочется без лишнего веса обойтись, компы тормозные. Реально сделать.

26.06.2013, 12:2016

Поступил бы так:

Visual Basic
26.06.2013, 13:0017
26.06.2013, 13:4418
27.06.2013, 15:5819

Ребят, а может мне кто-нибудь подскажет вариант реализации? Задача у меня такая: есть файл с адресами: Город, Улица, Дом — это столбцы. Файл для каждого региона свой, и он динамичный лежит в общем справочнике. Мы работаем с другим файлом, в который вставляется нужный листик, в зависимости от того, какой регион его открывает. Это я написала. Так вот. теперь надо, чтобы на другом листочке в столбцах Город/улица/дом, выходили выпадающие списки, причём для определённого города, только его улицы, а для улиц дома. При этом не должно быть пустых и улицы уникальны.

Я придумала два варианта реализации. Но они оба не отличаются особым успехом.

Первый: почти без VBA

с помощью VBA я создаю уникальный список городов и делаю его именем и прописываю кодом, что значение в столбце должно быть список с именем «Город»

далее, так как у меня есть список Город-Улица (на адресном листе), с помощью формулы смещения и поиска позиции нахожу улицы только этого города.(минус этого такой, что эта формула в именах постоянно почему-то (!) сбивается. )

Далее аналогично, этой же формулой нахожу дома для улиц.

Второй способ — не осуществила до конца, так как ступор. Создала уникальные города на отдельном листе в строку. Далее создала под каждым городом его улицы — так же уникальные. А вот теперь надо через ДВССЫЛ через VBA создать столько имён, сколько у меня вышло городов. вообщем как это сделать. если это реально.

Если есть другой вариант решения очень жду!!

Заранее спасибо

VBA Excel. Элемент управления ComboBox (поле со списком)

Элемент управления пользовательской формы ComboBox для выбора и ввода информации в VBA Excel. Свойства поля с раскрывающимся списком, заполнение, извлечение данных, примеры кода.

Элемент управления ComboBox

UserForm.ComboBox – это элемент управления пользовательской формы, предназначенный для передачи в код VBA информации, выбранной пользователем из раскрывающегося списка или введенной с клавиатуры.

ComboBox представляет из себя комбинацию двух элементов управления: текстового поля (TextBox) и списка (ListBox), поэтому его еще называют «комбинированным списком» или «полем со списком». Также ComboBox сочетает в себе свойства этих двух элементов управления.

Изначально комбинированный список прорисовывается на форме в виде текстового поля с кнопкой для отображения раскрывающегося списка. Далее по тексту будем использовать слово «поле» в значении текстового поля в составе элемента управления ComboBox, а словосочетание «раскрывающийся список» – в значении списка в составе элемента управления ComboBox.

Поле со списком используется в тех случаях, когда необходимо добавить в форму информацию, которая заранее известна, а ее отдельные позиции можно сгруппировать в список, а также для ручного ввода с клавиатуры или вставки из буфера обмена, если необходимое значение в списке отсутствует.

Элемент управления ComboBox незаменим при больших списках. При списках из нескольких позиций его можно заменить на ListBox, который отображает позиции для выбора сразу после загрузки формы, не требуя дополнительных действий от пользователя.

Свойства поля со списком

СвойствоОписание
AutoSizeАвтоподбор размера комбинированного поля. True – размер автоматически подстраивается под длину выбранной или введенной строки. False – размер элемента управления определяется свойствами Width и Height.
AutoTabВключение автоматической табуляции – передачи фокуса следующему элементу управления при достижении максимального числа символов при значениях свойства MaxLenght > 0. True – автоматическая табуляция включена, False – выключена.
ColumnCountУказывает количество столбцов в раскрывающемся списке. Значение по умолчанию = 1.
ColumnHeadsДобавляет строку заголовков в раскрывающийся список. True – заголовки столбцов включены, False – заголовки столбцов выключены. Значение по умолчанию = False.
ColumnWidthsШирина столбцов в раскрывающемся списке. Значения для нескольких столбцов указываются в одну строку через точку с запятой (;).
ControlSourceСсылка на ячейку для ее привязки к элементу управления ComboBox.
ControlTipTextТекст всплывающей подсказки при наведении курсора на элемент управления.
EnabledДоступ пользователя к полю и раскрывающемуся списку. True – доступ разрешен, False – доступ запрещен*. Значение по умолчанию = True.
FontШрифт, начертание и размер текста в поле.
HeightВысота элемента управления ComboBox.
LeftРасстояние от левого края внутренней границы пользовательской формы до левого края комбинированного списка.
ListПозволяет заполнить ComboBox данными из одномерного или двухмерного массива, а также обращаться к отдельным элементам раскрывающегося списка по индексам для записи и чтения.
ListIndexНомер выбранной пользователем строки в раскрывающемся списке. Нумерация начинается с нуля. Если ничего не выбрано, ListIndex = -1.
ListRowsКоличество видимых строк в раскрытом списке. Если общее количество строк больше ListRows, появляется полоса прокрутки. Значение по умолчанию = 8.
LockedЗапрет на отображение раскрывающегося списка, ввод и редактирование данных в поле. True – ввод и редактирование запрещены**, False – ввод и редактирование разрешены. Значение по умолчанию = False.
MaxLenghtМаксимальная длина строки в поле. Значение по умолчанию = 0, что означает – ограничений нет.
RowSourceИсточник строк для раскрывающегося списка (адрес диапазона на рабочем листе Excel).
TabIndexЦелое число, определяющее позицию элемента управления в очереди на получение фокуса при табуляции. Отсчет начинается с 0.
TextТекстовое содержимое (значение) поля (=Value).
TextAlignВыравнивание текста в поле: 1 (fmTextAlignLeft) – по левому краю, 2 (fmTextAlignCenter) – по центру, 3 (fmTextAlignRight) – по правому краю.
TopРасстояние от верхнего края внутренней границы пользовательской формы до верхнего края комбинированного списка.
ValueТекстовое содержимое (значение) поля (=Text).
VisibleВидимость поля со списком. True – ComboBox отображается на пользовательской форме, False – ComboBox скрыт.
WidthШирина элемента управления.

* При Enabled в значении False пользователь не может раскрывать список, а также вводить или редактировать данные в поле.
** Для элемента управления ComboBox действие свойства Locked в значении True аналогично действию свойства Enabled в значении False.

В таблице перечислены только основные, часто используемые свойства поля со списком. Еще больше доступных свойств отображено в окне Properties элемента управления ComboBox, а все методы, события и свойства – в окне Object Browser.

Вызывается Object Browser нажатием клавиши «F2». Слева выберите объект ComboBox, а справа смотрите его методы, события и свойства.

Свойства BackColor, BackStyle, BorderColor, BorderStyle отвечают за внешнее оформление комбинированного списка и его границ. Попробуйте выбирать доступные значения этих свойств в окне Properties, наблюдая за изменениями внешнего вида элемента управления ComboBox на проекте пользовательской формы.

Способы заполнения ComboBox

Используйте метод AddItem для загрузки элементов в поле со списком по одному:

Поле со списком — элемент управления формы в EXCEL

Поле со списком представляет собой сочетание текстового поля и раскрывающегося списка. Поле со списком компактнее обычного списка, однако для того чтобы отобразить список элементов, пользователь должен щелкнуть стрелку. Поле со списком следует использовать, когда требуется обеспечить возможность ввести в список или выбрать в нем только один элемент. В этом элементе управления отображается текущее значение. Этот элемент имеет много общего с элементом Список .

Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик.

  • В MS EXCEL 2007 это можно сделать через меню Кнопка офис/ Параметры Excel/ Основные/ Показывать вкладку Разработчик на ленте .
  • В MS EXCEL 2010 это можно сделать так: Откройте вкладку Файл ; Нажмите кнопку Параметры ; Нажмите кнопку Настроить ленту ; Выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик .

Теперь вставить элемент управления можно через меню: Разработчик/ Элементы управления/ Вставить .

Обратите внимание, что в этом меню можно также вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список , Флажок и т.п. Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.

Поле со списком ( Combo box, Drop down ) как, впрочем и все другие Элементы управления формы, возвращает только 1 числовое значение. См. файл примера .

Обзорную статью обо всех элементах управления формы можно прочитать здесь .

Вставка Поля со списком

Через меню Разработчик/ Элементы управления/ Вставить выберем левой клавишей мыши элемент Поле со списком (см. рисунок ниже).

После этого выпадающее меню закроется, а курсор вместо обычного толстого крестика

превратится в тонкий крестик.

Удерживая левую клавишу мыши проведите курсором вправо и немного вниз, элемент Поле со списком будет помещен на лист.

Выделение Поля со списком

Чтобы выделить Поле со списком нажмите и удерживайте клавишу CTRL , затем кликните левой клавишей на Поле со списком .

Перемещение Поля со списком и изменение его размеров

Если навести курсор на выделенный элемент Поле со списком (курсор примет форму 4-х направленных в разные стороны стрелок), затем нажать и удерживать левую кнопку мыши, то можно его переместить. Удерживая клавишу ALT можно выровнять Поле со списком по границам ячеек. Выделенный элемент также можно перемещать стрелками с клавиатуры.

Если навести курсор на углы прямоугольника или на маленькие кружки на границе, то можно изменить его размер.

Заполняем Поле со списком элементами

Заполним наше Поле со списком названиями месяцев. Для начала разместим названия месяцев на листе в диапазоне F2:F13 .

Чтобы заполнить Поле со списком , кликните на него ПРАВОЙ клавишей мыши, в появившемся контекстном меню выберите Формат объекта. Появится диалоговое окно, выберите вкладку Элемент управления (если такая вкладка отсутствует, то Вы вставили Элемент ActiveX, а не Элемент управления формы, об этом см. выше).

Введите в поле Формировать список по диапазону ссылку на вышеуказанный диапазон.

Примечание . Вместо указания ссылку на диапазон можно указать Имя диапазона (т.е. ссылку на Именованный диапазон ). Подробнее можно посмотреть в статье Выпадающий список в MS EXCEL на основе элемента управления формы .

Нажмите ОК, Поле со списком заполнится элементами.

Теперь пользователь может выбрать требуемый месяц, но Поле со списком пока не способно вернуть выбранное значение в ячейку. О том, как связать Поле со списком с ячейкой читайте ниже.

Связываем Поле со списком с ячейкой

Как было сказано выше, все Элементы управления формы возвращают значение. Это значение помещается в ячейку определенную пользователем. Чтобы связать Элемент управления с ячейкой, кликните на него ПРАВОЙ клавишей мыши, в появившемся контекстном меню выберите Формат объекта. Появится диалоговое окно, выберите вкладку Элемент управления (если такая вкладка отсутствует, то Вы вставили Элемент ActiveX, а не Элемент управления формы, об этом см. выше).

В поле Связь с ячейкой нужно ввести ссылку на ячейку. Свяжем наше Поле со списком с ячейкой А1 .

Существует и другой способ связать Элемент управления и ячейку: выделите правой клавишей мыши Элемент управления, в Строке формул введите =, затем кликните левой клавишей мыши на нужную ячейку, нажмите клавишу ENTER . Чтобы изменить ячейку, с которой связан Элемент управления, достаточно перетащить эту ячейку в нужное место, взяв за ее границу.

Использование Поля со списком

Поле со списком удобно для выбора единственного значения из заранее подготовленного списка. Выбранное значение может быть использовано для вывода соответствующих значений из той же строки. Например, если в таблице продаж содержатся объемы продаж по месяцам (диапазон F2:G13 на рисунке ниже), то выбирая в Поле со списком месяц, можно вывести соответствующий объем продаж (см. ячейку В3 ).

Необходимо помнить, что Поле со списком возвращает в связанную ячейку не сам элемент, а его позицию в списке (для месяца Май на картинке выше Поле со списком вернуло значение 5). Поэтому, чтобы вывести выбранный месяц, потребуется формула =ИНДЕКС(F2:F13;B1) (ячейка В2 ).

Формула =ИНДЕКС(G2:G13;B1) позволяет вывести объем продаж для выбранного месяца (ячейка В3 ).

Условное форматирование использовано для отображения в таблице выбранного месяца и его продаж.

Имя Элемента управления

У каждого Элемента управления есть имя. Чтобы его узнать, нужно выделить Поле со списком , в Поле имя будет отображено его имя. Чтобы изменить имя Поля со списком — введите в Поле имя новое имя и нажмите клавишу ENTER . Также имя можно изменить в Области выделения ( Главная / Редактирование/ Найти и выделить/ Область выделения ).

Зачем нам знать имя элемента управления? Если Вы не планируете управлять Поля со списком из программы VBA, то имя может потребоваться только для настройки его отображения на листе. Об этом читайте ниже.

Прячем Поле со списком на листе

Включите Область выделения ( Главная / Редактирование/ Найти и выделить )

В Области выделения можно управлять отображением не только Элементов управления, но и других объектов на листе, например рисунков.

Нажмите на изображение глаза напротив имени объекта и объект исчезнет/ появится.

Ссылка на основную публикацию
Adblock
detector