Private Sub CommandButton1_Click()
Application.EnableCancelKey = xlDisabled
' Validate Dates
Dim startDate As Date
Dim endDate As Date
startDate = CDate(IIf(TextBox1.Value = vbNullString, 0, TextBox1.Value))
endDate = CDate(IIf(TextBox2.Value = vbNullString, 0, TextBox2.Value))
If startDate = 0 Then
MsgBox "You need to select your First Day off", vbCritical, "Beginning dates"
Exit Sub
End If
If endDate = 0 Then
MsgBox "You need to select your Last day off", vbCritical, "End Date"
Exit Sub
End If
' Validate product
Dim productName As String
productName = ComboBox1.Value
If productName = vbNullString Then
MsgBox "Please choose a Supervisor", vbCritical, "Select Supervisors"
Exit Sub
End If
' Prepare listbox
Dim dataListBox As MSForms.ListBox
Set dataListBox = Me.ListBox1
With dataListBox
.Clear
.ColumnCount = 8
.Top = 122
.Font.Size = 10
.IntegralHeight = False
AddDataToListBox dataListBox, productName, startDate, endDate
End With
With ListBox1
'.Height = .Height + .Font.Size + 2
.Top = Me.ListBox1.Top + 10
ListBox1.ColumnWidths = "70;80;80;80;80;80;85;70"
.IntegralHeight = True
End With
End Sub
Private Sub AddDataToListBox(ByVal listBoxControl As MSForms.ListBox, ByVal productName As String, ByVal startDate As Date, ByVal endDate As Date)
' Set a reference to the vehicles worksheet
Dim AttendanceSheet As Worksheet
Set AttendanceSheet = ThisWorkbook.Worksheets("Attendance")
Application.ScreenUpdating = False
Application.EnableEvents = False
' Get last row
Dim lastRow As Long
lastRow = AttendanceSheet.Cells(AttendanceSheet.Rows.Count, "A").End(xlUp).Row
' Set range to evaluate
Dim sourceRange As Range
Set sourceRange = AttendanceSheet.Range("A5:A" & lastRow)
' Loop through each cell in range
Dim sourceCell As Range
For Each sourceCell In sourceRange.Cells
' Check if source cell is between dates
If sourceCell.Value >= startDate And sourceCell.Value <= endDate Then
' Check if product matches
If sourceCell.Offset(0, 7).Value = productName Then
' Begin a counter to add list items
Dim counter As Long
With listBoxControl
Dim X, d, yuk, mak As Integer
For X = 1 To 15
DoEvents
If E = 0 Then
d = d + 9
yuk = 70
End If
UserForm4.Height = yuk + d
Next
.AddItem
.List(counter, 0) = sourceCell.Offset(0, 0).Value
' You're missing one column here (is it on purpose?)
.List(counter, 1) = sourceCell.Offset(0, 1).Value
.List(counter, 2) = sourceCell.Offset(0, 2).Value
.List(counter, 3) = sourceCell.Offset(0, 3).Value
.List(counter, 4) = sourceCell.Offset(0, 4).Value
.List(counter, 5) = sourceCell.Offset(0, 5).Value
.List(counter, 6) = sourceCell.Offset(0, 6).Value
.List(counter, 7) = sourceCell.Offset(0, 7).Value
End With
counter = counter + 1
End If
End If
Next sourceCell
If counter = 0 Then
MsgBox "No new record found!", vbExclamation, "No Records"
Exit Sub
Else
MsgBox Me.ListBox1.ListCount & Space(4) & "New Records Found", vbInformation, "New Records Found"
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim X, a, b As Long, c As Variant
Dim h As Variant
Set sh = Worksheets("Attendance")
Application.ScreenUpdating = False
'Unique Records
For X = 2 To Cells(Rows.Count, 8).End(xlUp).Row
If WorksheetFunction.CountIf(sh.Range("H5:H" & X), Cells(X, 8)) = 1 Then
ComboBox1.AddItem Cells(X, 8).Value
End If
Next
'Alphabetic Order
For a = 0 To ComboBox1.ListCount - 1
For b = a To ComboBox1.ListCount - 1
If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
ComboBox1.List(a) = ComboBox1.List(b)
ComboBox1.List(b) = c
End If
Next
Next
With ListBox2
ListBox2.ColumnCount = 8
ListBox2.ColumnWidths = "70;80;80;80;80;80;95;70"
.Top = Me.ListBox1.Top - 30
.Height = 20
.Font.Bold = False
.Font.Name = "Tahoma"
.Font.Size = 8
.List = Sheets("Attendance").Range("A3:H3").Value
End With
UserForm4.Height = 150
Application.ScreenUpdating = True
End Sub
