Tooprogram.ru

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

Заполнение listbox vba excel

Заполнение listbox vba excel

На этом шаге мы рассмотрим назначение, основные свойства и методы этого элемента .

Элемент управления ListBox (Список) создается с помощью кнопки Список (ListBox) (рисунок 1).

Рис.1. Список в форме

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

Приведем наиболее часто используемые свойства элемента управления ListBox .

Таблица 1. Основные свойства ListBox
СвойствоНазначение
ListIndexВозвращает номер текущего элемента списка. Нумерация элементов списка начинается с нуля
ListCountВозвращает число элементов списка
TopIndexВозвращает элемент списка с наибольшим номером
ColumnCountУстанавливает число столбцов в списке
TextColumnУстанавливает столбец в списке, элемент которого возвращается свойством Text
EnabledДопустимые значения: True (запрещен выбор значения из списка пользователем) и False (в противном случае)
TextВозвращает выбранный в списке элемент
ListВозвращает элемент списка, стоящий на пересечении указанных строки и столбца. Синтаксис:

RowSourceУстанавливает диапазон, содержащий элементы списка
ControlSourceУстанавливает диапазон (ячейку), куда возвращается выбранный элемент из списка
MultiSelectУстанавливает способ выбора элементов списка. Допустимые значения:

  • fmMultiSelectSingle (выбор только одного элемента);
  • fmMultiSelectMulti (разрешен выбор нескольких элементов посредством либо щелчка, либо нажатием клавиши Пробел );
  • fmMultiSelectExtended (разрешено использование клавиши Shift при выборе ряда последовательных элементов списка).

SelectedДопустимые значения: True (если элемент списка выбран) и False (в противном случае). Используется для определения выделенного текста, когда свойство MultiSelect имеет значение fmMultiSelectMulti или fmMultiSelectExtended .
ColumnWidthsУстанавливает ширину столбцов списка. Синтаксис:
ColumnHeadsДопустимые значения: True (выводятся заголовки столбцов раскрывающегося списка) и False (в противном случае)
ListStyleДопустимые значения:

  • fmListStylePlain (выбранный элемент из списка выделяется цветом);
  • fmListStyleOption (перед каждым элементом в списке располагается флажок и выбор элемента из списка соответствует установке этого флажка).

MatchEntryВыводит первый подходящий элемент из списка при наборе его имени на клавиатуре. Допустимые значения:

  • fmMatchEntryNone (режим вывода подходящего элемента в списке отключен);
  • fmMatchEntryFirstLetter (вводит подходящий элемент по набранной первой букве. В этом случае, предпочтительно, чтобы элементы списка были бы упорядочены в алфавитном порядке)
  • fmMatchEntryComplete (вводит подходящий элемент по полному набранному имени).

BoundColumnУстанавливает тип, возвращаемый свойством Value . А именно,

  • если свойство BoundColumn равно 0, то свойство Value возвращает индекс выбранной строки, т.е. в этом случае оно действует как свойство ListIndex ;
  • если свойство BoundColumn принимает значение из диапазона от 1 до количества столбцов в списке, то свойство Value возвращает элемент из выбранной строки, стоящий в столбце, определенном свойством BoundColumn .

Приведем наиболее часто используемые методы элемента управления ListBox .

Таблица 2. Основные методы ListBox
МетодНазначение
ClearУдаляет все элементы из списка
RemoveItemУдаляет из списка элемент с указанным номером. Синтаксис:

Параметр index определяет номер удаляемого из списка элемента

AddItemДобавляет элемент в список. Синтаксис:

  • item — элемент (строковое выражение), добавляемый в список;
  • varIndex — номер добавляемого элемента.

На следующем шаге мы рассмотрим особености заполнения списка .

VBA Excel. ListBox – заполнение списка данными

Заполнение ListBox данными с помощью кода VBA Excel. Добавление значений в список методом AddItem, с помощью свойств List и RowSource. Примеры.

Создайте в редакторе VBA Excel пользовательскую форму с любым именем и разместите на ней список с именем ListBox1. Вставляйте в модуль формы код примера, запускайте код или форму и смотрите результат.

Чтобы запустить форму, фокус должен быть на ее проекте или на одном из ее элементов управления. Чтобы запустить код, курсор должен быть в одной из его строк. Запускается код или форма нажатием клавиши «F5» или треугольной кнопки «Run Sub/UserForm»:

Заполнение ListBox методом AddItem

Метод AddItem используется для загрузки отдельного элемента в ListBox. Он создает в списке новую строку и записывает в нее значение. Используя цикл, можно загрузить в ListBox одномерный массив.

Пример 1
Загрузка элементов в ListBox по отдельности:

Результат работы кода:

Пример 2
Загрузка данных в ListBox из одномерного массива при помощи цикла VBA Excel:

Заполнение ListBox с помощью свойства List

Свойство List позволяет в коде VBA Excel скопировать целиком одномерный или двухмерный массив значений в элемент управления ListBox. А также добавлять данные в элементы двухмерного списка по их индексам в строки, созданные методом AddItem.

Пример 3
Заполнение списка данными из одномерного массива.

Загрузка значений, возвращенных функцией Array:

Загрузка значений из переменной одномерного массива:

Пример 4
Заполнение списка данными из двухмерного массива.

Результат получается следующий:

Пример 5
Заполнение списка с тремя столбцами по каждому элементу отдельно. Создаем строку и записываем значение в первый столбец методом AddItem. Значения во второй и третий столбцы записываем с помощью свойства List по индексам:

Результат работы кода будет таким же, как в Примере 4.

Заполнение ListBox с помощью свойства RowSource

Свойство RowSource позволяет загрузить в элемент управления ListBox значения из диапазона ячеек на рабочем листе Excel. Задать адрес диапазона свойству RowSource можно как в ходе выполнения кода VBA, так и в окне Properties элемента управления ListBox.

Адрес диапазона ячеек для свойства RowSource указывается по следующей формуле: «Имя_листа!Адрес_диапазона» . Имя_листа соответствует имени листа по ярлыку. Адрес в окне Properties вводится без парных кавычек.

Если адрес диапазона указать без имени рабочего листа, то данные будут загружаться в список из соответствующего диапазона активного листа. Если имя рабочего листа содержит пробелы, то его следует заключить в одинарные кавычки: «‘Данные для списка’!A1:A10» .

Пример 6
Импорт данных в одностолбцовый список из диапазона «A1:A7» рабочего листа «Лист1»:

Примеры использования элемента управления ListBox

Ранее я рассмотрел методы создания пользовательских форм и основы работы с ними (если вы никогда не работали с пользовательскими формами, рекомендую для начала прочитать указанную заметку). Далее привел целый ряд практически полезных примеров пользовательских диалоговых окон. В настоящей заметке подробнее рассказывается об использовании элемента управления ListBox.[1]

Рис. 1. Установка свойства RowSource на этапе разработки

Скачать заметку в формате Word или pdf, примеры в архиве

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

При работе с элементом управления ListBox следует учитывать, что:

  • Опции списка элемента управления ListBox могут извлекаться из диапазона ячеек (определяемого свойством RowSource) или добавляться с помощью VBA (для этого используется метод Addltem).
  • Элемент управления ListBox может быть применен для выделения одной или нескольких опций. Соответствующее поведение определяется значением свойства MultiSelect.
  • Если элемент управления ListBox не настроен на выделение нескольких опций, то значение элемента управления ListBox может связываться с ячейкой листа с помощью свойства ControlSource.
  • Элемент управления ListBox может отображаться без предварительно выбранной опции (для этого необходимо установить свойство Listlndex равным –1). Но как только пользователь выделит одну из опций списка, отменить выделение будет невозможно. Исключение из этого правила— значение свойства MultiSelect равно True.
  • Элемент управления ListBox может содержать несколько столбцов (что указывается в свойстве ColumnCount) и даже описательные заголовки (для этого используется свойство ColumnHeads).
  • Вертикальный размер элемента управления ListBox, помещенного в пользовательское диалоговое окно, не всегда совпадает с вертикальным размером объекта UserForm на экране.
  • Опции списка элемента управления ListBox могут отображаться в виде флажков, если разрешено выделение нескольких элементов, или в виде переключателей, если поддерживается только единичное выделение. Это поведение определяется значением свойства ListStyle.

Добавление опций в элемент управления ListBox

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

В двух примерах этого раздела предполагается следующее:

  • используется диалоговое окно UserForm с именем UserForm1;
  • диалоговое окно UserForm1 содержит элемент управления ListBox, который называется ListBox1;
  • рабочая книга содержит лист Лист1, в диапазоне А1:А12 которого определены опции, отображаемые в элементе управления ListBox.

Добавление опций в элемент управления ListBox на этапе разработки

Для добавления опций в элемент управления ListBox на этапе разработки необходимо, чтобы они хранились в диапазоне ячеек рабочей книги. Воспользуйтесь свойством RowSource для указания диапазона, который содержит опции элемента управления ListBox. На рис. 1 показано окно Properties для элемента управления ListBox (см. также файл listbox fill.xlsm) Свойство RowSource установлено равным Лист1!А1: А12. Когда диалоговое окно UserForm отображается на экране, элемент управления ListBox содержит двенадцать опций из этого диапазона. Опции добавляются в элемент управления ListBox на этапе разработки, сразу после того, как диапазон определяется в качестве значения свойства RowSource.

Удостоверьтесь, что в значении свойства RowSource присутствует имя листа. В противном случае элемент управления ListBox будет применять указанный диапазон в активном рабочем листе. Иногда следует идентифицировать диапазон максимально точно, указав даже имя рабочей книги. Лучше всего сначала определить имя для диапазона, а затем включить его в состав разработанного кода. Это гарантирует использование требуемого диапазона, даже если были добавлены или удалены строки, не входящие в диапазон.

Добавление опций в элемент управления ListBox на этапе выполнения

Чтобы добавить опции элемента управления ListBox на этапе выполнения, необходимо:

  • с помощью кода определить значение свойства RowSource, чтобы указать диапазон, хранящий необходимые данные;
  • создать код, использующий метод Addltem для добавления опций в элемент управления ListBox.

Следующая процедура устанавливает значения свойства RowSource элемента управления ListBox перед тем, как отображается диалоговое окно UserForm. В этом случае опции состоят из значений в ячейках диапазона Categories рабочего листа Budget:

UserForml.ListBoxl.RowSource = » Budget!Categories »
UserForml.Show

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

VBA Excel ListBox Multiselect

«VBA Excel ListBox Multiselect»
Привязка диапазона данных к ListBox.
Добавление, удаление пунктов списка.
Сортировка списка и множественный выбор элементов.

ListBox или «окно со списком» – это один из элементов управления, который может быть расположен на форме для предоставления пользователю возможности выбора одного или нескольких элементов (пунктов) из предоставленного множества (списка) вариантов …

    VBA предоставляет две возможности заполнения списка ListBox

  • Через свойство RowSource (источник строк) в список загружается определенный диапазон (колонка ячеек). В этом случае добавление новых пунктов в список или удаление существующих из списка в процессе выполнения макросов VBA не возможно… (по крайней мере, до момента присвоения свойству RowSource значения пустой строки).
  • Через методы AddItem и RemoteItem (добавление или удаление пунктов списка)… Повторю, что для такой возможности, свойство RowSource должно иметь пустое значение.
  • Разместить на форме несколько радиокнопок, позволяющих загружать в элемент ListBox1 списки из разных колонок листа Excel.
    Обеспечить возможность сортировки загруженных списков в ListBox1.
    Обеспечить возможность множественного выбора элементов из списка и вывод результата выбора в окно сообщений MsgBox.

    Привязка к списку диапазона значений через свойство RowSource

    Считаю, что для удобства реальной работы со списками на листах Excel (добавление новых значений в ячейки соответствующих колонок или удаление существующих) необходимо использовать функцию для автоматического определения номера последней заполненной строки в указанном столбце…
    Метод Find объектов класса Range (рекомендую заглянуть в его справку) очень помогает в этом вопросе….
    И так, функция может выглядеть, например, следующим образом….

    Function GetLastRowFromColumn(numColumn As Integer) As Integer
    GetLastRowFromColumn = Columns(numColumn).Cells.Find(«*», , , , xlByRows, xlPrevious).Row
    End Function

    Как видите, единственным параметром она получает номер колонки (правда, для простоты я оставил всего один лист в книге, а иначе бы лист тоже пришлось бы указать как параметр), а возвращает номер строки той ячейки, которую вернул метод Find…

    Тогда обработчики событий щелчков мышью по радиокнопкам будут выглядеть так…

    Private Sub OptionButton1_Click()
    lastrow = GetLastRowFromColumn(1)
    If OptionButton1 Then Me.ListBox1.RowSource = «=A1:A» & lastrow
    End Sub

    Private Sub OptionButton2_Click()
    lastrow = GetLastRowFromColumn(2)
    If OptionButton2 Then Me.ListBox1.RowSource = «=B1:B» & lastrow
    End Sub

    Private Sub OptionButton3_Click()
    lastrow = GetLastRowFromColumn(3)
    If OptionButton3 Then Me.ListBox1.RowSource = «=C1:C» & lastrow
    End Sub

    Можно было бы и еще упростить (до одной строки в процедуре),

    Me.ListBox1.RowSource = «=A1:A» & GetLastRowFromColumn(1)

    но считаю, что наличие переменной lastrow помогает просматривать ее значение при отладке, а это сэкономленное время…. которое дороже, чем уменьшение объема кода…

    В общем, первая радиокнопка помещает в список диапазон из колонки А (используя свойство RowSource), а вторая и третья, соответственно, из колонок В и С

    Множественный выбор

    Основное свойство элемента ListBox – это List… индексированный список значений… (As Variant) Поэтому к любому элементу списка можно обратиться по индексу… ,например List(idx)…
    И так же важно второе индексированное свойство Selected(idx), которое представляет собой массив логических величин, показывающий, выделен ли конкретный элемент списка пользователем или нет…

    Таким образом, обработчик кнопки «Сообщение» выглядит так…

    Private Sub CommandButton1_Click()
    Dim n As Integer, s As String
    s = «»

    For n = 0 To Me.ListBox1.ListCount — 1
    If Me.ListBox1.Selected(n) Then
    s = s & Me.ListBox1.List(n) & vbLf
    End If
    Next n

    If s = «» Then
    MsgBox «Нет выбранных пунктов», 0, «Выбранные пункты списка»
    Else
    MsgBox s, 0, «Выбранные пункты списка»
    End If

    Он формирует строку s , только из выделенных пунктов списка и выводит соответствующее сообщение… Встроенная константа vbLf означает переход на новую строку…

    Сортировка списка

    А вот для сортировки списка нам придется отказаться от свойства RowSource , т.к. изменение порядка элементов списка будет противоречить привязанному диапазону. VBA справедливо заругается…

    Вот процедура сортировки объекта ListBox (который передается в виде параметра As Object)

    Sub mySort(aL As Object)
    Dim locList() As Variant, siz As Long
    ‘Сортирует список ListBox (свойство .List — как массив Variant)
    Dim j As Long
    siz = UBound(aL.List)
    ReDim locList(UBound(aL.List))

    For j = 0 To siz
    locList(j) = .List(j)
    Next j

    .RowSource = «»
    .Clear
    mySortArray locList

    For j = 0 To siz
    .AddItem (locList(j))
    Next j

    End With
    End Sub

    Как видите, сначала создаем массив locList() нужной размерности и заполняем его элементами списка…
    Затем отвязываем список от диапазона (aL.RowSource = «») и очищаем его (aL.Clear)
    А полученный массив сортируем обычным образом (любым из алгоритмов сортировки).
    Все. Осталось загрузить отсортированный массив в список, используя метод AddItem, конечно же, в цикле…

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

    ListBox1.MultiSelect = fmMultiSelectMulti



    Все…

    Читать еще:  Как сделать сортировку в word
    Ссылка на основную публикацию
    Adblock
    detector