EndSub
Кнопка “Табличный отчет”:
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
EndSub
Флажок “Активация года рождения”:
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
EndSub
Завершение работы формы:
Private Sub UserForm_Terminate()
MainForm.Show (0)
End Sub