Автошкола "Кадиллак"

Private Sub bt_add_Click()

On Error GoTo erin:

If ed_surname.Text <> "" And ed_name.Text <> "" And ed_patron.Text <> "" And ed_birth.Text <> "" And ed_str.Text <> "" And ed_home.Text <> "" And ed_room.Text <> "" And ed_who.Text <> "" And ed_date.Text <> "" And ed_ser.Text <> "" And ed_num.Text <> "" And Val(ed_home.Text) <> 0 And Val(ed_room.Text) <> 0 And Val(ed_ser.Text) <> 0 And Val(ed_num.Text) <> 0 And Val(ed_birth.Text) <> 0 And Val(ed_date.Text) <> 0 And ed_birth.Text = CDate(ed_birth.Text) And ed_date.Text = CDate(ed_date.Text) Then

Dim all As Integer

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

Selection.CurrentRegion.Select

all = Selection.CurrentRegion.Rows.Count

Sheets("База").Cells(all + 1, 1) = Val(Sheets("База").Cells(all, 1)) + 1

Sheets("База").Cells(all + 1, 2) = AddClientForm.ed_surname.Text

Sheets("База").Cells(all + 1, 3) = AddClientForm.ed_name.Text

Sheets("База").Cells(all + 1, 4) = AddClientForm.ed_patron.Text

Sheets("База").Cells(all + 1, 5) = CDate(AddClientForm.ed_birth.Text)

Sheets("База").Cells(all + 1, 6) = AddClientForm.ed_who.Text

Sheets("База").Cells(all + 1, 7) = CDate(AddClientForm.ed_date.Text)

Sheets("База").Cells(all + 1, 8) = AddClientForm.ed_ser.Text

Sheets("База").Cells(all + 1, 9) = AddClientForm.ed_num.Text

Sheets("База").Cells(all + 1, 10) = AddClientForm.ed_str.Text

Sheets("База").Cells(all + 1, 11) = AddClientForm.ed_home.Text

Sheets("База").Cells(all + 1, 12) = AddClientForm.ed_room.Text

Sheets("База").Cells(all + 1, 13) = AddClientForm.ed_phone.Text

Sheets("База").Cells(all + 1, 14) = AddClientForm.ed_mobile.Text

Sheets("База").Cells(all + 1, 15) = "Нет"

Sheets("База").Cells(all + 1, 16) = "Нет"

Sheets("База").Cells(all + 1, 17) = "Нет"

Sheets("База").Cells(all + 1, 18) = AddClientForm.cb_car.Value

Sheets("База").Cells(all + 1, 19) = AddClientForm.cb_teacher.Value

Sheets("База").Cells(all + 1, 20) = 0

Sheets("База").Cells(all + 1, 21) = 0

Sheets("База").Cells(all + 1, 23) = "Нет"

Sheets("База").Cells(all + 1, 24) = "Нет"

Sheets("База").Cells(all + 1, 25) = "Нет"

Sheets("База").Cells(all + 1, 26) = "Нет"

Sheets("База").Cells(all + 1, 27) = "Нет"

Sheets("База").Cells(all + 1, 28) = "Нет"

Sheets("База").Cells(all + 1, 29) = "Ожидает"

x = MsgBox("Клиент успешно занесен в базу данных", vbInformation + vbOKOnly, "Автошкола")

AddClientForm.Hide

ClientForm.Show (0)

Else

erin:

x = MsgBox("Проверьте правильность введеных значений", vbCritical + vbOKOnly, "Автошкола")

End If

End Sub

Кнопка “Закрыть”:

Private Sub bt_exit_Click()

AddClientForm.Hide

ClientForm.Show (0)

End Sub

Список “Автомобиль”:

Private Sub cb_car_Change()

Sheets("Данные").Activate

cb_teacher.Clear

For i = 2 To 10

If Sheets("Данные").Cells(i, 4) = cb_car.Value Then cb_teacher.AddItem Sheets("Данные").Cells(i, 3)

Next i

cb_teacher.ListIndex = 0

End Sub

Поле “Дом”:

Private Sub ed_home_Change()

ed_home.Text = Val(ed_home.Text)

End Sub

Поле “Сотовый”:

Private Sub ed_mobile_Change()

ed_mobile.Text = Val(ed_mobile.Text)

End Sub

Поле “Номер”:

Private Sub ed_num_Change()

ed_num.Text = Val(ed_num.Text)

End Sub

Поле “Дом. телефон”:

Private Sub ed_phone_Change()

ed_phone.Text = Val(ed_phone.Text)

End Sub

Поле “Квартира”:

Private Sub ed_room_Change()

ed_room.Text = Val(ed_room.Text)

End Sub

Поле “Серия”:

Private Sub ed_ser_Change()

ed_ser.Text = Val(ed_ser.Text)

End Sub

Активация формы:

Private Sub UserForm_Activate()

ed_surname.Text = ""

ed_name.Text = ""

ed_patron.Text = ""

ed_birth.Text = ""

ed_who.Text = ""

ed_date.Text = ""

ed_ser.Text = ""

ed_num.Text = ""

ed_str.Text = ""

ed_home.Text = ""

ed_room.Text = ""

ed_phone.Text = ""

ed_mobile.Text = ""

End Sub

Инициализация формы:

Private Sub UserForm_Initialize()

Sheets("Данные").Activate

cb_car.ListIndex = 0

cb_teacher.Clear

cb_teacher.AddItem Cells(4, 3)

cb_teacher.ListIndex = 0

End Sub

Завершение работы формы:

Private Sub UserForm_Terminate()

AddClientForm.Hide

ClientForm.Show (0)

End Sub

Private Sub bt_exit_Click()

PayForm.Hide

ClientForm.Show (0)

End Sub

Кнопка “Сформировать бланк”:

Private Sub bt_makeblank_Click()

If (ed_datepay.Text = CDate(ed_datepay.Text)) And cb_whopay.Text <> "" And ed_money.Value <> "" And ed_money.Value <> 0 Then

Sheets("Оплата").Activate

Sheets("Оплата").Range("B5").Value = cb_whopay.Value

Sheets("Оплата").Range("C8").Value = ed_datepay.Text

Sheets("Оплата").Range("C9").Value = ed_money.Text & " руб."

PayForm.Hide

Sheets("Оплата").Visible = True

Else

x = MsgBox("Проверьте правильность введеных значений", vbCritical + vbOKOnly, "Автошкола")

End If

End Sub

Кнопка “Подтвердить оплату”:

Private Sub bt_pay_Click()

If (ed_datepay.Text = CDate(ed_datepay.Text)) And cb_whopay.Text <> "" And ed_money.Value <> "" And ed_money.Value <> 0 Then

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If Sheets("База").Cells(i, 29) <> "Окончил" And (Sheets("База").Cells(i, 2) & " " & Sheets("База").Cells(i, 3) & " " & Sheets("База").Cells(i, 4)) = cb_whopay.Text Then Cells(i, 21) = Val(Cells(i, 21)) + ed_money.Value

Next i

y = MsgBox("Оплата внесена!", vbInformation + vbOKOnly, "Автошкола")

Else

x = MsgBox("Проверьте правильность введеных значений", vbCritical + vbOKOnly, "Автошкола")

End If

End Sub

Поле “Сумма платежа (руб.)”:

Private Sub ed_money_Change()

ed_money.Value = Val(ed_money.Value)

End Sub

Активация формы:

Private Sub UserForm_Activate()

cb_whopay.Clear

ed_money.Value = ""

Dim x As Integer

x = 0

ed_datepay.Text = Date

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If Sheets("База").Cells(i, 29) <> "Окончил" Then

cb_whopay.AddItem (Sheets("База").Cells(i, 2) & " " & Sheets("База").Cells(i, 3) & " " & Sheets("База").Cells(i, 4))

x = x + 1

End If

Next i

If x = 0 Then

y = MsgBox("Текущая группа пуста!", vbCritical + vbOKOnly, "Автошкола")

PayForm.Hide

ClientForm.Show (0)

Else

cb_whopay.ListIndex = 0

End If

End Sub

Завершение работы формы:

Private Sub UserForm_Terminate()

PayForm.Hide

ClientForm.Show (0)

End Sub

Sub endis()

level_1.Enabled = True

level_2.Enabled = True

level_3.Enabled = True

If cb_able.Value = True And cb_pdd.Value = True And cb_help.Value = True And Val(desc_howmuchdrive.Text) >= Sheets("Данные").Range("L4") And Val(desc_howmuchpay.Caption) >= Sheets("Данные").Range("L2") Then

level_1.Enabled = False

If cb_insidepdd.Value = True And cb_insidedrive.Value = True And cb_insidegorod.Value = True Then

level_2.Enabled = False

Else

level_3.Enabled = False

End If

Else

level_2.Enabled = False

level_3.Enabled = False

End If

End Sub

Кнопка “Выйти”:

Private Sub bt_exit_Click()

WayForm.Hide

ClientForm.Show (0)

End Sub

Кнопка “Сохранить”:

Private Sub bt_save_Click()

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If ComboBox1.Text = (Sheets("База").Cells(i, 2) & " " & Sheets("База").Cells(i, 3) & " " & Sheets("База").Cells(i, 4)) And Sheets("База").Cells(i, 29) = "Обучаемый" Then

If cb_able.Value = True Then Sheets("База").Cells(i, 17) = "Да" Else Sheets("База").Cells(i, 17) = "Нет"

If cb_pdd.Value = True Then Sheets("База").Cells(i, 15) = "Да" Else Sheets("База").Cells(i, 15) = "Нет"

If cb_help.Value = True Then Sheets("База").Cells(i, 16) = "Да" Else Sheets("База").Cells(i, 16) = "Нет"

If cb_insidepdd.Value = True Then Sheets("База").Cells(i, 23) = "Да" Else Sheets("База").Cells(i, 23) = "Нет"

If cb_insidedrive.Value = True Then Sheets("База").Cells(i, 24) = "Да" Else Sheets("База").Cells(i, 24) = "Нет"

If cb_insidegorod.Value = True Then Sheets("База").Cells(i, 25) = "Да" Else Sheets("База").Cells(i, 25) = "Нет"

If cb_gaipdd.Value = True Then Sheets("База").Cells(i, 26) = "Да" Else Sheets("База").Cells(i, 26) = "Нет"

If cb_gaidrive.Value = True Then Sheets("База").Cells(i, 27) = "Да" Else Sheets("База").Cells(i, 27) = "Нет"

If cb_gaigorod.Value = True Then Sheets("База").Cells(i, 28) = "Да" Else Sheets("База").Cells(i, 28) = "Нет"

Sheets("База").Cells(i, 20) = desc_howmuchdrive.Text

End If

Next i

End Sub

Элементы типа Checkbox:

Private Sub cb_able_Click()

Call endis

End Sub

Private Sub cb_help_Click()

Call endis

End Sub

Private Sub cb_insidedrive_Click()

Call endis

End Sub

Private Sub cb_insidegorod_Click()

Call endis

End Sub

Private Sub cb_insidepdd_Click()

Call endis

End Sub

Private Sub cb_pdd_Click()

Call endis

End Sub

Список “Клиент”:

Private Sub ComboBox1_Change()

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If ComboBox1.Text = (Sheets("База").Cells(i, 2) & " " & Sheets("База").Cells(i, 3) & " " & Sheets("База").Cells(i, 4)) And Sheets("База").Cells(i, 29) = "Обучаемый" Then

cb_able.Value = False

cb_pdd.Value = False

cb_help.Value = False

cb_insidepdd.Value = False

cb_insidedrive.Value = False

cb_insidegorod.Value = False

cb_gaipdd.Value = False

cb_gaidrive.Value = False

cb_gaigorod.Value = False

level_1.Enabled = True

level_2.Enabled = True

level_3.Enabled = True

If Sheets("База").Cells(i, 17) = "Да" Then cb_able.Value = True

If Sheets("База").Cells(i, 15) = "Да" Then cb_pdd.Value = True

If Sheets("База").Cells(i, 16) = "Да" Then cb_help.Value = True

If Sheets("База").Cells(i, 23) = "Да" Then cb_insidepdd.Value = True

If Sheets("База").Cells(i, 24) = "Да" Then cb_insidedrive.Value = True

If Sheets("База").Cells(i, 25) = "Да" Then cb_insidegorod.Value = True

If Sheets("База").Cells(i, 26) = "Да" Then cb_gaipdd.Value = True

If Sheets("База").Cells(i, 27) = "Да" Then cb_gaidrive.Value = True

If Sheets("База").Cells(i, 28) = "Да" Then cb_gaigorod.Value = True

desc_howmuchdrive.Text = Val(Cells(i, 20))

desc_howmuchpay.Caption = Val(Cells(i, 21))

Sheets("Данные").Activate

Call endis

End If

Next i

End Sub

Поле “Откатано часов”:

Private Sub desc_howmuchdrive_Change()

desc_howmuchdrive.Text = Val(desc_howmuchdrive.Text)

Call endis

End Sub

Активация формы:

Private Sub UserForm_Activate()

Dim first, hod As Integer

hod = 0

first = 0

ComboBox1.Clear

cb_able.Value = False

cb_pdd.Value = False

cb_help.Value = False

cb_insidepdd.Value = False

cb_insidedrive.Value = False

cb_insidegorod.Value = False

cb_gaipdd.Value = False

cb_gaidrive.Value = False

cb_gaigorod.Value = False

level_1.Enabled = True

level_2.Enabled = True

level_3.Enabled = True

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If Sheets("База").Cells(i, 29) = "Обучаемый" Then

hod = hod + 1

If hod = 1 Then first = i

ComboBox1.AddItem (Sheets("База").Cells(i, 2) & " " & Sheets("База").Cells(i, 3) & " " & Sheets("База").Cells(i, 4))

End If

Next i

If first <> 0 Then

i = first

If Sheets("База").Cells(i, 17) = "Да" Then cb_able.Value = True

If Sheets("База").Cells(i, 15) = "Да" Then cb_pdd.Value = True

If Sheets("База").Cells(i, 16) = "Да" Then cb_help.Value = True

If Sheets("База").Cells(i, 23) = "Да" Then cb_insidepdd.Value = True

If Sheets("База").Cells(i, 24) = "Да" Then cb_insidedrive.Value = True

If Sheets("База").Cells(i, 25) = "Да" Then cb_insidegorod.Value = True

If Sheets("База").Cells(i, 26) = "Да" Then cb_gaipdd.Value = True

If Sheets("База").Cells(i, 27) = "Да" Then cb_gaidrive.Value = True

If Sheets("База").Cells(i, 28) = "Да" Then cb_gaigorod.Value = True

desc_howmuchdrive.Text = Val(Sheets("База").Cells(i, 20))

desc_howmuchpay.Caption = Val(Sheets("База").Cells(i, 21))

Sheets("Данные").Activate

Call endis

ComboBox1.ListIndex = 0

Else

y = MsgBox("Текущая группа пуста!", vbCritical + vbOKOnly, "Автошкола")

WayForm.Hide

ClientForm.Show (0)

End If

End Sub

Завершение работы формы:

Private Sub UserForm_Terminate()

ClientForm.Show (0)

End Sub

Private Sub bt_ok_Click()

If ComboBox1.Text <> "" Then

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If ComboBox1.Text = (Sheets("База").Cells(i, 2) & " " & Sheets("База").Cells(i, 3) & " " & Sheets("База").Cells(i, 4)) Then

Sheets("Клиент-Отчет").Range("B3") = ComboBox1.Text

Sheets("Клиент-Отчет").Range("B4") = Sheets("База").Cells(i, 5)

Sheets("Клиент-Отчет").Range("B5") = "№ " & Sheets("База").Cells(i, 8) & " " & Sheets("База").Cells(i, 9) & " выдан " & Sheets("База").Cells(i, 6) & ", " & Sheets("База").Cells(i, 7)

Sheets("Клиент-Отчет").Range("B6") = "ул. " & Sheets("База").Cells(i, 10) & ", " & "дом " & Sheets("База").Cells(i, 11) & ", кв. " & Sheets("База").Cells(i, 12)

If Sheets("База").Cells(i, 13) <> "" And Sheets("База").Cells(i, 14) <> "" Then

Sheets("Клиент-Отчет").Range("B7") = Sheets("База").Cells(i, 13) & " ; " & Sheets("База").Cells(i, 14)

Else

Sheets("Клиент-Отчет").Range("B7") = Sheets("База").Cells(i, 13) & Sheets("База").Cells(i, 14)

End If

Sheets("Клиент-Отчет").Range("D3") = Sheets("База").Cells(i, 19)

Sheets("Клиент-Отчет").Range("D4") = Sheets("Данные").Range("H2")

Sheets("Клиент-Отчет").Range("D5") = Sheets("База").Cells(i, 18)

Sheets("Клиент-Отчет").Range("D6") = Sheets("База").Cells(i, 21)

Sheets("Клиент-Отчет").Range("D7") = Sheets("База").Cells(i, 20)

Sheets("Клиент-Отчет").Range("B9") = Sheets("База").Cells(i, 17)

Sheets("Клиент-Отчет").Range("B10") = Sheets("База").Cells(i, 15)

Sheets("Клиент-Отчет").Range("B11") = Sheets("База").Cells(i, 16)

Sheets("Клиент-Отчет").Range("D9") = Sheets("База").Cells(i, 23)

Sheets("Клиент-Отчет").Range("D10") = Sheets("База").Cells(i, 24)

Sheets("Клиент-Отчет").Range("D11") = Sheets("База").Cells(i, 25)

Sheets("Клиент-Отчет").Range("A14") = Sheets("База").Cells(i, 26)

Sheets("Клиент-Отчет").Range("C14") = Sheets("База").Cells(i, 27)

Sheets("Клиент-Отчет").Range("D14") = Sheets("База").Cells(i, 28)

End If

Next i

SelectForm.Hide

Sheets("Клиент-Отчет").Activate

Sheets("Клиент-Отчет").Visible = True

Else

x = MsgBox("Нет клиентов в этой категории!", vbCritical + vbOKOnly, "Автошкола")

End If

End Sub

Список “Статус”:

Private Sub ComboBox2_Change()

Dim hod As Integer

hod = 0

ComboBox1.Clear

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

status = ComboBox2.Text

For i = 2 To all

If Sheets("База").Cells(i, 29) = status Then

hod = hod + 1

If hod = 1 Then first = i

ComboBox1.AddItem (Sheets("База").Cells(i, 2) & " " & Sheets("База").Cells(i, 3) & " " & Sheets("База").Cells(i, 4))

End If

Next i

If hod <> 0 Then ComboBox1.ListIndex = 0

End Sub

Активизация формы:

Private Sub UserForm_Activate()

ComboBox1.Clear

ComboBox2.Clear

ComboBox2.AddItem ("Ожидает")

ComboBox2.AddItem ("Обучаемый")

ComboBox2.AddItem ("Окончил")

ComboBox2.ListIndex = 0

End Sub

Завершение работы формы:

Private Sub UserForm_Terminate()

ClientForm.Show (0)

End Sub

Кнопка “Быстрый отчет”:

Private Sub bt_filter_Click()

Dim ins1, ins2, ins3, gai1, gai2, gai3, fins, fgai As Integer

ins1 = 0

ins2 = 0

ins3 = 0

gai1 = 0

gai2 = 0

gai3 = 0

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

Selection.CurrentRegion.Select

all = Selection.CurrentRegion.Rows.Count

Sheets("База").Activate

Sheets("База").Cells(1, 36).Select

Selection.CurrentRegion.Select

Selection.Clear

Sheets("База").Cells(2, 31) = ""

Sheets("База").Cells(2, 32) = ""

Sheets("База").Cells(2, 33) = ""

Sheets("База").Cells(2, 34) = ""

If cb_stat.Text = "Любой" Then Sheets("База").Cells(2, 31) = "" Else Sheets("База").Cells(2, 31) = cb_stat.Text

If CheckBox1 = True Then

Sheets("База").Cells(2, 32) = ">=" & CDate("1.1." & ed_year.Text)

Sheets("База").Cells(2, 33) = "<" & CDate("1.1." & Val(ed_year.Text) + 1)

End If

If cb_car.Text = "Любой" Then Sheets("База").Cells(2, 34) = "" Else Sheets("База").Cells(2, 34) = cb_car.Text

Sheets("База").Range(Cells(1, 1), Cells(all, 29)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("База").Range( _

"AE1:AH2"), CopyToRange:=Sheets("База").Range("AJ1"), Unique:=False

If CheckBox1.Value = True Then y = ed_year.Text Else y = "Любой"

Sheets("База").Cells(1, 36).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If Sheets("База").Cells(i, 58) = "Да" Then ins1 = ins1 + 1

If Sheets("База").Cells(i, 59) = "Да" Then ins2 = ins2 + 1

If Sheets("База").Cells(i, 60) = "Да" Then ins3 = ins3 + 1

If Sheets("База").Cells(i, 58) = "Да" And Sheets("База").Cells(i, 59) = "Да" And Sheets("База").Cells(i, 60) = "Да" Then fins = fins + 1

If Sheets("База").Cells(i, 61) = "Да" Then gai1 = gai1 + 1

If Sheets("База").Cells(i, 62) = "Да" Then gai2 = gai2 + 1

If Sheets("База").Cells(i, 63) = "Да" Then gai3 = gai3 + 1

If Sheets("База").Cells(i, 61) = "Да" And Sheets("База").Cells(i, 62) = "Да" And Sheets("База").Cells(i, 63) = "Да" Then fgai = fgai + 1

Next i

x = MsgBox("По вашему запросу:" & vbNewLine & "Статус -" & cb_stat.Text & ", Год рождения: " & y & ", Автомобиль: " & cb_car.Text & vbNewLine & vbNewLine & "Всего записей: " & all - 1 & vbNewLine & vbNewLine & "Сдавших внутренний экзамен - ПДД: " & ins1 & vbNewLine & "Сдавших внутренний экзамен - Автодром: " & ins2 & vbNewLine & "Сдавших внутренний экзамен - Город: " & ins3 & vbNewLine & "Итого допущеных до экзамена в ГАИ: " & fins & vbNewLine & "Сдавших экзамен в ГАИ - ПДД: " & gai1 & vbNewLine & "Сдавших экзамен в ГАИ - Автодром: " & gai2 & vbNewLine & "Сдавших экзамен в ГАИ - Город: " & gai3 & vbNewLine & "Итого получивших права: " & fgai, vbInformation + vbOKOnly, "Автошкола")

End Sub

Кнопка “Табличный отчет”:

Private Sub bt_tab_Click()

Sheets("Отчет").Visible = True

Dim ins1, ins2, ins3, gai1, gai2, gai3, fins, fgai As Integer

ins1 = 0

ins2 = 0

ins3 = 0

gai1 = 0

gai2 = 0

gai3 = 0

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

Selection.CurrentRegion.Select

all = Selection.CurrentRegion.Rows.Count

Sheets("База").Activate

Sheets("База").Cells(1, 36).Select

Selection.CurrentRegion.Select

Selection.Clear

Sheets("База").Cells(2, 31) = ""

Sheets("База").Cells(2, 32) = ""

Sheets("База").Cells(2, 33) = ""

Sheets("База").Cells(2, 34) = ""

If cb_stat.Text = "Любой" Then Sheets("База").Cells(2, 31) = "" Else Sheets("База").Cells(2, 31) = cb_stat.Text

If CheckBox1 = True Then

Sheets("База").Cells(2, 32) = ">=" & CDate("1.1." & ed_year.Text)

Sheets("База").Cells(2, 33) = "<" & CDate("1.1." & Val(ed_year.Text) + 1)

End If

If cb_car.Text = "Любой" Then Sheets("База").Cells(2, 34) = "" Else Sheets("База").Cells(2, 34) = cb_car.Text

Sheets("База").Range(Cells(1, 1), Cells(all, 29)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("База").Range( _

"AE1:AH2"), CopyToRange:=Sheets("База").Range("AJ1"), Unique:=False

If CheckBox1.Value = True Then y = ed_year.Text Else y = "Любой"

Sheets("База").Cells(1, 36).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If Sheets("База").Cells(i, 58) = "Да" Then ins1 = ins1 + 1

If Sheets("База").Cells(i, 59) = "Да" Then ins2 = ins2 + 1

If Sheets("База").Cells(i, 60) = "Да" Then ins3 = ins3 + 1

If Sheets("База").Cells(i, 58) = "Да" And Sheets("База").Cells(i, 59) = "Да" And Sheets("База").Cells(i, 60) = "Да" Then fins = fins + 1

If Sheets("База").Cells(i, 61) = "Да" Then gai1 = gai1 + 1

If Sheets("База").Cells(i, 62) = "Да" Then gai2 = gai2 + 1

If Sheets("База").Cells(i, 63) = "Да" Then gai3 = gai3 + 1

If Sheets("База").Cells(i, 61) = "Да" And Sheets("База").Cells(i, 62) = "Да" And Sheets("База").Cells(i, 63) = "Да" Then fgai = fgai + 1

Next i

Sheets("Отчет").Activate

Sheets("Отчет").Range("A3") = cb_stat.Text

If ed_year.Text = "" Then Sheets("Отчет").Range("B3") = "Любой" Else Sheets("Отчет").Range("B3") = ed_year.Text

Sheets("Отчет").Range("C3") = cb_car.Text

Sheets("Отчет").Range("B4") = ins1

Sheets("Отчет").Range("B5") = ins2

Sheets("Отчет").Range("B6") = ins3

Sheets("Отчет").Range("D4") = gai1

Sheets("Отчет").Range("D5") = gai2

Sheets("Отчет").Range("D6") = gai3

Sheets("Отчет").Range("D8") = fins

Sheets("Отчет").Range("D10") = fgai

Sheets("Отчет").Range("B11") = all - 1

StatForm.Hide

End Sub

Флажок “Активация года рождения”:

Private Sub CheckBox1_Click()

If CheckBox1 = True Then

ed_year.Enabled = True

ed_year.Text = 1990

Else

ed_year.Enabled = False

ed_year.Text = ""

End If

End Sub

Поле “Год рождения”:

Private Sub ed_year_Change()

If CheckBox1 = True Then ed_year.Text = Val(ed_year.Text)

End Sub

Активация формы:

Private Sub UserForm_Activate()

cb_stat.Clear

cb_car.Clear

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

all = Selection.CurrentRegion.Rows.Count

descnumcount.Caption = all - 1

cb_stat.AddItem ("Любой")

cb_stat.AddItem ("Обучаемый")

cb_stat.AddItem ("Окончил")

cb_stat.ListIndex = 0

Sheets("Данные").Activate

Sheets("Данные").Cells(1, 14).Select

alld = Selection.CurrentRegion.Rows.Count

cb_car.AddItem ("Любой")

For i = 2 To alld

cb_car.AddItem (Sheets("Данные").Cells(i, 14))

Next i

cb_car.ListIndex = 0

ed_year.Enabled = False

End Sub

Завершение работы формы:

Private Sub UserForm_Terminate()

StatForm.Hide

MainForm.Show (0)

End Sub

Private Sub cb_car_Change()

Sheets("Данные").Activate

cb_teacher.Clear

For i = 2 To 10

If Sheets("Данные").Cells(i, 4) = cb_car.Value Then cb_teacher.AddItem Sheets("Данные").Cells(i, 3)

Next i

If cb_car.Value = "Любой" Then cb_teacher.AddItem "Любой"

cb_teacher.ListIndex = 0

End Sub

Кнопка “Закрыть”:

Private Sub CommandButton1_Click()

InstrForm.Hide

MainForm.Show (0)

End Sub

Кнопка “Отчет”:

Private Sub CommandButton2_Click()

Dim ins1, ins2, gai1, gai2, fins, fgai As Integer

ins1 = 0

ins2 = 0

gai1 = 0

gai2 = 0

fins = 0

fgai = 0

Sheets("База").Activate

Sheets("База").Cells(1, 1).Select

Selection.CurrentRegion.Select

all = Selection.CurrentRegion.Rows.Count

Sheets("База").Activate

Sheets("База").Range("BN2") = ""

Sheets("База").Range("BO2") = ""

Sheets("База").Cells(1, 70).Select

Selection.CurrentRegion.Select

Selection.Clear

If cb_car.Text <> "Любой" Then Sheets("База").Range("BN2") = cb_car.Text

If cb_teacher.Text <> "Любой" Then Sheets("База").Range("BO2") = cb_teacher.Text

Sheets("База").Range(Cells(1, 1), Cells(all, 29)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("База").Range( _

"BN1:BP2"), CopyToRange:=Sheets("База").Range("BR1"), Unique:=False

Sheets("База").Cells(1, 70).Select

all = Selection.CurrentRegion.Rows.Count

For i = 2 To all

If Sheets("База").Cells(i, 93) = "Да" Then ins1 = ins1 + 1

If Sheets("База").Cells(i, 94) = "Да" Then ins2 = ins2 + 1

If Sheets("База").Cells(i, 92) = "Да" And Sheets("База").Cells(i, 93) = "Да" And Sheets("База").Cells(i, 94) = "Да" Then fins = fins + 1

If Sheets("База").Cells(i, 96) = "Да" Then gai1 = gai1 + 1

If Sheets("База").Cells(i, 97) = "Да" Then gai2 = gai2 + 1

If Sheets("База").Cells(i, 95) = "Да" And Sheets("База").Cells(i, 96) = "Да" And Sheets("База").Cells(i, 97) = "Да" Then fgai = fgai + 1

Next i

Sheets("Отчет_Инструктор").Activate

Sheets("Отчет_Инструктор").Range("B1") = cb_teacher.Text

Sheets("Отчет_Инструктор").Range("B2") = cb_car.Text

Sheets("Отчет_Инструктор").Range("B3") = all - 1

Sheets("Отчет_Инструктор").Range("B4") = ins1

Sheets("Отчет_Инструктор").Range("B5") = ins2

Sheets("Отчет_Инструктор").Range("B6") = fins

Sheets("Отчет_Инструктор").Range("B7") = gai1

Sheets("Отчет_Инструктор").Range("B8") = gai2

Sheets("Отчет_Инструктор").Range("B9") = fgai

Sheets("Отчет_Инструктор").Range("B11") = ((100 / (all - 1)) * fgai) / 100

InstrForm.Hide

Sheets("Отчет_Инструктор").Visible = True

End Sub

Активация формы:

Private Sub UserForm_Activate()

Sheets("Данные").Activate

cb_car.Clear

cb_car.AddItem ("Любой")

cb_car.AddItem ("ВАЗ-2105")

cb_car.AddItem ("ВАЗ-2106")

cb_car.AddItem ("ВАЗ-2108")

cb_car.AddItem ("ВАЗ-2109")

cb_car.AddItem ("ВАЗ-2110")

cb_car.ListIndex = 0

End Sub

Завершение работы формы:

Private Sub UserForm_Terminate()

MainForm.Show (0)

End Sub

Страницы: 1, 2