При вводе данных очень легко допустить ошибку или опечатку. Исключить вероятность ввода неверных данных помогают справочники и базы данных.
В продолжение темы предыдущего обзора рассмотрим пример создания справочника для выбора и ввода данных, а также возможность связи пользовательских форм между собой.
▶️ Исходные данные
В качестве исходника будем использовать файл с примером рассмотренный в предыдущем обзоре:
А также определим лист на котором будет располагаться справочник:
🎯 Задача на сегодня:
- создать дополнительную форму (UserForm);
- разместить недостающие элементы управления;
- задать макросы посредством которых:
- устанавливается связь между формами;
- происходит заполнение/корректировка справочника (на листе);
- формируется выпадающий список содержащий данные из справочника (на главной форме).
▶️ Создание форм и размещение элементов управления
Так как основная форма уже создана будем использовать ее, добавив элемент управления:
- ComboBox
- CommandButton (кнопка)
А также, создадим еще одну форму, посредством взаимодействия с которой будет заполнятся и корректироваться справочник:
▶️ Работа с главной формой
Первым новым элементом управления на главной форме является ComboBox3 в котором должны быть отображены все возможные исполнители. Но, как видите сейчас он пуст:
Чтобы данные отображались в данном ComboBox следует назначить соответствующий макрос.
🔔 Что важно! Так данный ComboBox должен содержать актуальные значения не только при инициализации, но и при переходе с одной формы на другую макрос назначаем на событие активации формы:
➡️ см. подробнее: Инициализация и активация события
📝 Краткое описание:
- Определяем рабочий лист ws, на котором находятся данные.
- Находим последнюю заполненную строку в столбце A (колонка A) и сохраняет это значение в переменной lastRow.
- Заполняем ComboBox3 значениями из столбца A, предполагая, что они уникальны и находятся в диапазоне от A1 до последней заполненной строки lastRow
Событие UserForm_Activate позволит автоматически выполнять макрос каждый раз, когда форма становится активной, в том числе при переходе с одной формы на другую. Таким образом, ComboBox всегда будет содержать актуальные значения.
🔔 Обратите внимание: Аналогично, как и в ComboBox1 в ComboBox3 с помощью свойства Style установлена возможность выбора только из выпадающего списка, что не позволит вводить данные с клавиатуры:
Теперь, при активации формы ComboBox3 заполняется уникальными значениями из столбца A.
▶️ Взаимосвязь форм
Вторым новым элементом управления на главной форме является CommandButton (кнопка).
Именно по клику на кнопку будет получен доступ к справочнику:
Чтобы задать связь между формами пишем совсем простой макрос:
🔔 Обратите внимание! в данном случае используется метод Me.Hide, что позволит сохранить главную форму в памяти приложения.
➡️ см. Подробнее об отличии методов Unload Me и Me.Hide в обзоре:
Теперь, у пользователя есть доступ к справочнику:
▶️ Работа с формой "Справочник"
Как говорилось выше, посредством формы пользователь будет вносить изменения в справочник. А следовательно, она должна иметь доступ к источнику данных.
Здесь все просто! Основной код мы написали выше 😉.
Так как источник данных для ComboBox3 на главной форме и ComboBox1 на форме справочника один, то достаточно внести небольшое изменение:
Теперь, при переходе на форму "Справочник" на ней так же будет выпадающий список:
Следующий элемент управления CommandButton3 (кнопка) по клику на которую должен осуществляться возврат к главной форме:
Так как обращение к справочнику осуществляется только в целях внесения изменений, то закрытие формы будем осуществлять методом Unload Me:
Это позволит не "загружать" память приложения занимаемую формой.
▶️ Создание и редактирование справочника
Чтобы придать приложению гибкости (и позволить пользователю вносить изменения) установим взаимосвязь между источником данных и формой справочника.
⏩ Добавление новой записи
Для кнопки "Добавить" пишем макрос:
📝 Краткое описание:
- Определяем рабочий лист ws, на котором нужно произвести запись данных.
- Выполняем проверку ошибок (пустое значение, дубликаты записи)
- Находим последнюю пустую строку в столбце A на листе ws.
- Записываем выбранное значение из ComboBox1 в последнюю пустую ячейку столбца A на листе ws.
- Добавляем выбранное значение в выпадающий список ComboBox1.
Теперь "Новая завись" будет добавлена, как в справочник, так и в выпадающий список:
В случае пустого значения ComboBox1 или при попытке ввода дубликатов пользователю будет выведено соответствующее сообщение:
⏩ Поиск и удаление записи
В данном случае применим цикл For
Для кнопки "Удалить" пишем макрос:
📝 Краткое описание:
- Получаем значение, которое нужно удалить, из ComboBox1.
- Находим последнюю заполненную строку в столбце A на листе Лист2.
- Перебираем каждую ячейку в столбце A, начиная со второй строки.
- Если значение в ячейке совпадает с тем, что нужно удалить, то оно удаляется из листа и из ComboBox1.
- Завершаем выполнение после первого совпадения (если нужно удалить только одно значение).
- Очищаем ComboBox1.value после удаления записи.
- Выводим сообщение пользователю с информацией об удаленной записи:
🔔 Обратите внимание! чтобы управлять записью для сообщения обратной связи задана глобальная переменная valueToDelete:
🚩 Подведем итог ...
С учетом рассмотренных выше изменений теперь приложение умеет:
- выполнять поиск зависимых данных;
- добавлять новые данные в справочник;
- удалять некорректные данные;
- формирует выпадающий список из актуальных данных справочника на главной форме.
А о том, как создать связи между двумя ComboBox и настроить замену с использованием справочника обсудим в следующем обзоре 😉.
📍 Рекомендуемые статьи 🔽
