Private Sub CmdView_Click()
Application.Goto Worksheets("Leave Application").Range("A1")
If TxtName.Value = "" Then
MsgBox "Please enter Employee ID", vbCritical
Exit Sub
End If
If OptionButton1.Value = False _
And OptionButton2.Value = False Then
Msg = MsgBox("Please select an option in Level")
Exit Sub
End If
If OptionButton3.Value = False _
And OptionButton4.Value = False _
And OptionButton5.Value = False Then
Msg = MsgBox("Please select an option in Type of Application")
Exit Sub
End If
If OptionButton6.Value = False _
And OptionButton7.Value = False _
And OptionButton8.Value = False _
And OptionButton9.Value = False _
And OptionButton10.Value = False _
And OptionButton11.Value = False Then
Msg = MsgBox("Please select an option in Type of Leave")
Exit Sub
End If
If TxtFrom.Value = "" Then
MsgBox "Please enter Leave Date From", vbCritical
Exit Sub
End If
If TxtTo.Value = "" Then
MsgBox "Please enter Leave Date To", vbCritical
Exit Sub
End If
If TxtImmediateHead.Value = "" Then
MsgBox "Please enter Immediate Head", vbCritical
Exit Sub
End If
Dim ws As Worksheet
Set ws = Worksheets("Ref")
'check for a part number
If Trim(Me.TxtName.Value) = "" Then
Me.TxtName.SetFocus
Me.OptionButton1.SetFocus
Me.OptionButton2.SetFocus
MsgBox "Please enter Employee ID"
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Range("D7").Value = Me.TxtName.Value
.Range("D8").Value = Me.TxtPosition.Value
.Range("d9").Value = Me.TxtBusinessUnit.Value
.Range("d10").Value = Me.TxtDepartment.Value
.Range("d11").Value = Me.TxtFrom.Value
.Range("d12").Value = Me.TxtTo.Value
.Range("d13").Value = Me.ComboBox1.Value
.Range("d14").Value = Me.TxtImmediateHead.Value
' .Protect Password:="password"
End With
'clear the data
Me.TxtName.Value = ""
Me.TxtPosition.Value = ""
Me.TxtBusinessUnit.Value = ""
Me.TxtDepartment.Value = ""
Me.TxtFrom.Value = ""
Me.TxtTo.Value = ""
Me.ComboBox1.Value = ""
Me.TxtImmediateHead.Value = ""
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub CommandButton1_Click()
ActiveWindow.SelectedSheets.PrintOut
End Sub
Private Sub OptionButton1_Click()
Worksheets("Ref").Range("d5").Value = 1
End Sub
Private Sub OptionButton10_Click()
Worksheets("Ref").Range("d3").Value = 5
End Sub
Private Sub OptionButton11_Click()
Worksheets("Ref").Range("d3").Value = 6
End Sub
Private Sub OptionButton12_Click()
Worksheets("Ref").Range("d4").Value = 1
End Sub
Private Sub OptionButton13_Click()
Worksheets("ref").Range("d4").Value = 2
End Sub
Private Sub OptionButton2_Click()
Worksheets("Ref").Range("d5").Value = 2
End Sub
Private Sub OptionButton3_Click()
Worksheets("Ref").Range("D1").Value = 1
End Sub
Private Sub OptionButton4_Click()
Worksheets("Ref").Range("d1").Value = 2
End Sub
Private Sub OptionButton5_Click()
Worksheets("Ref").Range("d1").Value = 3
End Sub
Private Sub OptionButton6_Click()
Worksheets("Ref").Range("d3").Value = 1
End Sub
Private Sub OptionButton7_Click()
Worksheets("Ref").Range("d3").Value = 2
End Sub
Private Sub OptionButton8_Click()
Worksheets("Ref").Range("d3").Value = 3
End Sub
Private Sub OptionButton9_Click()
Worksheets("Ref").Range("d3").Value = 4
End Sub
Private Sub ResetButton_Click()
Sheets("Ref").Range("D1:D21").ClearContents
LeaveApp.TxtName.Value = ""
Unload Me
LeaveApp.Show
End Sub
Private Sub TxtName_Change()
If Application.CountIf(Worksheets("Ref").Range("L1:L1000"), TxtName.Value) > 0 Then
TxtPosition.Value = Worksheets("Ref").Range("L1:Y1000").Find(What:=TxtName.Value, After:=[L1000], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1).Value
Else
TxtPosition.Value = ""
End If
If Application.CountIf(Worksheets("Ref").Range("L1:L1000"), TxtName.Value) > 0 Then
TxtBusinessUnit.Value = Worksheets("ref").Range("L1:Y1000").Find(What:=TxtName.Value, After:=[L1000], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 3).Value
TxtName.Visible = True
Else
TxtBusinessUnit.Value = ""
End If
If Application.CountIf(Worksheets("Ref").Range("L1:L1000"), TxtName.Value) > 0 Then
TxtDepartment.Value = Worksheets("Ref").Range("L1:Y1000").Find(What:=TxtName.Value, After:=[L1000], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 2).Value
TxtName.Visible = True
Else
TxtDepartment.Value = ""
End If
End Sub
Private Sub TxtPosition_Change()
End Sub
Private Sub UserForm_Initialize()
ComboBox1.AddItem ("Birthday Leave")
ComboBox1.AddItem ("Marriage Leave")
ComboBox1.AddItem ("Bereavement Leave")
ComboBox1.AddItem ("Solo Parent Leave")
ComboBox1.AddItem ("Special Leave for Women")
ComboBox1.AddItem ("Violence Against Women")
ComboBox1.AddItem ("Official Business")
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close button!"
End If
End Sub