Option Explicit
Dim LASTROW As Long
Dim LASTROWF As Long
Private Sub CHECKBOX2_CLICK()
If CheckBox3 = True Then
CheckBox3 = False
MsgBox "Nel'zya stavit' galku v oboikh mestakh. Nuzhno oboznachit' chto libo eto nash sotrudnik, libo podryadchik."
End If
End Sub
Private Sub CHECKBOX3_CLICK()
If CheckBox2 = True Then
CheckBox2 = False
MsgBox "Nel'zya stavit' galku v oboikh mestakh. Nuzhno oboznachit' chto libo eto nash sotrudnik, libo podryadchik."
End If
End Sub
Private Sub COMBOBOX2_CHANGE()
ComboBox2.Style = fmStyleDropDownList
'If ComboBox2.ListIndex = -1 Then Application.OnTime Now + 0.00000001, "qq"
End Sub
Private Sub COMBOBOX4_CHANGE()
ComboBox4.Style = fmStyleDropDownList
End Sub
'vybor daty po kliku
Private Sub COMMANDBUTTON1_CLICK()
calendar.Show
If calendar.Value > 0 Then CommandButton1.Caption = Format(calendar.Value, "dd.mm.yyyy")
End Sub
Private Sub COMMANDBUTTON2_CLICK()
calendar.Show
If calendar.Value > 0 Then CommandButton2.Caption = Format(calendar.Value, "dd.mm.yyyy")
End Sub
Private Sub COMMANDBUTTON3_CLICK()
calendar.Show
If calendar.Value > 0 Then CommandButton3.Caption = Format(calendar.Value, "dd.mm.yyyy")
End Sub
Private Sub COMMANDBUTTON4_CLICK()
'Sub Macros1()
'
' Macros1 Macros
'
Worksheets("Base").Unprotect Password:="123"
Dim SFILENAME As String, SNEWFILENAME As String, M
SFILENAME = GetFileName("Vyberite foto", ThisWorkbook.Path)
If SFILENAME = "" Then Exit Sub
M = Split(SFILENAME, " \ ")
SNEWFILENAME = "P:\COPY\Safety Audits\photo\" & M(UBound(M)) '
FileCopy SFILENAME, SNEWFILENAME
ActiveCell.FormulaR1C1 = "=HYPERLINK(""" & SNEWFILENAME & ""","">>>"")"
MsgBox "Done", vbInformation
End Sub
Private Sub LABEL12_CLICK()
End Sub
Private Sub LABEL43_CLICK()
End Sub
Private Sub LABEL15_CLICK()
End Sub
Private Sub LABEL71_CLICK()
End Sub
Private Sub TEXTBOX1_CHANGE()
If CheckBox3 = False Then
If CheckBox2 = False Then
MsgBox "Komu Vy provodite PAB? Personal predpriyatiya ili Podryadchik?" & vbCrLf & "Postav'te galku"
End If
End If
End Sub
Private Sub TEXTBOX6_CHANGE()
End Sub
Private Sub USERFORM_INITIALIZE()
With Sheets("People") 'Dobavlenie Sotrudnikov v spisok
' Stop
LASTROW = .Cells(Rows.Count, 1).End(xlUp).Row
LASTROWF = .Cells(Rows.Count, 6).End(xlUp).Row
Me.AuditorFIO.List = .Range("a2:a" & LASTROW).Value
Me.ComboBox4.List = .Range("f2:f" & LASTROWF).Value
Me.ComboBox5.List = .Range("a2:a" & LASTROW).Value
Me.ComboBox2.List = .Range("c2:c" & LASTROW).Value 'Array("Na rabote", "V obed", "i t.d.") 'Dobavlenie sostavleniya PAB v spisok
End With
End Sub
'Protsedura izmeneniya v kombe. V zavisimosti ot vybrannykh dannykh v kombobokse izmenyayutsya polya tekstboksov
Private Sub AuditorFIO_Change()
TextBox6 = Sheets("People").Cells(AuditorFIO.ListIndex + 2, 2): TextBox7 = Sheets("People").Cells(AuditorFIO.ListIndex + 2, 3)
End Sub
Private Sub Zapis() 'PAB_Click()
Dim IROW As Long
Dim A As Long
Dim B As Long
Dim C As Long
Dim s As Long
Dim FF As String
'ActiveSheet.Protect Password:="123" 'ActiveSheet.Unprotect Password:="123"
Dim ICOUNTER As Long
Dim WS As Worksheet
Set WS = Worksheets("Base")
'Stop
If CommandButton1.Caption = "Data" Or AuditorFIO = "" Or ComboBox2 = "" Or TextBox5 = "" Or TextBox1 = "" Or TextBox3 = "" Or ComboBox4 = "" Or CommandButton2.Caption = "Data vypolneniya1" Then
MsgBox "Ne zapolneny neobkhodimye polya. Okoshki i Daty belogo tsveta"
GoTo 1
End If
If CheckBox53 = False Then
If CheckBox52 = False Then
MsgBox "Oboznach'te galkoj kakoj tip PABa?" & vbCrLf & "Opasnoe dejstvie ili opasnoe uslovie raboty?"
GoTo 1
End If
End If
IROW = WS.Cells.Find(WHAT:=" * ", SEARCHORDER:=xlRows, SEARCHDIRECTION:=xlPrevious, LookIn:=xlValues).Row + 1
Worksheets("Base").Unprotect Password:="123"
'Stop
With Sheets("Base")
If CheckBox8 = True Then
.Cells(IROW, "S").Value = Label16 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox7 = True Then
.Cells(IROW, "S").Value = Label15 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox9 = True Then
.Cells(IROW, "S").Value = Label17 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox10 = True Then
.Cells(IROW, "S").Value = Label18 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox11 = True Then
.Cells(IROW, "S").Value = Label19 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox12 = True Then
.Cells(IROW, "S").Value = Label20 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox13 = True Then
.Cells(IROW, "S").Value = Label21 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox17 = True Then
.Cells(IROW, "T").Value = Label27 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox15 = True Then
.Cells(IROW, "T").Value = Label25 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox16 = True Then
.Cells(IROW, "T").Value = Label28 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox18 = True Then
.Cells(IROW, "T").Value = Label26 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox19 = True Then
.Cells(IROW, "T").Value = Label29 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox20 = True Then
.Cells(IROW, "T").Value = Label30 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox22 = True Then
.Cells(IROW, "T").Value = Label22 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox6 = True Then
.Cells(IROW, "U").Value = Label14 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox26 = True Then
.Cells(IROW, "U").Value = Label37 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox24 = True Then
.Cells(IROW, "U").Value = Label35 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox25 = True Then
.Cells(IROW, "U").Value = Label36 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox27 = True Then
.Cells(IROW, "U").Value = Label38 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox30 = True Then
.Cells(IROW, "U").Value = Label41 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox31 = True Then
.Cells(IROW, "U").Value = Label32 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox35 = True Then
.Cells(IROW, "V").Value = Label46 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox33 = True Then
.Cells(IROW, "V").Value = Label44 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox34 = True Then
.Cells(IROW, "V").Value = Label45 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox36 = True Then
.Cells(IROW, "V").Value = Label47 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox49 = True Then
.Cells(IROW, "V").Value = Label62 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox41 = True Then
.Cells(IROW, "W").Value = Label53 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox39 = True Then
.Cells(IROW, "W").Value = Label51 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox40 = True Then
.Cells(IROW, "W").Value = Label54 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox42 = True Then
.Cells(IROW, "W").Value = Label52 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox50 = True Then
.Cells(IROW, "W").Value = Label63 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox47 = True Then
.Cells(IROW, "X").Value = Label60 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox45 = True Then
.Cells(IROW, "X").Value = Label58 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox46 = True Then
.Cells(IROW, "X").Value = Label59 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox48 = True Then
.Cells(IROW, "X").Value = Label61 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
If CheckBox51 = True Then
.Cells(IROW, "X").Value = Label64 'zapis' vida narusheniya iz Label v Bazu
C = C + 1
End If
End With
If C = 0 Then
Worksheets("Base").Cells(IROW, "AA").Value = 1 '"Bezopasno"
MsgBox "Vy ne postavili ni odnoj galki v kharakteristikakh dejstvij rabotnika(nablyudaemogo)" & vbCrLf & "Postav'te galki oboznachiv dejstviya nablyudaemogo ili sostoyanie mesta"
GoTo 1
End If
'___________ vybor pol'zovatelya vstavlyat' li foto ________________
If MsgBox("KHotite dobavit' FOTO?", vbYesNo, "adding a photo") = vbNo Then
'Selection = "Nazhata Net"
GoTo 20
Else
'Selection = "Nazhata DA"
End If
'____________________________ vsavka giperssylki _____________________
Worksheets("Base").Unprotect Password:="123"
Dim SFILENAME As String, SNEWFILENAME As String, M
SFILENAME = GetFileName("Vyberite foto", ThisWorkbook.Path)
If SFILENAME = "" Then Exit Sub
M = Split(SFILENAME, " \ ")
SNEWFILENAME = "P:\COPY\Vnutrennie audity po OT i PB\photo\" & M(UBound(M)) '
FileCopy SFILENAME, SNEWFILENAME
'Stop
FF = "=HYPERLINK(""" & SNEWFILENAME & ""","">>>"")"
' MsgBox "Done", vbInformation
'ZAPIS' STROK V LIST "Base"
20
Worksheets("Base").Cells(IROW, "A").Value = Me.CommandButton1.Caption 'Data sostavleniya PAB
Worksheets("Base").Cells(IROW, "B").Value = Me.AuditorFIO.Value '
Worksheets("Base").Cells(IROW, "C").Value = Me.TextBox6.Value '
'Stop
Worksheets("Base").Cells(IROW, "D").Value = Me.TextBox7.Value '
Worksheets("Base").Cells(IROW, "E").Value = Me.ComboBox2.Value 'gde sostavlen
Worksheets("Base").Cells(IROW, "F").Value = Me.TextBox5.Value ' opisanie rabot
If CheckBox2 = True Then
Worksheets("Base").Cells(IROW, "G").Value = 1 ' ptichka nash
End If
If CheckBox3 = True Then
Worksheets("Base").Cells(IROW, "H").Value = 1 ' ptichka podryadchik
End If
Worksheets("Base").Cells(IROW, "I").Value = Me.TextBox1.Value 'Opisanie nablyudeniya
Worksheets("Base").Cells(IROW, "J").Value = Me.TextBox2.Value 'Vtoroe Opisanie nablyudeniya
If CheckBox52 = True Then
Worksheets("Base").Cells(IROW, "K").Value = 1 ' ptichka opasnoe uslovie
End If
If CheckBox53 = True Then
Worksheets("Base").Cells(IROW, "L").Value = 1 ' ptichka opasnoe povedenie
End If
Worksheets("Base").Cells(IROW, "M").Value = Me.TextBox3.Value 'Meropriyatie
Worksheets("Base").Cells(IROW, "N").Value = Me.TextBox4.Value 'Meropriyatie 2
Worksheets("Base").Cells(IROW, "O").Value = Me.ComboBox4.Value 'Otvetstvennyj za meropr1
Worksheets("Base").Cells(IROW, "P").Value = Me.ComboBox5.Value 'Otvetstvennyj za meropr2
Worksheets("Base").Cells(IROW, "Q").Value = Me.CommandButton2.Caption 'Data vypolneniya Meropriyatie 1
Worksheets("Base").Cells(IROW, "R").Value = Me.CommandButton3.Caption 'Data vypolneniya Meropriyatie 2
'Stop
'Worksheets("Base").Cells(iRow, "AC").Value = FF
Worksheets("Base").Cells(IROW, "AC").FormulaR1C1 = FF
'Stop
With Sheets("Base")
For ICOUNTER = 19 To 24
If .Cells(IROW, ICOUNTER).Value = "" Then
A = A + 1
.Cells(IROW, "AA") = A
.Cells(IROW, "AB") = 1
Else
B = B + 1
.Cells(IROW, "Y").Value = B
.Cells(IROW, "Z").Value = 1
End If
Next
If B > 0 Then
.Cells(IROW, "AB") = 0
.Cells(IROW, "AB").Interior.ColorIndex = 3 'okraska bezopasnoj yachejki v krasnyj
End If
End With
Worksheets("Base").Protect Password:="123"
Unload UserForm1
'Stop
' KhitrovIA@biaxplen.ru
2
Dim OUTAPP As Object
Dim OUTMAIL As Object
Dim CELL As Range
Dim LL As String
Application.ScreenUpdating = False
Set OUTAPP = CreateObject("Outlook.Application") 'zapuskaem Outlook v skrytom rezhime
OUTAPP.SESSION.LOGON
On Error GoTo CLEANUP 'esli ne zapustilsya - vykhodim
Set OUTMAIL = OUTAPP.CREATEITEM(0) 'sozdaem novoe soobschenie
On Error Resume Next
'Stop
'll = Sheets("People").Cells(1, 9).Value
'GoTo 3
With OUTMAIL
.To = Sheets("People").Cells(ComboBox4.ListIndex + 2, 4).Value 'Sheets("People").Cells(AuditorFIO.ListIndex + 2, 2)
.CC = Sheets("People").Cells(1, 9).Value
.Subject = "PAB " & " Avtomaticheskaya rassylka" '.Value
.BODY = "Dobryj den' " & Sheets("Base").Cells(IROW, 15) & " " & vbCrLf & " " & Sheets("Base").Cells(IROW, 1) & " " & vbCrLf & "vo vremya: " & Sheets("Base").Cells(IROW, 6) & vbCrLf & "mnoyu byla vyyavlena sleduyuschaya opasnaya situatsiya: " & Sheets("Base").Cells(IROW, 9).Value & vbCrLf & "proshu Vas prinyat' korrektiruyushie mery v vide: " & Sheets("Base").Cells(IROW, 13) & vbCrLf & "Srokom do: " & Sheets("Base").Cells(IROW, 17) & vbCrLf & "Esli Vy uvereny chto Otvetstvennym za vypolnenie dannogo korrektiruyuschego meropriyatiya dolzhen byt' drugoj sotrudnik, pereshlite eto pis'mo emu, postaviv v kopiyu menya i nachal'nika otdela OT i PB "
'.Attachments.Add Range("A4").Value
.SEND 'Send Display'komandu Send mozhno zamenit' na Display, chtoby posmotret' soobschenie pered otpravkoj
End With
'3
If ComboBox5.ListIndex >= 0 Then
With OUTMAIL
.To = Sheets("People").Cells(ComboBox4.ListIndex + 2, 4).Value 'Sheets("People").Cells(AuditorFIO.ListIndex + 2, 2)
.CC = Sheets("People").Cells(1, 9).Value
.Subject = "PAB " & " Avtomaticheskaya rassylka" '.Value
.BODY = "Dobryj den' " & Sheets("Base").Cells(IROW, 16) & " " & vbCrLf & " " & Sheets("Base").Cells(IROW, 1) & " " & vbCrLf & "vo vremya: " & Sheets("Base").Cells(IROW, 6) & vbCrLf & "mnoyu byla vyyavlena sleduyuschaya opasnaya situatsiya: " & Sheets("Base").Cells(IROW, 10).Value & vbCrLf & "proshu Vas prinyat' korrektiruyushie mery v vide: " & Sheets("Base").Cells(IROW, 14) & vbCrLf & "Srokom do: " & Sheets("Base").Cells(IROW, 18) & vbCrLf & "Esli Vy uvereny chto Otvetstvennym za vypolnenie dannogo korrektiruyuschego meropriyatiya dolzhen byt' drugoj sotrudnik, pereshlite eto pis'mo emu, postaviv v kopiyu menya i nachal'nika otdela OT i PB "
'.Attachments.Add Range("A4").Value
.SEND 'SendDisplay
End With
End If
On Error GoTo 0
Set OUTMAIL = Nothing
CLEANUP:
Set OUTAPP = Nothing
Application.ScreenUpdating = True
1
End Sub |