Пользовательские формы в Excel Разработка VBA-приложений

Приложение Табель успеваемости

Удобный способ хранения информации

Общие требования к приложению I.Удобный способ хранения информации II.Возможность быстрого ввода оценок по всем предметам и возможность просмотра всех оценок за отдельный день III.Возможность просмотра всех оценок и среднего балла по всем предметам IV.Создание графического пользовательского интерфейса (создание своего меню)

I.Этапы создания рабочей книги 1.Создание рабочего листа с именем предмета (Русский язык) 2.Создание заголовков таблицы на рабочем листе Дата Оценка Примечание «Вес» оценки 3.Задание форматов для каждого столбца таблицы 4.Многократным копированием создаем определенное количество листов

Табель успеваемости

Табель успеваемости формат ячеек

Табель успеваемости создание копии листа Вызвать контекстное меню листа и выбрать Переместить/скопировать В диалоговом окне выбрать (переместить в конец) И установить флажок «Создать копию»

Табель успеваемости новый лист

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

Табель успеваемости задание имени для нового листа 1.Вводим новое имя «Литература»

Создание оставшихся листов 1.Алгебра 2.Геометрия 3.Физика 4.Химия 5.Биология 6.Информатика 7.Английский язык 8.Физкультура Многократно повторяя операцию копирования листа, создаем следующие рабочие листы:

Вид формы

II.Вход в интегрированную среду разработки VBA

Добавление новой формы в проект

Созданная форма (frmВвод)

Размещение элементов управления в форме 1.Добавить ЭУ Microsoft Date and Time Picker Control на панель инструментов, через контекстное меню, командой: Additional Controls…

Размещение элементов управления в форме Установить флажок напротив пункта: – Microsoft Date and Time Picker Control

Этапы создания ЭУ Создать календарь (DTPicker) Создать две надписи (Label) Создать текстовое поле (TextBox) Создать рамку (Frame) Внутри рамки разместить 3 переключателя (OptionButton)

Этапы создания ЭУ

Свойство Tag ЭУ TextBox Значение данного свойства равно имени соответствующего листа Пример для txtРусс – Русский язык

Создание всех ЭУ 1.Выделить созданные ЭУ 2.Скопировать их 3.Вставить из буфера обмена и задать новые имена и значения свойств Имена должны различаться только в русскоязычной части Русскоязычная часть состоит из первых 4-х букв названия рабочего листа (Русс, Лите, Алге, и т.д.)

Вид формы

Размещение кнопок на форме Всего 4 кнопки: – Просмотр (cmdПросмотр) – Ввод (cmdВвод) – Очистить (cmdОчистить) – Выход (cmdВыход) Выбрать ЭУ CommandButton Разместить в левом нижнем углу формы Задать имя и надпись Остальные 3 создаются по аналогии

Вид формы

Алгоритм проверки текстовых полей формы (кнопка Ввод) 1.Опросить 1 ЭУ из семейства ЭУ 2.Если это текстовое поле, то 1.strОценка=TRIM(Имя_Поля.Text) 2.Если strОценка «», то 1.Определить имя соответствующего листа (Tag) 2.Определить кол-во занятых строк начиная с ячейки А1 3.В первую свободную ячейку столбца А вставить дату 4.В первую свободную ячейку столбца В - оценку 3.Конец если 4.Перейти к пункту 1

Оператор выполнения группы инструкций для каждого элемента семейства For Each Элемент In Группа [инструкция] [Exit For] [инструкция] Next [Элемент]

Код программы Private Sub cmdВвод_Click() Dim txt As Object For Each txt In Controls If txt.Tag "" Then strОценка = Trim(txt.Text) If strОценка "" Then strИмяЛиста = txt.Tag Worksheets(strИмяЛиста).Select intКолСтрок = Worksheets(strИмяЛиста).Range("A1").CurrentRegion.Rows.Count 'определяем кол-во заполненных строк по ячейке 'содержащей дату начиная с ячейки A1 Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 1).Value = dtpДата.Value Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 2).Value = strОценка Код определения состояния переключателей End If Next txt

Определение типа урока и «веса» оценки strИмя1 = "opt1" & Mid(strИмяЛиста, 1, 4) strИмя2 = "opt2" & Mid(strИмяЛиста, 1, 4) strИмя3 = "opt3" & Mid(strИмяЛиста, 1, 4) If Me.Controls(strИмя1).Value Then strВес = "1" strТип = "обычный" ElseIf Me.Controls(strИмя2).Value Then strВес = "1.5" strТип = "самостоятельная работа" ElseIf Me.Controls(strИмя3).Value Then strВес = "2" strТип = "контрольная работа" End If Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 3).Value = strТип Worksheets(strИмяЛиста).Cells(intКолСтрок + 1, 4).Value = strВес

Код программы для кнопки Очистить Private Sub cmdОчистить_Click() Dim txt As Object For Each txt In Controls If txt.Tag "" Then txt.Text = "" End If Next txt End Sub

Код программы для кнопки Выход Private Sub cmdВыход_Click() End End Sub

Код программы для кнопки Просмотр Private Sub cmdПросмотр_Click() Me.Hide frmПросмотр.Show End Sub

Форма Просмотр (frmПросмотр)

Код формы Private Sub UserForm_Initialize() Dim strВсеОценки As String Dim intСуммОценки As Integer Dim sngСуммБалл As Single

Код формы For i = 1 To Worksheets.Count Worksheets(i).Select strИмяЛиста = ActiveSheet.Name intКолСтрок = Worksheets(i).Range("A1").CurrentRegion.Rows.Count strИмяПоля = "txt" & Mid(strИмяЛиста, 1, 4) strИмяПоля1 = strИмяПоля & "Ср" For j = 2 To intКолСтрок intСуммОценки = intСуммОценки + (Cells(j, 2).Value * Cells(j, 4)) sngСуммБалл = sngСуммБалл + Cells(j, 4).Value strВсеОценки = strВсеОценки & " " & Cells(j, 2).Value Next j Me.Controls(strИмяПоля).Text = Trim(strВсеОценки)

Код формы If sngСуммБалл 0 Then Me.Controls(strИмяПоля1).Text = CStr(Format((intСуммОценки) / (sngСуммБалл), "fixed")) Else Me.Controls(strИмяПоля1).Text = CStr(Format(0, "fixed")) End If strВсеОценки = "" intСуммОценки = 0 sngСуммБалл = 0 Next i End Sub

Код кнопок Ввод и Выход Private Sub cmdВвод_Click() Me.Hide frmВвод.Show End Sub Private Sub cmdВыход_Click() End End Sub

III.Создание собственного меню Private Sub Workbook_Open() Dim MyBar As CommandBar Dim MyButton(1 To 2) As CommandBarButton With Application.DisplayFormulaBar = False.CommandBars("Formatting").Visible = False.CommandBars("Standard").Visible = False End With

III.Создание собственного меню 'Создание своей панели Set MyBar = Application.CommandBars.Add(Name:="Моя панель", _ Position:=msoBarTop, MenuBar:=False, Temporary:=True) 'Создание своих кнопок With MyBar.Visible = True.Protection = msoBarNoMove + msoBarNoChangeVisible _ + msoBarNoCustomize With.Controls Set MyButton(1) =.Add(Type:=msoControlButton, ID:=1, Temporary:=True) Set MyButton(2) =.Add(Type:=msoControlButton, ID:=1, Temporary:=True) End With

III.Создание собственного меню 'Установка свойств кнопок With MyButton(1).Caption = "Просмотр".TooltipText = "Просмотр всех оценок".Style = msoButtonCaption.OnAction = "Просмотр" End With With MyButton(2).Caption = "Ввод".TooltipText = "Ввод оценок".Style = msoButtonCaption.OnAction = "Ввод" End With

III.Удаление собственного меню при закрытии книги Private Sub Workbook_BeforeClose(Cancel As Boolean) With Application.DisplayFormulaBar = True.CommandBars("Formatting").Visible = True.CommandBars("Standard").Visible = True End With 'Удаление созданной панели Dim bar As CommandBar For Each bar In Me.Application.CommandBars If Not bar.BuiltIn Then bar.Delete Next End Sub

Стандартный модуль Sub Ввод() frmВвод.Show End Sub Sub Просмотр() frmПросмотр.Show End Sub

Для самостоятельной работы Описать событие Change() для ЭУ Microsoft Date and Picker Control, так чтобы при изменение даты выводились все оценки по предметам за этот день. Подсказка. Данное событие противоположно событию Click() для кнопки Ввод формы «Ввод оценок»