Thursday, September 28, 2017

Monday, September 25, 2017

Listbox Create Labour Working Hours Calculater

Private Sub ComboBox1_Change()
Dim i As Long
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = False
c = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
If Me.ListBox1.List(i, 0) = Me.ComboBox1.Value And _
Format(Me.ListBox1.List(i, 1), "MMMM") = Me.ComboBox2.Value Then
Me.ListBox1.Selected(i) = True
For x = 1 To 4
Sheet1.Range("A" & c).End(xlToLeft).Offset(1, x - 1) = _
Me.ListBox1.List(i, x - 1)
Next x
End If
Next i
Dim Totala As Long, Totalb As Long
For r = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(r) = True Then
Totala = Totala + Val(Me.ListBox1.List(r, 2))
Totalb = Totalb + Val(Me.ListBox1.List(r, 3))
End If
Next r
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" Then
Sheet1.Range("A1000000").End(xlUp).Offset(1, 0).Value = "Total"
Sheet1.Range("A1000000").End(xlUp).Offset(0, 1).Value = Me.ComboBox2.Value
Sheet1.Range("A1000000").End(xlUp).Offset(0, 2).Value = Totala
Sheet1.Range("A1000000").End(xlUp).Offset(0, 3).Value = Totalb
End If
End Sub
_____________________________________________________
Private Sub CommandButton1_Click()
For b = 0 To 6
On Error Resume Next
Me.ComboBox1.ListIndex = b
Next b
End Sub
_____________________________________________________
Private Sub CommandButton2_Click()
Dim i As Long
Me.ComboBox1.ListIndex = 0 - 1
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
Sheet1.Range("A" & 2, "D" & i).ClearContents
Next i
End Sub