Oc-windows.ru

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

Vba excel элементы управления формы

VBA-Урок 12.2. Элементы управления (Controls)

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

Давайте начнем с добавления 3-х элементов управления — Label , TextBox и CommandButton :

Давайте сейчас отредактируем название и свойства этого элемента управления (используя свойство Caption , которое содержит текст). Мы хотим получить следующий результат:

Сейчас, когда мы введем номер и нажмем ОК — ничего не произойдет.

Чтобы создать какое-то событие, мы начнем добавлять событие, которое введет значение из текстового окна в ячейки А1 и закроет Форму.

Вы можете получить доступ к опциям, которые показаны ниже, дважды щелкнув по элементу управления:

Данный выпадающий список содержит различные элементы управления и эту Форму.

Выберите кнопку (Button) и событие Click:

Введенное значение сейчас уже будет сохранено в ячейке А1 перед закрытием этой Формы ( UserForm ).

Добавьте вторую подпись (Label) и измените следующие свойства: Caption , Forecolor (color: red) and Visible (False, чтобы скрыть элемент управления по умолчанию) :

Теперь давайте добавим событие, которое будет запускаться, когда значение в поле будет изменено. Событие отобразит сообщение об ошибке, если значение не является цифровым.

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

Нам еще нужно предотвратить валидацию формы, если значение не является числом.

Для того, чтобы не оставлять правую сторону формы пустой, когда там нет сообщения об ошибке, мы можем уменьшить её размер, откорректировав свойство Width этой формы:

Вы можете скачать Excel файл с примером: userform1.xls

В этом примере, чекбокс стартует неотмеченным, когда Форма открывается впервые.

Чтобы проверить бокс когда значения соответствующих ячеек является & quot; Отмечено & quot;, мы запустим проверку когда форму активировано, используя UserForm_Initialize:

Здесь вы можете посмотреть пример в готовом Excel файле: userform2.xls

Кнопки опций (Option Buttons)

Пользователь может выбрать только одну кнопку-опцию на «группу» в отличие от чекбоксов.

Чтобы создать «группу», сначала вставьте Frame, а затем OptionButton :

Здесь вы можете посмотреть пример в готовом Excel файле: userform3.xls

Как только форма была отправлена, мы введем данные в ячейку, которая связана с названием колонки и row_value chosen.

Для того, чтобы знать какие опции были выбраны, мы могли бы сделать то же самое что и в предыдущем примере (с чекбоксами), но мы используем цикл, чтобы уменьшить размер кода.

Мы собираемся использовать цикл For Each , тип цикла, который мы еще до этого не рассматривали. Этот цикл позволяет выполнять инструкции для каждого объекта в «группе объектов»:

Сейчас эта форма вводит значение «Ячейка выбрана!» в ячейку, которая была выбрана.

Чтобы избежать баги, нам необходимо проверить , что пользователь выбрал правильно с двух наборов опций.

В этом примере, когда форма еще не завершена, кнопка «Подтверждение» («Confirm») появится в сером (будет деактивирована). Это не самое простое решение, но это хороший пример того, почему функции/процедуры является Полезные внутри формы (UserForm).

Измените текст и свойство Enabled , чтобы деактивировать кнопку.

Результат будет следующий:

В предыдущем коде мы использовали два For Each цикла, чтобы получить значение опционных кнопок (option buttons). Сейчас нам нужно использовать те же значения для кнопки «Подтверждение» («Confirm») и событие Click для десяти опций.

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

Начнем с предыдущего кода и модифицируя его, мы достигнем этого результата:

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

Опять же, проверка выполняется в отдельной процедуре, чтобы избежать копирования кода 10 раз для каждого события каждого опционной кнопки:

Общие сведения о формах, элементах управления форм и элементах ActiveX на листе

Важно: Это средство недоступно в Office на компьютерах под управлением Windows RT. Хотите просмотреть версию Office, которую вы используете?

с помощью форм и элементов управления и объектов, которые можно добавлять в них, вы можете упростить ввод данных на листе и улучшить внешний вид листов. Вы также можете делать это самостоятельно, и вам почти или совсем не потребуется код Microsoft Visual Basic для приложений (VBA).

Форма листа и шаблон Excel — это не одно и то же. Шаблон — это предварительно отформатированный файл, который служит отправной точкой для создания книги с требуемым оформлением. Форма содержит элементы управления, такие как поля и раскрывающиеся списки, упрощающие ввод и изменение данных для использующих ваш лист. Дополнительные сведения о шаблонах, которые вы можете скачать, можно найти в разделе шаблоны Excel.

Общие сведения о формах

Форма, печатаемая или доступная по сети, — это документ, разработанный с использованием стандартной структуры и формата, который упрощает сбор, систематизацию и редактирование данных.

Печатные формы содержат инструкции, форматирование, метки и пробелы для ввода и ввода данных. Для создания печатных форм можно использовать шаблоны Excel и Excel.

Формы в Интернете содержат те же функции, что и печатные формы. Кроме того, в веб-формах есть элементы управления. Элементы управления — это объекты, которые отображают данные или упрощают ввод и редактирование данных, выполнение действий или выделение. Как правило, элементы управления упрощают использование формы. Примерами часто используемых элементов управления являются списки, переключатели и кнопки. Элементы управления также могут запускать назначенные им макросы и реагировать на события, например щелчки мышью, путем выполнения кода Visual Basic для приложений (VBA).

Читать еще:  Как совместить ячейки в excel

Вы можете использовать Excel несколькими способами для создания печатных и электронных форм.

Типы форм Excel

В Excel можно создавать формы нескольких типов: формы данных, листы с элементами управления формы и ActiveX, а также пользовательские формы VBA. Каждый из этих типов формы можно использовать по отдельности или сочетать с другими типами для создания нужного решения.

Форма данных

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

Приложение Excel может автоматически создавать встроенные формы данных для диапазон или таблица. Такая форма представляет собой диалоговое окно, в котором все заголовки столбцов отображаются в виде подписей. Каждой подписи соответствует текстовое поле, в которое можно вводить данные для столбца (максимальное количество столбцов — 32). В форме данных вы можете вводить новые строки, искать строки по переходу или (в зависимости от содержимого ячейки) обновлять строки и удалять строки. Если ячейка содержит формула, ее результат отображается в форме данных, но саму формулу в форме данных изменить нельзя.

Лист с формой и элементами ActiveX

Лист — это тип формы, который позволяет вводить и просматривать данные в сетке, а также некоторые функции, подобные элементам управления, уже встроенные в листы Excel, такие как примечания и проверка данных. Ячейки похожи на текстовые поля, и их можно вводить и форматировать разными способами. Ячейки часто используются в качестве меток, а также при настройке высоты и ширины ячеек и объединении ячеек можно сделать так, чтобы лист находился как простая форма для ввода данных. Другие функции, такие как элементы управления, такие как примечания к ячейкам, гиперссылки, фоновые изображения, проверка данных, условное форматирование, внедренные диаграммы и Автофильтр, могут вести себя как дополнительные формы.

Для повышения гибкости можно добавлять элементы управления и другие графические объекты в полотно листа, а также комбинировать и координировать их с помощью ячеек листа. Например, можно использовать элемент управления «список», чтобы облегчить пользователю выбирать элементы из списка. Кроме того, можно использовать элемент управления «Счетчик» для упрощения ввода номера пользователем.

Элементы управления и объекты хранятся на полотне, и поэтому их можно отображать и просматривать одновременно со связанным текстом, не зависящим от границ строк и столбцов, без изменения макета сетки или таблицы данных на листе. В большинстве случаев многие из этих элементов управления можно также связать с ячейками на листе, а для их нормальной работы не требуется создавать код VBA. Можно задать свойства, определяющие, является ли элемент управления свободно перемещаемым или перемещается и изменяет размеры вместе с ячейкой. Например, может потребоваться, чтобы флажок перемещался вместе со связанной ячейкой при сортировке диапазона. С другой стороны, если нужно, чтобы список всегда отображался в одном и том же месте, нежелательно, чтобы он перемещался вместе с ячейкой.

В Excel поддерживаются два типа элементов управления: элементы управления форм и элементы ActiveX. В дополнение к этим наборам элементов управления можно также добавлять объекты из средств рисования, например автофигуры, объект WordArt, графических элементов SmartArt или текстовых полей.

В следующих разделах более подробно описываются элементы управления и средства рисования, а также работа с ними.

Элементы управления формы

Элементы управления формы появились в Excel раньше всего и поддерживаются в предыдущих выпусках Excel (начиная с версии 5.0). Их также можно использовать на листах макросов XLM.

Элементы управления формы используются, если нужно легко ссылаться на данные ячеек и взаимодействовать с ними без использования кода VBA, а при необходимости добавлять элементы управления на листы диаграмм. Например, после добавления элемента управления «список» на лист и связывания его с ячейкой можно вернуть числовое значение для текущего положения выбранного элемента в элементе управления. Затем вы можете использовать это числовое значение в сочетании с функцией индекс для выбора различных элементов списка.

С помощью элементов управления формы можно также выполнять макросы. Можно назначить элементу управления существующий макрос либо создать или записать новый. Когда пользователь формы щелкает элемент управления, запускается макрос.

Однако эти элементы управления невозможно добавить в пользовательские формы, которые используются для управления событиями или изменены для запуска веб-сценариев на веб-страницах.

Элементы управления и пользовательская форма

Элементы управления

VBA обладает встроенным набором элементов управления. Используя этот набор и редактор форм не трудно создать любой пользовательский интерфейс, который будет удовлетворять всем требованиям, предъявляемым к интерфейсу в среде Windows. В данной главе дан обзор встроенных элементов управления VBA. Элементы управления являются объектами. Поэтому, как любые объекты, они обладают свойствами, методами и событиями. Элементы управления создаются при помощи панели инструментов Элементы управления (Control Toolbox) (рис. 7.1). На этой панели представлены кнопки, позволяющие конструировать элементы управления, а также кнопки вызова окна свойств, перехода в режим конструктора и редактор кода.

Читать еще:  Рабочий лист ms excel состоит из

Рис. 7.1. Панель инструментов Элементы управления

Создание элементов управления на рабочем листе или в форме как правило происходит на начальном этапе конструирования приложения. Иногда используется программное их создание в процессе работы приложения. Но этот подход применяется реже.

Большинство элементов управления можно располагать как на рабочем листе, так и в форме. Но существуют такие элементы управления, как RefEdit, Набор страниц и Набор вкладок, которые можно располагать только в форме. В табл. 7.1 приведен список основных элементов управления и соответствующих кнопок панели инструментов Элементы управления (Control Toolbox).

Таблица 7.1. Панель инструментов Элементы управления

Кнопка для создания элемента

Поле со списком

Для размещения элемента управления на листе или в форме нажмите соответствующую кнопку панели инструментов Элементы управления (Control Toolbox) и с помощью мыши перетащите рамку элемента управления в нужное место. После этого элемент управления можно перемещать, изменять его размеры, копировать в буфер обмена и вставлять из буфера обмена.

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

Режим конструктора (Design Mode) панели инструментов Элементы управления (Control Toolbox). В режиме конструктора отключена реакция элемента управления на события. Поэтому при включенном режиме конструктора можно видоизменять элемент управления и задавать его свойства. На рис. 7.2 показано создание элемента управления CommandButton (кнопка) на рабочем листе. После того как пользователь решит, что созданный элемент управления имеет тот вид, который ему нужен, и все требуемые свойства элемента управления установлены, он должен отключить режим конструктора повторным нажатием кнопки Режим конструктора (Design Mode).

Рис. 7.2. Создание элемента управления CommandButton на рабочем листе в режиме конструктора

Установка свойств элемента управления

Для установки свойств элемента управления вручную при его конструировании необходимо выделить элемент управления и нажать кнопку Свойства (Properties) панели инструментов Элементы управления (Control

ТоoolBох). На экране отобразится диалоговое окно Свойства (Properties) (рис. 7.3). Оно аналогично окну Свойства (Properties) редактора Visual Basic. Вручную при помощи этого окна обычно устанавливается свойство Caption, возвращающее отображаемый текст элемента управления. На рис. 7.3 значение свойства Caption элемента управление CommandButton изменено с установленного ПО умолчанию (CommandButtonl) на текст Нажми меня. Кроме

того, внесены изменения в параметры свойства Font, устанавливающее шрифт выводимого на поверхности кнопки текста, а именно, увеличен размер шрифта с 8 до 12, шрифт MS sans serif заменен на courier New и изменено начертание букв с обычного на полужирное. Также свойство shadow установлено равным True, вместо False, как это имеет место по умолчанию. Свойство shadow устанавливает, отображается ли элемент управления с тенью или без нее.

Рис. 7.3. Диалоговое окно Свойства и элемент управления CommandButton с измененными свойствами Caption, Font и Shadow

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

Элементы управления формы в EXCEL

Элементы управления формы (Поле со списком, Флажок, Счетчик и др.) помогают быстро менять данные на листе в определенном диапазоне, включать и выключать опции, делать выбор и пр. В принципе, без них можно обойтись, но они делают управление данными на листе более наглядным и уменьшают вероятность ввода некорректных данных.

Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик.

  • В MS EXCEL 2007 это можно сделать через меню Кнопка офис/ Параметры Excel/ Основные/ Показывать вкладку Разработчик на ленте .
  • В MS EXCEL 2010 это можно сделать так: Откройте вкладку Файл ; Нажмите кнопку Параметры ; Нажмите кнопку Настроить ленту ; Выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик .

Теперь вставить элемент управления можно через меню: Разработчик/ Элементы управления/ Вставить .

Обратите внимание, что в этом меню можно вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список, Флажок и т.п. Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.

Для тех, кто не ранее не работал с Элементами управления формы, советуем подробно ознакомиться с ними в следующих статьях:

В этой статье рассмотрим более сложный пример совместного использования элементов управления и Условного форматирования .

Пример

Разберем конкретный пример применения сразу нескольких Элементов управления. В файле примера с помощью элементов управления показано как пользователь может отредактировать значения в таблице (диапазон F9:K12 ).

С помощью одного из 3-х элементов управления Поле со списком, Список и Счетчик , пользователь может выбрать столбец таблицы (год) . Нужный элемент управления выбирается с помощью группы Переключателей . Название выбранного элемента подсвечивается серым цветом (см. A8:B8 на рис. выше). Выбранный год выделяется в таблице Условным форматированием темно серым цветом (см. H9 :H12 на рис. выше). Отображение этого выделения регулируется Флажком (фон флажка — красный). Полосами прокрутки можно редактировать Цену и Количество в выбранном году, но только в определенном диапазоне. Теперь – подробнее.

Читать еще:  Защита выделенных ячеек в excel

Переключатели

На листе использовано 3 Переключателя объединенных в Группу . Каждому Переключателю соответствует определенный элемент управления: Поле со списком, Список, Счетчик .

Для объединения Элементов в группу помещаем на лист Элемент управления Группа (через меню Разработчик/ Элементы управления/ Вставить ). В рамках группы создаем 3 переключателя (также через меню Разработчик/ Элементы управления/ Вставить ) и связываем их все с одной ячейкой С2 (выделив элемент управления, правой клавишей вызываем контекстное меню, Формат объекта …, вкладка Элемент управления ).

Удерживая клавишу CTRL выделяем 3 переключателя и элемент Группа , вызываем правой клавишей мыши контекстное меню и выбираем Группировка/ Группировать . Теперь при выборе одного из трех Переключателей в Группе , в ячейке С2 будет выводиться значение 1, 2 или 3.

Поле со списком

Теперь вставим элемент управления Поле со списком . Вставить элемент можно через меню: Разработчик/ Элементы управления/ Вставить . В ячейках столбца М введем несколько значений лет: 2009, 2010, 2011, 2012, 2013. Эти значения будут использованы в элементе Поле со списком .

  • выделяем диапазон М9:М12;
  • нажимаем Формулы/ Определенные имена/ Присвоить имя ;
  • в поле Имя вводим Список .

Теперь свяжем элемент управления с данными на листе. Для этого:

  • выделите элемент управления Поле со списком ;
  • правой клавишей вызовите его контекстное меню, затем Формат объекта… , вкладка Элемент управления ;
  • в поле Формировать список по диапазону вводим Список (вместо ссылки на ячейку мы ввели ссылку на определенное Имя !). Конечно, вместо имени можно было указать просто ссылку на диапазон;
  • свяжем элемент с ячейкой $C$8 . В этой ячейке будет выводится порядковый номер выбранного элемента списка, т.е. если выберем 2009 , то выведется 1, т.к. это первый элемент в списке. Для дальнейших целей нам проще использовать именно год, а не его порядковый номер. Для этого в ячейку D8 введем формулу =C8+2008 .

Список

Вставляем на лист элемент Список . Аналогично предыдущему элементу связываем его с ячейкой $C$13 и формируем список на основе того же Именованного диапазона Список . В ячейку D13 введем формулу =C13+2008 .

Счётчик

Вставляем на лист элемент Счетчик . Определяем минимальное значение 2009 , максимальное – 2013 , шаг 1 . Связываем элемент с ячейкой $C$17 . В D17 введем формулу =С17 , т.к. элемент Счетчик в нашем случае возвращает значение года.

Чтобы определить значение какого элемента ( поле со списком, список или счетчик ) является активным в настоящий момент, в ячейке E9 введем формулу: =ЕСЛИ(C2=1;D8;ЕСЛИ(C2=2;D13;D17)) Как мы помним, значение в ячейке С2 определяется Группой переключателей .

Полоса прокрутки

Вставляем на лист элемент Полоса прокрутки . Этим элементом мы будем изменять ячейку на пересечении строки Количество (строка 10) и столбца выбранного года. Значения ячейки будет меняться в диапазоне от 0 до 1000. Но как определить эту ячейку?

Создадим Именованную формулу СмещГода для определения позиции выбранного года в диапазоне лет G 9: K 9 . Нажимаем Формулы/ Определенные имена/ Присвоить имя , в поле Имя вводим СмещГода , в поле диапазон вводим формулу =ПОИСКПОЗ($E$9; $G$9:$K$9;0) Если выбран 2009 , то формула вернет 1.

Для определения ячейки строки Количество , соответствующую выбранному году используем формулу =СМЕЩ($F$10;0;СмещГода) . Формула вернет диапазон, состоящий из одной ячейки.

В поле Связь с ячейкой элемента Полоса прокрутки нельзя ввести формулу, но можно, как мы уже видели, ввести Имя . Создадим Именованную формулу Количество , в поле Диапазон укажем формулу =СМЕЩ($F$10;0;СмещГода) . Теперь в поле Связь с ячейкой элемента полоса прокрутки введите Количество .

Аналогичные манипуляции проделайте с полосой прокрутки для Цены . Для этого необходимо создать Именованную формулу Цена , где в поле Диапазон указать формулу =СМЕЩ($F$11;0;СмещГода) .

Флажок

При выборе пользователем текущего года, в таблице с данными ( G9:K12 ) соответствующий столбец будет закрашиваться серым фоном. Для выделения столбца выбранного года используем Условное форматирование .

Сначала вставим на лист элемент Флажок . Этим элементом мы будем включать и выключать выделение в таблице столбца выбранного года. Элемент свяжите с ячейкой $G$2 . Если флажок снят, то в этой ячейке будет ЛОЖЬ (этому значению соответствует 0), если установлен, то ИСТИНА (этому значению соответствует 1).

Для настройки Условного форматирования выделим диапазон G9:K12 . Так как формула в Условном форматировании будет содержать относительную ссылку , то убедимся, что после выделения диапазона активной ячейкой является G9 (т.е. диапазон надо выделять начиная именно с нее. Подсказкой служит поле Имя , находящееся слева от Строки формул . После выделения диапазона оно должно содержать G 9 ).

  • вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило );
  • выберите Использовать формулу для определения форматируемых ячеек;
  • в поле « Форматировать значения, для которых следующая формула является истинной » введите =И(СТОЛБЕЦ(G9)=СмещГода+6;$G$2) Формула примет значение ИСТИНА, когда выполнится одновременно 2 условия:
  • значение выражения ( СмещГода (изменяется от 1 до 5 (т.е. от 2009 до 2013 года) + 6) совпадет с номером текущего столбца (7, т.е. 2009 год);
  • Флажок Условное форматирование установлен.
  • выберите требуемый формат, например, серый цвет заливки;
  • нажмите ОК.

Тестируем

  • убедимся, что флажок Условное форматирование установлен;
  • выберем переключатель Список ;
  • в элементе управления Список выберем 2010;
  • убедимся, что столбец 2010 выделен серым;
  • Полосой прокрутки изменим количество в столбце 2010.

Результат показан на рисунке.

К сожалению, у элементов управления формы Флажок, Поле со списком и Список нет возможности отформатировать отображаемый шрифт. Зато это можно сделать у элементов ActiveX ( Разработчик/ Элементы управления/ Вставить ). Правда, для работы с этими элементами требуется писать программу на VBA.

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