Private Sub ComboBox1_Change()
Me.ListBox1.Clear
Me.Label6 = Sheets(ComboBox1.Value).Name
TextBox1.SetFocus
End Sub
Private Sub TextBox1_AfterUpdate()
Me.TextBox1 = CDate(Me.TextBox1)
TextBox2.SetFocus
End Sub
Private Sub TextBox2_AfterUpdate()
Application.ScreenUpdating = False
Dim beginDate As Long, endDate As Long, srcWS As Worksheet, rng As Range, LastRow As Long, total As Double, C As Long
Set srcWS = Sheets(ComboBox1.Value)
Me.TextBox1 = CDate(Me.TextBox1)
beginDate = DateValue(Me.TextBox1.Value)
endDate = DateValue(Me.TextBox2.Value)
ldatefrom = DateSerial(Day(beginDate), Month(beginDate), Year(beginDate))
ldateto = DateSerial(Day(endDate), Month(endDate), Year(endDate))
Me.ListBox1.Clear
With srcWS
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & CLng(beginDate), Operator:=xlAnd, Criteria2:="<=" & CLng(endDate)
For Each rng In .Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible)
If rng.Row > 1 Then
total = total + rng.Offset(, 3).Value
End If
Me.ListBox1.AddItem
For C = 0 To 3
Me.ListBox1.List(ListBox1.ListCount - 1, C) = .Cells(rng.Row, C + 1)
Next C
Next rng
.Range("A1").AutoFilter
Label5.Caption = "Total Hours: " & total
End With
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
For i = 2 To Sheets.Count
Me.ComboBox1.AddItem Sheets(i).Name
Next i
End Sub