Return data to userform via two criterias

[LearningEX]

hi all, the picture below is what i want to achieve. i want to be able to select a date from the date picker, then select a truck for that date as i may have two trucks out on that date, then based on the two criteria, i then want the information returned to the listboxes like the picture.

Private Sub txtTourDate_AfterUpdate()
100: 'Check to see if value exists
101: If WorksheetFunction.CountIf(Sheet12.Range("C:C"), Me.txtTourDate.Value) = 0 Then
102: MsgBox "This date is Not Found"
103: Me.txtTourDate.Value = ""
104: Exit Sub
105: End If
106: 'Lookup values based on first control
107: With Me
108: .CboTrucks = Application.WorksheetFunction.VLookup(CLng(Me.txtTourDate), Sheet12.Range("Lookup"), 2, 0)
109: .Label3 = Application.WorksheetFunction.VLookup(CLng(Me.txtTourDate), Sheet12.Range("Lookup"), 14, 0)
110: .Label6 = Application.WorksheetFunction.VLookup(CLng(Me.txtTourDate), Sheet12.Range("Lookup"), 20, 0)
111: .Label10 = Application.WorksheetFunction.VLookup(CLng(Me.txtTourDate), Sheet12.Range("Lookup"), 23, 0)
112: .Label13 = Application.WorksheetFunction.VLookup(CLng(Me.txtTourDate), Sheet12.Range("Lookup"), 26, 0)
113:
114: End With
End Sub

 

[dmt32]

txtTourDate is a Datepicker does that make a difference again? also after adding your code my combobox is blank and does not show registrations

Code should show Trucks that match selected date in the range - It may be that you will need to use the change event for the control to call the code
Personally, I tend to avoid using datepicker control as it is as far as I am aware, not available of all versions / systems. Certainly not mine (64bit)
what I would be inclined to do would be to build a list of available dates in the range & populate a combobox where user can select from.

Dave

 

[LearningEX]

Code should show Trucks that match selected date in the range - It may be that you will need to use the change event for the control to call the code
Personally, I tend to avoid using datepicker control as it is as far as I am aware, not available of all versions / systems. Certainly not mine (64bit)
what I would be inclined to do would be to build a list of available dates in the range & populate a combobox where user can select from.

Dave

so are you saying use a named range to populate a text box?

 

[dmt32]

so are you saying use a named range to populate a text box?

No - I would extract a list of available dates in the Range to populate a combobox - this way, only valid dates can be selected.

Dave

 

[LearningEX]

Dave

i did what you suggested. but, ive never come across this before

 

[LearningEX]

i did what you suggested. but, ive never come across this before

sorted this, but the truck reg is not populating

 

[dmt32]

i did what you suggested. but, ive never come across this before

Don't use RowSource property of the control - Build an array of dates & then use that to populate the control using the List property.

Dave

 

[LearningEX]

Don't use RowSource property of the control - Build an array of dates & then use that to populate the control using the List property.

Dave

great. thank you for the reply, only one small problem.....i dont know how to do that lol

 

[dmt32]

great. thank you for the reply, only one small problem.....i dont know how to do that lol

Try following updated codes

Private Sub CboTrucks_Change()
    FindTruck Me.txtTourDate.Value, Me.CboTrucks.Value
End Sub

Private Sub txtTourDate_Change()
     FindTruck Me.txtTourDate.Value, Me.CboTrucks.Value, True
     Me.CboTrucks.Enabled = Len(Me.txtTourDate.Value) > 0
End Sub

Sub FindTruck(ByVal TourDate As Variant, ByVal Truck As String, Optional ByVal AddTrucks As Boolean)
    Dim FoundCell As Range, SearchRange As Range
    Dim FirstAddress As String
    
     If Not IsDate(TourDate) Then Exit Sub
    
    Set SearchRange = Sheet12.Columns(3)
    
    With Me.CboTrucks
    If AddTrucks Then .RowSource = "": .Clear
    End With
           
    Set FoundCell = SearchRange.Find(CStr(TourDate), LookIn:=xlFormulas, lookat:=xlWhole)
    
    If Not FoundCell Is Nothing Then
        FirstAddress = FoundCell.Address
        Do
            With FoundCell
             If AddTrucks Then
                    Me.CboTrucks.AddItem .Offset(, 1).Value
                Else
                If .Offset(, 1).Value = Truck Then
                    Me.Label3 = .Offset(, 14).Text
                    Me.Label6 = .Offset(, 20).Text
                    Me.Label10 = .Offset(, 23).Text
                    Me.Label13 = .Offset(, 26).Text
                    Exit Sub
                End If
                End If
            End With
            Set FoundCell = SearchRange.FindNext(FoundCell)
            If FoundCell Is Nothing Then Exit Do
        Loop Until FoundCell.Address = FirstAddress
         Me.CboTrucks.Enabled = True
    Else
        MsgBox TourDate & Chr(10) & "This date is Not Found", 48, "Not Found"
        Me.CboTrucks.Enabled = False
    End If
    
End Sub

Private Sub UserForm_Initialize()
'**********************************************************************
'******DO NOT RENAME THIS EVENT TO MATCH YOUR USERFORM NAME************
    
    Me.txtTourDate.List = GetList(Sheet12, 3)
    
End Sub

Function GetList(ByVal ws As Object, ByVal WhichColumn As Long) As Variant
    Dim Cell As Range, Rng As Range
    Dim Text As String

    Set Rng = ws.Cells(2, WhichColumn).Resize(ws.Cells(ws.Rows.Count, WhichColumn).End(xlUp).Row)
    
    For Each Cell In Rng.Cells
        Cell.Value = Trim(Cell.Value)
        If Len(Cell.Value) > 0 Then
            If Len(Text) = 0 Then
                Text = Trim(Cell.Value)
            ElseIf InStr(1, Text, Cell.Text) = 0 Then
                Text = Text & "," & Trim(Cell.Text)
            End If
        End If
    Next Cell
    GetList = CVar(Split(Text, ","))
End Function

I have created a Function that will build an array of Unique list of Dates (no duplicates) in specified range to populate your combobox.


Dave

 

[LearningEX]

i get this error popup

 

[LearningEX]

lol my mistake was my error all working as it should. thank you thank you thank you for all you help