Разработка экранных форм
Следующим шагом в построении нашего приложения будет разработка экранных форм и программного кода, поддерживающего их функционирование. Использование экранных форм дает значительные преимущества. Работая с формой, пользователь не может нарушить целостность данных, изменить структуру таблиц. Также при использовании форм возможна, интерактивная проверка введенных пользователем, можно организовать проверку условий любой сложности. Применение специальных элементов управления позволяет упростить ввод данных.
Добавим форму «Отпускные цены», служащую для изменения текущих отпускных цен на топлива. Разместите элементы управления как показано на рисунке ниже. При нажатии на кнопку «Изменить», прежде всего, надо организовать проверку введенных пользователем данных, затем сохранить их в диапазоне rgFuelPrice.
Рис. ХХ. Экранная форма «Отпускные цены»
Private Sub cmdChange_Click()
If Not IsNumeric(txtPrice76.Text) Then
SetFocusToError txtPrice76
Exit Sub
End If
If Not IsNumeric(txtPrice92.Text) Then
SetFocusToError txtPrice92
Exit Sub
End If
If Not IsNumeric(txtPrice95.Text) Then
SetFocusToError txtPrice95
Exit Sub
End If
If Not IsNumeric(txtPriceDT.Text) Then
SetFocusToError txtPriceDT
Exit Sub
End If
With Worksheets("Главный").Range("rgFuelPrice")
.Cells(1, 1).Value = CDbl(txtPrice76.Text)
.Cells(2, 1).Value = CDbl(txtPrice92.Text)
.Cells(3, 1).Value = CDbl(txtPrice95.Text)
.Cells(4, 1).Value = CDbl(txtPriceDT.Text)
End With
End Sub
Public Sub SetFocusToError(ctrl As Control)
ctrl.SelStart = 0
ctrl.SelLength = Len(ctrl.Text)
ctrl.SetFocus
End Sub
С помощью встроенной функции IsNumeric мы производим проверку, можно ли рассматривать введенной значение как число. Если нет, то производится вызов специально разработанной функции SetFocusToError, которая устанавливает фокус на элемент управления с неверным значением. Также в код необходимо добавить вывод на экран сообщения об ошибке c помощью функции MsgBox. При проверке всех значений данные сохраняются на рабочем листе в требуемом диапазоне.
Добавим еще один обработчик события для данной формы: при открытии формы необходимо загрузить текущие значения цен в элементы управления.
Private Sub UserForm_Initialize()
With Worksheets("Главный").Range("rgFuelPrice")
txtPrice76.Text = .Cells(1, 1).Text
txtPrice92.Text = .Cells(2, 1).Text
txtPrice95.Text = .Cells(3, 1).Text
txtPriceDT.Text = .Cells(4, 1).Text
End With
End Sub
Подобным образом строятся остальные формы изменения данных на рабочих листах, основные действия, которые необходимо реализовать на форме: загрузка текущих данных, контроль данных и сохранение новых данных.
Экранная форма «Продажа» вызывается при нажатии на одну из восьми кнопок. За каждой их колонок закреплен определенный вид топлива. При продаже топлива должна измениться информация в таблице «Резервуары», находящаяся на втором листе. С данной таблицей связана диаграмма, таким образом, оператор сможет визуально контролировать объем топлива, находящегося резервуаров. Форма позволяет ввести необходимое клиенту количество либо в литрах, в таком случае автоматически вычисляется цена, либо указать сумму, на которую он совершает покупку, в таком случае количество литров также вычисляется автоматически. Перед продажей необходимо проверить условие: есть ли в резервуаре необходимое количество топлива.
Рис. ХХ. Экранная форма «Продажа»
На форме «Получение бензина», показанной ниже, располагаются выпадающий список и текстовое поле. При доставке бензина соответствующего типа обновляется информация в таблице «Резервуары». При получении топливо необходимо проверить, есть ли в резервуаре достаточно свободного места.
Рис. ХХ. Экранная форма «Получение бензина»
Разработаем экранные формы для работы с информацией о сотрудниках.
Форма «Персонал» содержит элемент управления ListBox и четыре командные кнопки. Для того, чтобы список отображал несколько колонок необходимо изменить свойство ColumnCount, присвоим данному свойству значение 7. Для настройки ширины столбцов используется свойство ColumnWidths, в нашем случае установим значение «49.95 pt;35 pt;49.95 pt;45 pt;67.95 pt;60 pt;40 pt». Для отображения заголовков столбцов необходимо установить свойство ColumnHeads в True.
Рис. ХХ. Экранная форма «Персонал»
Команды «Добавить», «Изменить» и «Удалить» используются для изменения данных на листе «Персонал». Таким образом, нам необходимо программно добавлять, редактировать и удалять строки таблицы. Программный код формы представлен ниже.
Public Sub UpdateListRowSource()
Dim ws As Worksheet
Set ws = Worksheets("Персонал")
lstEmloyees.RowSource = "'Персонал'!A2:G" + _
CStr(ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
End Sub
Private Sub cmdAdd_Click()
frmEmployee.Show
If frmEmployee.Tag = "OK" Then
Dim NewRow As Integer
Dim r As Range, ws As Worksheet
Set ws = Worksheets("Персонал")
Set r = ws.UsedRange.SpecialCells(xlCellTypeLastCell)
NewRow = r.Row + 1
SaveDataToRow NewRow
UpdateListRowSource
End If
End Sub
Private Sub SaveDataToRow(RowIndex As Integer)
With frmEmployee
Dim ws As Worksheet
Set ws = Worksheets("Персонал")
ws.Cells(RowIndex, 1).Value = .txtLastname.Text
ws.Cells(RowIndex, 2).Value = .txtName.Text
ws.Cells(RowIndex, 3).Value = .txtPatronymic.Text
ws.Cells(RowIndex, 4).Value = .cmbSex.Text
ws.Cells(RowIndex, 5).Value = .dtpDateOfBirth.Value
ws.Cells(RowIndex, 6).Value = .cmbAppointment.Text
ws.Cells(RowIndex, 7).Value = .txtGrade.Text
End With
End Sub
Private Sub cmdClose_Click()
Me.Hide
End Sub
Private Sub cmdEdit_Click()
Dim RowIndex As Integer, ws As Worksheet
RowIndex = lstEmloyees.ListIndex + 2
Set ws = Worksheets("Персонал")
With frmEmployee
.txtLastname.Text = ws.Cells(RowIndex, 1).Value
.txtName.Text = ws.Cells(RowIndex, 2).Value
.txtPatronymic.Text = ws.Cells(RowIndex, 3).Value
.cmbSex.Text = ws.Cells(RowIndex, 4).Value
.dtpDateOfBirth.Value = ws.Cells(RowIndex, 5).Value
.cmbAppointment.Text = ws.Cells(RowIndex, 6).Value
.txtGrade.Text = ws.Cells(RowIndex, 7).Value
.Show vbModal
End With
SaveDataToRow RowIndex
End Sub
Private Sub cmdDelete_Click()
If lstEmloyees.ListIndex <> -1 Then
Dim RowIndex As Integer, ws As Worksheet
RowIndex = lstEmloyees.ListIndex + 2
Set ws = Worksheets("Персонал")
ws.Rows(RowIndex).Delete
UpdateListRowSource
End If
End Sub
Private Sub UserForm_Initialize()
UpdateListRowSource
End Sub
Процедура UpdateListRowSource используется для обновления свойства RowSource списка, при добавлении и удалении строк адрес связанного диапазона необходимо обновить. Строка
ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row
используется для быстрого поиска первой пустой строки. Для поиска первой пустой строки можно применить и другие методы, например, просмотреть все строки, начиная с первой, пока не будет найдена первая пустая строка. Однако, при больших объемах данных па поиск строки будет тратиться продолжительное время.
При добавлении новой строки отображается форма для ввода данных, затем с помощью рассмотренного выше метода находится первая пустая строка и с помощью специально разработанной процедуры SaveDataToRow данные с формы переносятся в таблицу. После добавления строки обновляется свойство RowSource списка, и в нем появляются данные о новом работнике.
Индекс редактируемой строки в таблице определяется с помощью индекса выделенного в данный момент элемента списка, после чего данные загружаются в форму. После изменения данных обновление свойства RowSource списка не требуется, т.к. адрес диапазона не изменился. Список на форме будет обновлен автоматически.
При удалении строки с помощью метода Delete из коллекции Rows удаляется строка с указанным индексом. После чего необходимо изменить свойство RowSource списка.
Добавьте новую форму «Работник». Разместите на ней три текстовых поля для фамилии, имени и отчества работника. Для выбора пола и должности работника добавьте два списка. Первый список мы программно заполним данными в обработчике события инициализации формы. Свойству RowSource второго списка присвойте адрес диапазона, содержащий список должностей (например, 'Данные'!B3:B6).
Для ввода даты будем использовать специальный элемент управления Microsoft Date and Time Picker Control. Данный элемент управления предлагает пользователю удобный способ работы с датами. По умолчанию данного элемента нет на панели элементов управления (Toolbox), поэтому перед использованием его необходимо туда вынести. Для чего в контекстном меню панели элементов управления выберите пункт «Additional Controls…», затем в списке «Available Controls» выберите пункт Date and Time Picker Control 6.0. На панели элементов управления появится новая пиктограмма, соответствующая добавленному элементу управления.
Рис. ХХ. Экранная форма для создания/изменения данных сотрудника
Для выбора разряда работника будем использовать элемент управления SpinButton, позволяющий выбирать числовые значения из заданного диапазона с указанным шагом. Разместите на форме текстовое поле, а рядом с ним элемент управления SpinButton, установите свойство Min равным 1, а свойство Max - 5. Программный код формы представлен ниже.
Option Explicit
Public Mode As Integer
Private Sub cmdCancel_Click()
Me.Tag = "Cancel"
Me.Hide
End Sub
Private Sub spnGrade_Change()
txtGrade.Value = spnGrade.Value
End Sub
Private Sub UserForm_Initialize()
cmbSex.AddItem "Мужской"
cmbSex.AddItem "Женский"
cmbSex.ListIndex = 0
End Sub
Private Sub cmdSave_Click()
Me.Tag = "OK"
If Trim(txtLastname.Text) = "" Then
MsgBox "Ошибка при заполнении поля: Фамилия."
Me.Tag = "Cancel"
End If
If Trim(txtName.Text) = "" Then
MsgBox "Ошибка при заполнении поля: Имя."
Me.Tag = "Cancel"
End If
If Trim(txtPatronymic.Text) = "" Then
MsgBox "Ошибка при заполнении поля: Отчество."
Me.Tag = "Cancel"
End If
Me.Hide
End Sub
Public Sub SetFocusToError(ctrl As Control)
ctrl.SelStart = 0
ctrl.SelLength = Len(ctrl.Text)
ctrl.SetFocus
End Sub