Foreversoft.ru

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

Excel vba list

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»:

VBA ListObjects

What are ListObjects in VBA?

In a table normally what we see is a data set but in VBA terminology there are much more such as there is range of the total data list range, the column is known as the list column and row is known as the list row and so on, so in order to access this properties we have an inbuilt function known as Listobjects and which is used with the worksheet function.

VBA ListObject is a way of referring to the excel tables while writing the VBA code. By using vba LISTOBJECTS we can create, delete table, and totally we can play around with excel tables in VBA code. Excel Tables are tricky, beginners and even to an extent intermediate level users find it difficult to work with tables. Since this article talks about referencing excel tables in VBA coding it is better you have good knowledge about tables in excel.

Читать еще:  Выгрузить данные из sql в excel

When the data is converted to tables we no longer work with a range of cells rather we need to work with table ranges, so in this article, we will show you how to work with excel tables to write VBA codes efficiently.

Create Table Format Using ListObjects in Excel VBA

For example, look at the below excel data.

Using VBA ListObject code we will create a table format for this data.

  • For this data first we need to find what is the last used row & column, so define two variables to find this.

Code:

  • To find the last used row and column use the below code.

Code:

  • Now define one more variable to hold the reference of the data.

Code:

  • Now set the reference to this variable by using the below code.

Code:

Now we need to use VBA “ListObject.Add” method to create a table and below is the syntax of the same.

ListObject.Add (Source, XlListObjectHasHeaders, Destination, TableStyleName)

Source: This is nothing for which range of cells we are inserting the table. So we can supply two arguments here i.e. “xlSrcRange” and “xlSrcExternal”.

XlListObjectHasHeaders: If the table inserting data has headers or not. If yes we can provide “xlYes” if not we can provide “xlNo”.

Destination: This is nothing but our data range.

Table Style: If you want to apply any table style we can provide styles.

  • Ok, now in the active sheet we are creating the table, so below code would create a table for us.

Code:

  • After this, we need to give a name to this table.

Code:

  • Below is the full code for your reference.

Code:

Ok, let’s run the code and see the magic.

It has created the table to the mentioned data and given the table name as “EmpTable”.

Formatting Excel Tables with VBA ListObjects

Once the Excel table has been created we can work with tables by using vba ListObject collection.

  • First, define the variable as “ListObject”.

Code:

  • Now set the reference to this variable by using the table name.

Code:

Now the variable “MyTable” holds the reference for the table “EmpTable”.

  • Enter the variable name and put a dot to see the properties and methods of the VBA ListObject.
Читать еще:  Как из видео извлечь текст

For example, if we want to select the entire table then we need to use the “Range” object and under this, we need to use the “Select” method.

Code:

This would select the entire data table including the heading.

  • If you want to select only the contents of the table without headers then we need to use “DataBodyRange”.

Code:

Like this, we can play around with tables.

  • Below is the list of activity codes for your reference.

Code:

Like this, we can use the “ListObject” collection to play around with excel tables.

Канал в Telegram

Вы здесь

Создаем расширенный список выбора в Excel с помощью VBA

В статье «Создание списка выбора в Excel 2003» были рассмотрены способы создания списков выбора, но в этих способах есть небольшое неудобство при работе с большими списками, а именно поиск нужного значения. В этой статье будет описан способ создания списков в Excel с помощью средств языка VBA, более универсальный, с реализованной возможностью быстрого поиска.

Задача: Создать форму выбора значений из списка и их вставка в ячейку. Вызов формы осуществляется только на определенных столбцах, при этом на каждый столбец отображается свой набор значений. Форма вызывается правой кнопкой мыши(ПКМ).

Приступим к реализации. Для начала нам необходимо создать списки значений на отдельном листе. Назовем этот лист «Списки».

Начиная с первого столбца, первой строки, создадим списки следующим образом:

где, первая строка — наименование списка.

Лист, в котором необходимо заполнение и выбор значений, назовем реестр и выглядеть будет так:

т.е. нам необходимо с помощью выбора из списка подставлять значения в столбцы : B, C, D, F.

Открываем редактор VB (Alt+F11)
В редакторе зададим нашим листам в окне «Properties» следующие имена:

Листу с реестром зададим имя — reestr.
Лист со списками же, получит имя — spisok.

Далее, создадим форму с именем (name) «SelectList», добавим на нее элементы Listbox и TextBox.

Имена для этих элементов следующие:
Listbox — List
TextBox — SearchText

В итоге получится форма следующего вида:

Теперь необходимо прописать процедуры в код нашей формы. Для этого выделите форму и нажмите F7, после чего вставьте следующий код:

‘глобальные переменные
Public R, C, count As Long
Public SheetOut As String
Public numList As Byte

‘Вставка значения двойным кликом в общем листе
Private Sub List_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If List.Text <> «» Then
Sheets(SheetOut).Cells(R, C) = List.Text
SelectList.Hide
End If
End Sub

‘Вставка значения нажатием Enter в листе Результата поиска
Private Sub List_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii = 13 Then
If List.Text <> «» Then
Sheets(SheetOut).Cells(R, C) = List.Text
SelectList.Hide
End If
End If

End Sub

Private Sub SearchText_Change() ‘Процедура поиска и отображения результатов
count = 2
List.Clear

While Trim(spisok.Cells(count, numList)) <> «»
If InStr(1, LCase(spisok.Cells(count, numList)), LCase(SearchText.Text)) > 0 Then
List.AddItem spisok.Cells(count, numList)
End If

count = count + 1
Wend

End Sub

Public Sub LoadList() ‘Процедура загрузки списка
count = 2
List.Clear ‘очищаем список
SearchText.Text = «»

Читать еще:  Меню сервис в excel

While Trim(spisok.Cells(count, numList)) <> «»
List.AddItem spisok.Cells(count, numList)
count = count + 1
Wend
SelectList.Caption = «Выберите — » & spisok.Cells(1, numList)

End Sub

Все. Элементы формы запрограммированы. Немного о процедурах по порядку:

  • List_DblClick — процедура вызываемая двойным кликом мыши по выбранному в списке значению. При выполнении, происходит вставка значения в ячейку листа «Реестр» координаты которой переданы в глобальных переменных R(строка), C(столбец);
  • List_KeyPress — процедура аналогична по действию с процедурой List_DblClick,только вставка осуществляется при нажатии клавиши Enter;
  • SearchText_Change — процедура, вызываемая при вводе символов в строку поиска. В результате работы данной процедуры, происходит обновление списка, значения которого содержат введенные символы в поле SearchText. Ввести достаточно часть названия и будут отображены значения(значение) содержащие эту часть названия;
  • LoadList — дополнительная процедура, которая загружает список в элемент List. Вызов производится в следующей части кода.

Для того, чтобы произвести вызов формы с нужным нам списком при нажатии правой кнопки мыши по ячейке листа Excel, необходимо в объект reestr (лист с реестром) добавить следующий код:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
‘вызов списка по щелчку ПКМ
Select Case Target.column
Case 2: loadform Target, 1, Cancel
Case 3: loadform Target, 2, Cancel
Case 4: loadform Target, 3, Cancel
Case 6: loadform Target, 4, Cancel
‘.
End Select
End Sub

Private Sub loadform(ByVal Target As Range, numList As Byte, Cancel As Boolean)
Cancel = True ‘отключает показ контекста после закрытия формы

With SelectList ‘ отправляем параметры форме, загружаем список и откр. форму
.R = Target.Row
.C = Target.column
.numList = numList
.LoadList
.SearchText.SetFocus
.SheetOut = reestr.Name
.Show
End With

End Sub

  • loadform — загружает форму со списком, передает координаты ячейки, в которой произошел правый клик (глобальные переменные С и R) и вызывает процедуру LoadList для загрузки списка;
  • Worksheet_BeforeRightClick — процедура, которая вызывается по событию нажатия ПКМ на листе. Собственно это наша ключевая процедура в которой происходит определение столбца, в котором необходимо использовать форму выбора из списка и задается номер загружаемого списка. Все это происходит в операторе Select Case.

Логика работы!

В Target.column возвращается номер столбца, в котором произошел клик ПКМ. Этот номер ищется в списке Select , если номер найден, то запускается процедура loadform с загрузкой соответствующего списка.

Например, Case 2: loadform Target, 1, Cancel, если кликнули ПКМ по второму столбцу (Case 2), то вызываем процедуру loadform, передаем ей координаты ячейки, которые находятся в Target, затем, указываем номер столбца списка (лист «Списки»), который необходимо загрузить.

Соответственно, для столбца в реестре:
2 загружаем список 1 (Месяц)
3 загружаем список 2 (Склад)
4 загружаем список 3 (НаимТовара)
6 загружаем список 4 (НаимКА)

На другие столбцы реакции не произойдет, будет вызвано контекстное меню. Этот список можно сократить (достаточно убрать Case 2, 3 ..) или добавить.

Снимок работающего списка

Вот и все. Если возникли вопросы, пишите.

П.С.: Данный вариант реализации списков отлично работает в Excel 2003-2007-2010

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