Private Sub AddDataToListBox()
'Get the required data
Dim rg As Range
If ShRouteData.AutoFilterMode Then ShRouteData.AutoFilterMode = False
Set rg = GetRangeRoute
' Me.ComboBoxRoute.List = GetRouteAM()
' Me.ComboBoxRoute.ListIndex = -1
'Link Data to Listbox
With ListBoxRoutes
'.RowSource = rg.Address(external:=True)
.List = rg.Value
.ColumnCount = rg.Columns.Count - 1
.ColumnWidths = "25,110,50,200,40,0"
.ColumnHeads = False
.ListIndex = 0
End With
End Sub
Private Sub ComBAdd_Click()
Dim sh As Worksheet
Set sh = ShRouteData
Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))
'Validation
'**********************************************************
If Me.ComboBoxRoute.Value = "" Then
MsgBox "Select Route", vbCritical
Exit Sub
End If
If Me.ComboBoxWhen.Value = "" Then
MsgBox "Select Time", vbCritical
Exit Sub
End If
If Me.TextBoxPickDrop.Value = "" Then
MsgBox "Enter Pick Up / Drop Point", vbCritical
Exit Sub
End If
If Me.TextBoxROrder.Value = "" Then
MsgBox "Enter Route Order", vbCritical
Exit Sub
End If
If Not IsNumeric(TextBoxROrder) Then
MsgBox ("Enter a Number"), vbCritical
Exit Sub
End If
'********************************************************
'Add Data
sh.Range("A" & lr + 1).Value = "=row()-1"
sh.Range("b" & lr + 1).Value = Me.ComboBoxRoute.Value
sh.Range("c" & lr + 1).Value = Me.ComboBoxWhen.Value
sh.Range("d" & lr + 1).Value = Me.TextBoxPickDrop.Value
sh.Range("e" & lr + 1).Value = Me.TextBoxROrder.Value * 1
'Clear after adding
Me.ComboBoxRoute.Value = ""
Me.ComboBoxWhen.Value = ""
Me.TextBoxPickDrop.Value = ""
Me.TextBoxROrder.Value = ""
End Sub
Private Sub ComBClear_Click()
'Clear Fields
Me.ComboBoxRoute.Value = ""
Me.ComboBoxWhen.Value = ""
Me.TextBoxPickDrop.Value = ""
Me.TextBoxROrder.Value = ""
Me.TextBoxID.Value = ""
End Sub
Private Sub ComBClose_Click()
Unload Me
End Sub
Private Sub ComBDelete_Click()
If MsgBox("Are you sure?", vbOKCancel, "Confirm Delete") = vbOK Then
Call DeleteRouteRow(ListBoxRoutes.ListIndex)
End If
End Sub
Private Sub ComboBoxRoute_Change()
Call FilterData
End Sub
Private Sub ComboBoxWhen_Change()
Call FilterData
End Sub
Private Sub ComBUpdate_Click()
If Me.TextBoxID.Value = "" Then
MsgBox "Select A Record To Update By Double Clicking It", vbCritical
Exit Sub
End If
Dim sh As Worksheet
Set sh = ShRouteData
Dim Srow As Long
Srow = Application.WorksheetFunction.Match(CLng(Me.TextBoxID.Value), sh.Range("a:A"), 0)
'Validation
'**********************************************************
If Me.ComboBoxRoute.Value = "" Then
MsgBox "Select Route", vbCritical
Exit Sub
End If
If Me.ComboBoxWhen.Value = "" Then
MsgBox "Select Time", vbCritical
Exit Sub
End If
If Me.TextBoxPickDrop.Value = "" Then
MsgBox "Enter Pick Up / Drop Point", vbCritical
Exit Sub
End If
If Me.TextBoxROrder.Value = "" Then
MsgBox "Enter Route Order", vbCritical
Exit Sub
End If
If Not IsNumeric(TextBoxROrder) Then
MsgBox ("Enter a Number"), vbCritical
Exit Sub
End If
'********************************************************
'Add Data
sh.Range("b" & Srow).Value = Me.ComboBoxRoute.Value
sh.Range("c" & Srow).Value = Me.ComboBoxWhen.Value
sh.Range("d" & Srow).Value = Me.TextBoxPickDrop.Value
sh.Range("e" & Srow).Value = Me.TextBoxROrder.Value * 1
'Clear after adding
Me.ComboBoxRoute.Value = ""
Me.ComboBoxWhen.Value = ""
Me.TextBoxPickDrop.Value = ""
Me.TextBoxROrder.Value = ""
Me.TextBoxID.Value = ""
End Sub
Private Sub ListBoxRoutes_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Select Record for Editing
Me.TextBoxID.Value = Me.ListBoxRoutes.List(Me.ListBoxRoutes.ListIndex, 0)
Me.ComboBoxRoute.Value = Me.ListBoxRoutes.List(Me.ListBoxRoutes.ListIndex, 1)
Me.ComboBoxWhen.Value = Me.ListBoxRoutes.List(Me.ListBoxRoutes.ListIndex, 2)
Me.TextBoxPickDrop.Value = Me.ListBoxRoutes.List(Me.ListBoxRoutes.ListIndex, 3)
Me.TextBoxROrder.Value = Me.ListBoxRoutes.List(Me.ListBoxRoutes.ListIndex, 4)
End Sub
Private Sub OptBRouteWhen_Click()
Dim sh As Worksheet
Set sh = ShRouteData
If sh.Cells(Rows.Count, 1).End(xlUp).row = 1 Then
lr = 2
Else
lr = sh.Cells(Rows.Count, 1).End(xlUp).row
End If
'Sort Records by Route & When
Set SortA = sh.Range("a2:f" & lr)
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add2 key:=sh.Range("b1:b" & lr), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
sh.Sort.SortFields.Add2 key:=sh.Range("c1:c" & lr), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With sh.Sort
.SetRange SortA
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub OptBPickDrop_Click()
'
'Sort By Drop / Pick
Set sh = ShRouteData
If sh.Cells(Rows.Count, 1).End(xlUp).row = 1 Then
lr = 2
Else
lr = sh.Cells(Rows.Count, 1).End(xlUp).row
End If
Set SortA = sh.Range("a2:f" & lr)
sh.Sort.SortFields.Clear
sh.Sort.SortFields.Add2 key:=sh.Range("d1:d" & lr), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With sh.Sort
.SetRange SortA
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub UserForm_Initialize()
Call AddDataToListBox
Call UniqueValues
End Sub
Private Sub UniqueValues()
Dim dict, key
Dim lr As Long
lr = Application.WorksheetFunction.CountA(Range("A:A"))
'Select Unique Records from Routes
With ShRouteData.Range("b2:b" & lr)
dict = .Value
End With
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each key In dict
If Not .exists(key) Then .Add key, Nothing
Next
If .Count Then Me.ComboBoxRoute.List = Application.Transpose(.keys)
End With
'Select Unique Periods from When
With ShRouteData.Range("c2:c" & lr)
dict = .Value
End With
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each key In dict
If Not .exists(key) Then .Add key, Nothing
Next
If .Count Then Me.ComboBoxWhen.List = Application.Transpose(.keys)
End With
End Sub
Private Sub FilterData()
Dim route As String, when As String
Dim mydb As Range
With FrmRouteAdmin
route = IIf(.ComboBoxRoute.Value = "", "*", .ComboBoxRoute.Value)
when = IIf(.ComboBoxWhen.Value = "", "*", .ComboBoxWhen.Value)
End With
With ShRouteData
Set mydb = .Range("A1:F1").Resize(.Cells(.Rows.Count, 1).End(xlUp).row)
End With
With mydb
.AutoFilter
.AutoFilter Field:=2, Criteria1:=route
.AutoFilter Field:=3, Criteria1:=when
Call UpdateListBox(Me.ListBoxRoutes, mydb, 2)
.AutoFilter
End With
End Sub
Private Sub UpdateListBox(ListBoxRoutes As MSForms.ListBox, mydb As Range, Columntolist As Long)
Dim cell As Range, datavalues As Range
If mydb.SpecialCells(xlCellTypeVisible).Count > mydb.Columns.Count Then
Set datavalues = mydb.Resize(mydb.Rows.Count + 1)
ListBoxRoutes.Clear
For Each cell In datavalues.Columns(Columntolist).SpecialCells(xlCellTypeVisible)
With Me.ListBoxRoutes
.AddItem cell.Value
.List(.ListCount - 1, 1) = cell.Offset(0, 1).Value
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
.List(.ListCount - 1, 3) = cell.Offset(0, 3).Value
.List(.ListCount - 1, 4) = cell.Offset(0, 2).Value
.List(.ListCount - 1, 5) = cell.Offset(0, 3).Value
End With
Next cell
Else
ListBoxRoutes.Clear
End If
ListBoxRoutes.SetFocus
End Sub