Remkomplekty.ru

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

Excel vba list

Работа со списками в Visual Basic

Приведённые практические работы по программированию могут быть использованы при изучении элементов выбора Visual Basic. Списки целесообразно изучать после знакомства с переключателями (OptionButton) и флажками (CheckBox).

При объяснении нового материала удобно использовать распечатки с изложением теоретического материала и примерами программ, которые выдаются каждому студенту (ученику), либо методические пособия (мы используем авторское пособие “Практикум по решению задач в среде Visual Basic”, часть 1, 2).

Приведённые практические работы рассчитаны на 3 занятия по 2 часа. Задания самостоятельной работы можно использовать в качестве домашних заданий.

В Visual Basic существует 2 вида списков:

  • комбинированный список ComboBox;
  • список ListBox.

1. Комбинированный список ComboBox

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

Свойство Style задаёт внешний вид комбинированного списка (по умолчанию оно равно 0 — элемент ComboBox отображается в виде текстового поля со стрелкой справа, позволяющей развернуть весь список и выбрать требуемое значение). Если Style = 1, то постоянно отображается весь список (или список с полосой прокрутки).

Основные свойства ComboBox

0 — Dropdown Combo (раскрывающийся комбинированный список);

1 — Simple Combo (простой комбинированный список);

2 — Dropdown List (раскрывающийся список).

Для добавления нового элемента в список используется метод AddItem.

Пример: Combo1.AddItem “Пение” — добавить в список Combo1 слово Пение.

Добавляемые в список элементы имеют тип String; [index] — порядковый номер в списке, под которым будет находиться новый элемент (необязательный параметр).

Добавить элементы в комбинированный список можно также на этапе разработки с помощью свойства List. Для добавления очередного элемента в список нужно ввести этот элемент в свойстве List и нажать комбинацию клавиш Ctrl + Enter.

2. Список ListBox

Данный элемент применяется в том случае, когда пользователю необходимо выбрать один элемент из имеющегося списка для выполнения определенных действий. Объекты ListBox используются обычно в сочетании с управляющими кнопками, которые позволяют обрабатывать элементы списка (добавлять, удалять и т. д.).

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

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

  • щелчок мыши при нажатой клавише Shift позволяет выделить несколько подряд расположенных элементов;
  • щелчок мыши при нажатой клавише Ctrl даёт возможность выделять группу несмежных элементов списка.

Основные свойства элемента ListBox

0 — одноколончатый список с вертикальной прокруткой;

1 — одноколончатый список с горизонтальной прокруткой;

>1 — многоколончатый список с горизонтальной прокруткой

0 — None (обычный список);

1 -Simple (простой множественный выбор — щелчок мыши или нажатие Пробел выделяет очередной элемент или снимает выделение);

2 — Extended (расширенный множественный выбор) — выделение с использованием вспомогательных клавиш.

Методы ListBox аналогичны методам ComboBox.

Практическая работа 1. Проект “Результаты сессии”

Задание: создать проект, содержащий 3 списка: список студентов группы; список студентов, получивших зачёт и список неаттестованных. Двойным щелчком мыши в списке студентов (List1) выбирается фамилия и добавляется в список “Зачёт” (List2). Двойным щелчком в списке “Зачёт” фамилия возвращается назад. Кнопкой “Добавить в список” фамилия, введённая в текстовое поле Text1, заносится в список List1. Кнопка “н/а” заносит выбранную фамилию из списка студентов в список List3 ( н/а).

Private Sub Form_Load() ‘загрузка формы

List1.AddItem «Чесноков»: List1.AddItem «Симонов»

List1.AddItem «Трушков»: List1.AddItem «Морозова»

Private Sub List1_DblClick() ‘перенести из списка в зачёт

List1.RemoveItem List1.ListIndex ‘удалить из списка

Private Sub List2_DblClick() ‘перенести назад в Список из Зачёт

Private Sub List3_DblClick() ‘перенести назад в список из н/а

Private Sub Command1_Click() ‘добавить в список из текст. поля

Private Sub Command3_Click() ‘добавить в н/а

List1.RemoveItem List1.ListIndex ‘удалить из списка

Задания

  1. Добавьте в проект список оценок (2, 3, 4, 5) – List4.
  2. Измените проект таким образом, чтобы можно было добавлять в результаты фамилию и оценку ученика.
  3. Создайте кнопку “Очистить результаты”.

Примерный вид формы показан на рисунке.

Практическая работа 2. Проект “Переводчик”

Задание: создать проект для проверки знания иностранных слов (англо-русский и русско-английский переводчик).

Элементы интерфейса проекта:

  1. Комбинированные списки Combo1 и Combo2 – для хранения русских и английских слов. Индексы русских слов в списке должны соответствовать индексам соответствующих им английских слов.
  2. Текстовое поле Text1 — для вывода случайного слова из списка, которое необходимо перевести.
  3. Текстовое поле Text2 — для вывода комментария “Верно” или “Неверно”.
  4. Текстовые поля Text3, Text4 — для вывода количества вопросов и верных ответов.
  5. Таймер Timer1 – для временной задержки при выводе очередного вопроса.
  6. Image1 – для вывода рисунка, соответствующего слову.
  7. Элемент MMControl1 — для воспроизведения звука.

Элемент MMControl является дополнительным компонентом VB (Activ X). Для его установки необходимо:

  • Открыть в меню Проект – Компоненты вкладку Управление (Control);
  • Установить флажок MicrosoftMultimedia Control 6.0;
  • На панели элементов управления появится новый значок MMControl.

Рисунки к проекту сохраните в папке с проектом в порядке следования слов в списке Combo1:

Ris0.jpg – рисунок к первому слову списка;

Ris1.jpg – рисунок ко второму слову списка и т.д.

Для определения имени файла рисунка в проекте используем переменную Path:

Path = «Ris» & Mid(Str(n), 2, 1) & «.jpg»

Функция Mid используется для того, чтобы убрать пробел перед числом в имени файла, т.к. функция Str(n) для положительного числа формирует строку с пробелом перед числом. Таким образом, получаем неверное имя файла Ris 0.jpg, вместо Ris0.jpg.

Рассмотрим основные процедуры проекта.

1) В процедуре загрузки формы нужно заполнить списки русских и английских слов.

Dim n, k, z, p As Integer, Path As String

‘n – индекс слова в списке; k – количество верных ответов; z – кол. вопросов; p – признак направления перевода; Path – путь к файлу с рисунком на диске

Private Sub Form_Load()

Combo1.AddItem «собака»: Combo1.AddItem «дельфин»

Combo1.AddItem «осёл»: Combo1.AddItem «красный»

Combo1.AddItem «зеленый»: Combo2.AddItem «Dog»

Combo2.AddItem «Dolphin»: Combo2.AddItem «Donkey»

Combo2.AddItem «Red»: Combo2.AddItem «Green»

k = 0 ’количество верных ответов

z = 0 ‘количество вопросов

2) При выборе кнопки “Перевод рус/англ” должно появляться русское слово. Для данного слова нужно найти перевод в списке английских слов. Список русских слов при этом должен скрываться.

Private Sub Command5_Click() ‘Перевод рус/англ

p = 1 ‘признак перевода – рус/англ

Combo1.Visible = False ‘скрыть список русских слов

Combo2.Visible = True ‘показать список англ. слов

Label1.Caption = «Русское слово»

Timer1.Enabled = True ‘включить таймер

3) В процедуре таймера случайным образом определяем индекс слова в списке слов. Если p=1, то было выбрано направление перевода рус/англ., поэтому слово выбирается из списка русских слов Combo1.

Private Sub Timer1_Timer()

z = z + 1 ‘подсчёт кол. вопросов

n = Int(Rnd * 4) ‘случайный выбор индекса слова в списке слов

If p = 1 Then Text1.Text = Combo1.List(n)

4) Для выбора перевода заданного русского слова пользователь делает щелчок по списку английских слов. Если индекс исходного русского слова n совпадает с индексом слова-перевода, выбранного пользователем, то ответ верный.

Private Sub Combo2_Click() ‘список англ. слов

If n = Combo2.ListIndex Then ‘если ответ верный

Image1.Visible = True ‘показать рисунок

MMControl1.FileName = «da.wav» ‘загрузить звуковой файл

Path = «Ris» & Mid(Str(n), 2, 1) & «.jpg»

Image1.Picture = LoadPicture(Path) ‘загрузить файл с соответствующим рисунком

Else ‘если ответ неверный

MMControl1.Command = «sound» ‘воспроизвести звук. файл

Задания:

  1. Создайте процедуру кнопки “Перевод англ/рус”.
  2. Добавьте кнопку Сброс.
  3. Подберите 2 звуковых файла для подтверждения верного и неверного ответа, подключите эти файлы к проекту.
  4. Создайте заставку к проекту.

Самостоятельная работа

  1. Создать проект “Выбор шрифта”, позволяющий выбрать с помощью списков тип шрифта, размер, начертание (свойства): жирный, подчёркнутый, курсив. Выбранные параметры должны применяться к метке Образец.

Свойства шрифта в метке задаются следующими командами:

Label1.Font.Italic = True – установить курсив

Label1.Font.Bold = False – отменить жирный шрифт

Label1.Font.Underline = True – установить подчёркнутый шрифт

Label1.Font = Combo1.Text – применить к метке тип шрифта, выбранный в комбинированном списке 1

Label1.Font.Size = Combo1.Text — применить к метке размер шрифта, выбранный в комбинированном списке 1

  • Создать проект “Заказы CD/DVD дисков по каталогам”. На форме разместить 2 комбинированных списка: “Игры” и “Обучающие программы”; список “Заказано”. Кнопки “Добавить в заказ” добавляют выбранный элемент в список “Заказано”. Кнопка “Удалить из заказа” удаляет выбранный элемент из заказа. Предусмотреть подсчёт количества заказов в списке.
  • Создать проект “Результаты кросса” с использованием 3-х списков. Двойным щелчком мыши в списке студентов (List1) выбирается фамилия и добавляется в список “Зачёт” (List2). Двойным щелчком в списке “Зачёт” фамилия возвращается назад. Кнопкой “Добавить в список” фамилия, введённая в текстовое поле Text1, заносится в список List1. Кнопка “н/а” заносит выбранную фамилию из списка в список List3 (н/а).
  • Литература

    1. Глушаков С.В. , Мельников В.В., Сурядный А.С. Программирование в среде Windows. Visual Basic 6.0. М.: ООО “Издательство АСТ”, 2001
    2. Браун С. Visual Basic. Учебный курс. – Спб.: Питер, 2002

    Поиск на листе Excel

    Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.

    Поиск перебором значений

    Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:

    Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.

    Поиск функцией Find

    Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:

    Вкратце опишу что делают строчки данного кода:
    1-я строка: Выбираем в книге лист «Данные»;
    2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;
    3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

    Полностью синтаксис оператора поиска выглядит так:

    Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

    What — Строка с текстом, который ищем или любой другой тип данных Excel

    After — Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

    LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

    LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

    SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

    SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

    MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

    MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

    SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

    Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

    Примеры поиска функцией Find

    Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

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

    Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

    В ниже следующем примере используется другой вариант продолжения поиска — с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

    Пример 3: Продолжение поиска с использованием Find с параметром After.

    Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

    Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

    Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

    Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

    Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

    Свойство FindFormat можно задавать разными способами, например, так:

    Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

    Пример 5: Найти последнюю колонку и столбец, заполненные данными

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

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

    Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы «т», при этом после этого слова может следовать любой текст.

    Для поиска функцией Find по маске (шаблону) можно применять символы:
    * — для обозначения любого количества любых символов;
    ? — для обозначения одного любого символа;

    — для обозначения символов *, ? и

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

    ?, чтобы искать именно звездочку (*), нужно написать

    * и наконец, чтобы найти в тексте тильду, необходимо написать

    Поиск даты с помощью Find

    Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

    • Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
    • В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

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

    Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

    Пример 8: Найти 1 марта 2018 г.

    Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

    Тем не менее, можно найти, например, 1 марта независимо от года.

    Пример 9: Найти 1 марта любого года.

    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.

    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.

    ШКОЛА ПРОГРАММИРОВАНИЯ

    Вы здесь

    Сообщение об ошибке

    Создаем расширенный список выбора в 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 = «»

    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

    Читать еще:  Excel vba колонтитулы
    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×