Rowsource vba excel

VBA Excel ListBox Multiselect

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

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

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

Разместить на форме несколько радиокнопок, позволяющих загружать в элемент 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

…
…
Все…

Rowsource vba excel

You can use the «AddItem» method when you have a single column listbox.
If you try to add items to a listbox that has a non empty RowSource property you will get a «permission denied» error.

Currently Selected Item

Obtaining the currently selected item in a single selection list box

Multiple Columns

A listbox can contain multiple columns by using the ColumnCount property.
You can use the «AddItem» combined with the List property when you have multiple columns.
All list entries start with a row number of 0, and a column number of 0, ie List(0,0) = «text»
If you want to add items to a multi column listbox, you need to use «AddItem» to add a new row and then either «List» or «Column» to add the specific items past the first column.

Both column and row numbers in a listbox start at 0 by default and not 1.
The only way to obtain the selected items in a multiple selection list box is to cycle through the whole list.

Adding using an Array

If you data is stored in a one-dimensional array you can assign the array directly using the List property.

If you data is stored in a two-dimensional array you can assign the array directly using the List property.

TextColumn

This property allows you to display one set of values to the user but return a different value when selection has been made.
Use the Text property to return the column specified in the TextBound column.
If you use the Value property you will always get the item in the first column.

BoundColumn

The BoundColumn property identifies which column is referenced when you refer to the Value property of a listbox entry.

No items selected

It is possible to display a listbox with no items selected (when the listindex = -1).
Although once an item is selected it is not possible to unselect all the items.

Multiple selections

By default only a single item can be selected although this can be changed by changing the MultiSelect property.
You can only make multiple selections with a listbox — not a combo box.

RowSource

The items in a Listbox can be retrieved from an Excel range of cells by using the RowSource property.
Make sure you include the worksheet name otherwise the active sheet will be used.

If you populate a listbox using the RowSource method you then can’t populate a second listbox using the «List» method.
If you populate a listbox using the RowSource method you cannot use the RemoveItem method.

Adding Column Headers

You can only display column headers when you use the RowSource property, not when you use an array or add items individually.
To display column headers set the ColumnHeads property to True.
Do not include the column headings on the worksheet in the range defined for RowSource.
The row directly above the first row of the RowSource will be automatically used.

Adding Unique Items

You should add all the items to a collection ensuring that only unique items get added and then add all the items from the collection to the listbox.

It might also be worth sorting the collection before you add it to the listbox.

Change the Integral Height to False and a line is roughly 13.42
Arial, 10, Regular
It is possible to have a drop-down listbox — change the property — doesn’t have to be a combo box !!
It is possible to display equally spaced items in a list box by using a monospaced font such as Courier New. A better approach is to use multiple columns.
Do you have to populate a listbox with data to be able to assign an array to it . I DON’T THINK YOU DO !!
The vertical height of a listbox in design mode may not be the same height when the userform is actually displayed.

Rowsource vba excel

Советчик

Профиль
Группа: Модератор
Сообщений: 20437
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 32
Всего: 449

Treod, уточните вопрос. Речь о VB6 или o VBA?

Если о VB6 — есть ли подключение к нужному Excel-файлу, и если да, то по какой технологии.
Если о VBA — о каком конкретно приложении речь.

О(б)суждение моих действий — в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция — Администрация форума.

1. Публиковать ссылки на вскрытые компоненты

2. Обсуждать взлом компонентов и делиться вскрытыми компонентами

• Литературу по VB обсуждаем здесь
• Действия модераторов можно обсудить здесь
• С просьбами о написании курсовой, реферата и т.п. обращаться сюда
• Вопросы по реализации алгоритмов рассматриваются здесь
• Используйте теги [code=vb][/code] для подсветки кода. Используйтe чекбокс «транслит» (возле кнопок кодов) если у Вас нет русских шрифтов.

• FAQ раздела лежит здесь!

Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, Akina.

[ Время генерации скрипта: 0.1261 ] [ Использовано запросов: 21 ] [ GZIP включён ]

Примеры использования элемента управления 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:

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Training VBA 2 Lesson 5

Filling UserForm Controls

| BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

Filling UserForm Controls

The vast majority of UserForms that are designed within Excel are used so users can easily select and input data. This also ensures that any entries that are entered into a spreadsheet are within the requirements needed. Possibly the best way to achieve both of these requirements is to supply a list of entries for the user to chose from. Excel has many Controls that can be placed on a UserForm that can make this not only easy for the user, but also for the designer of the UserForm. The two most useful Controls for this are the “ComboBox” and the “ListBox”. Let’s look at each of these Controls.

ComboBox

A ComboBox is so called because it combines the features of two other Controls. These are the TextBox and the ListBox. While it will allow a user to select an existing entry from a list of entries, as with a ListBox, it will also allow the user to enter a new entry, as with a TextBox.

The default Property for a ComboBox is the Value property.

The default Event for a ComboBox is the Click event.

A ComboBox will only display one row of data at any one time unless the user selects the drop arrow on the right of the ComboBox. The rows of data can then be between 1 and the total number of rows in the ComboBox. The default is eight. This is set by the ListRows Property of the ComboBox.

A ListBox is quite similar to a ComboBox but has three important differences. The first is that we can have a ListBox display a specified number of rows without user intervention. The second is we can set a ListBox so that a user can select more than one item at a time. The third is that a user cannot directly enter any new values into the ListBox.

The default Property for a ListBox is the Value property.

The default Event for a ListBox is the Click event.

Which One To Use?

While it is true that both the ListBox and the ComboBox have many similar Properties and Events, there are times that a ListBox is called for and times when a ComboBox is called for. It is up to us as the designer to make the correct decision.

The fact that a ListBox can display more than one row at a time is really more of a cosmetic effect than anything else. So this should really be our last reason for using a ListBox over a ComboBox or vice versa. The two differences that should dictate our decision are:

Will the user need to type in their own entries.

Will the user need to be able to select more than one entry at a time.

If the answer is yes to number one, then it’s a probably a ComboBox we need. If the answer is yes to number two then it’s probably a ListBox we need.

Putting Data Into Our Controls

Once we have made our decision on which Control we are going to use we next need to decide which Method we are going to use to fill our Control with data. This is usually quite a simple decision and we will nearly always use a range of Worksheet cells to hold all our data and refer our Control to this range. Ideally, the Worksheet we use should be hidden from view. We can do this via Window>Hide or via VBA using the Worksheets Visible Property. eg;

I would opt for the latter in most cases. As I have said, we will usually opt for storing our data within a range of cells. This means we can easily add data to the range either manually or via VBA. The other option we have is to use the AddItem Property and I will discuss this after we have looked at the method we would use to refer to a range of cells. This is done via the RowSource Property.

Row Source — Applies to both a ComboBox and ListBox

A valid setting for the RowSource Property is a String. The String that we use would be either a valid cell address or a valid range name.

All of the above are valid settings for the RowSource Property. The RowSource can be set at either Run-time or Design-time. If the RowSource were set at Design-time we would enter our String into the Property window of the ComboBox or ListBox without quotation marks, eg;

As you can see the range we specify can be either a single column or multiple columns. I will explain this further later on when we look at the column setting Properties of both Controls.

AddItem — Applies to both a ComboBox and ListBox

This Property can only be set at Run-time. If the ListBox or ComboBox has only one column then AddItem will add an item to the ListBox or ComboBox list. If the ListBox or ComboBox has more than one column then AddItem will add a new row to the ListBox or ComboBox list

The syntax for the AddItem Property Is: [ item [, varIndex]] Both arguments are optional. The item is used to specify the item or the row to add. The number of the first item or row is always 0 (zero), the second is 1, and the third is 2 and so on…. The varIndex is used to specify the position within the Control where the “item” is to be placed. If you had a ComboBox that contained a ten row list and you used the AddItem to add another item you could use

ComboBox1.AddItem «Horse», 5

This would add the text “Horse” to our ComboBox as the fifth item or row in the list. If we had omitted the varIndex from the AddItem then the text “Horse” would be placed as the last item or row in the list.

The AddItem is best suited if

You have only a short list of entries to add

You need to increment the items added. In which case you could place the AddItem Property within a Loop.

For most other cases the RowSource Property is better suited.