ComboBox Populating Data With Max Values

[reberryjr]

I have a form with a combobox, that is tied to a range on a worksheet. The range can have multiple duplicate values. When a user selects a value from a drop down box, I'd like them to see only 1 of the possible values, and have the data presented tied to the max date of the Update Date. Essentially, if the worksheet has 5 entries where the name = "Tom" and the Update Date is 5/1/17, 6/1/17, 7/8/17, 8/1/17, 10/1/17, I want the User to only see one instance of Tom in the combo box (along with the other names in there), and the data presented in the rest of the form to be tied to the record from 10/1/17.

I've searched this, and other sites, and haven't found anything that makes sense to me. That's probably b/c I'm still new to VBA, and trying to piece things together.

Private Sub cmd_Submit_Click()

Dim ws1 As Worksheet


Set ws1 = ThisWorkbook.Sheets("Client Measurements")


LastRow = ws1.Range("C" & Rows.Count).End(xlUp).Row + 1


ws1.Range("B" & LastRow) = Me.txt_Updated
ws1.Range("C" & LastRow) = Me.txt_First
ws1.Range("D" & LastRow) = Me.txt_Last
ws1.Range("E" & LastRow) = Me.txt_Suffix
ws1.Range("F" & LastRow) = Me.cobo_Name
ws1.Range("G" & LastRow) = Me.txt_EntryType
ws1.Range("H" & LastRow) = Me.txt_Height
ws1.Range("I" & LastRow) = Me.txt_Weight
ws1.Range("J" & LastRow) = Me.txt_Chest
ws1.Range("K" & LastRow) = Me.txt_Hips
ws1.Range("L" & LastRow) = Me.txt_Waist
ws1.Range("M" & LastRow) = Me.txt_BicepL
ws1.Range("N" & LastRow) = Me.txt_BicepR
ws1.Range("O" & LastRow) = Me.txt_ThighL
ws1.Range("P" & LastRow) = Me.txt_ThighR
ws1.Range("Q" & LastRow) = Me.txt_CalfL
ws1.Range("R" & LastRow) = Me.txt_CalfR




End Sub
Private Sub cobo_Name_DropButt*******()


Dim i As Long
Dim coll As Collection
Dim ws1 As Worksheet


Set ws1 = ThisWorkbook.Sheets("Client Measurements")


LastRow = Sheets("Client Measurements").Range("F" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow


If Sheets("Client Measurements").Cells(i, "F").Value = (Me.cobo_Name) Or _
Sheets("Client Measurements").Cells(i, "F").Value = Val(Me.cobo_Name) Then
    Me.txt_First = Sheets("Client Measurements").Cells(i, "C").Value
    Me.txt_Last = Sheets("Client Measurements").Cells(i, "D").Value
    Me.txt_Suffix = Sheets("Client Measurements").Cells(i, "E").Value
    Me.txt_Height = Sheets("Client Measurements").Cells(i, "H").Value
    Me.txt_Weight = Sheets("Client Measurements").Cells(i, "I").Value
    Me.txt_Chest = Sheets("Client Measurements").Cells(i, "J").Value
    Me.txt_Hips = Sheets("Client Measurements").Cells(i, "K").Value
    Me.txt_Waist = Sheets("Client Measurements").Cells(i, "L").Value
    Me.txt_BicepL = Sheets("Client Measurements").Cells(i, "M").Value
    Me.txt_BicepR = Sheets("Client Measurements").Cells(i, "N").Value
    Me.txt_ThighL = Sheets("Client Measurements").Cells(i, "O").Value
    Me.txt_ThighR = Sheets("Client Measurements").Cells(i, "P").Value
    Me.txt_CalfL = Sheets("Client Measurements").Cells(i, "Q").Value
    Me.txt_CalfR = Sheets("Client Measurements").Cells(i, "R").Value
    


End If
Next
End Sub


Private Sub UserForm_Initialize()


Dim ws1 As Worksheet
Dim cCMName As Range


Set ws1 = ThisWorkbook.Sheets("Client Measurements")


For Each cCMName In ws1.Range("CMName")
    With Me.cobo_Name
        .AddItem cCMName.Value
    End With
Next cCMName


txt_EntryType = "Check In"


End Sub

 

[Fluff]

Hi, this can be done, but I'll need some info.
What is the relationship between CMName & the Update date range?
Judging by the code, it looks like the named range is in col F on sheet Client Measurements. If so what column contains the date?
Also are the dates, just dates, or is it date & time?

 

[reberryjr]

Thank you for the reply Fluff! When a Client is onboarded, the Update Date is completed. Then, as they progress and check in, they will get another record added to the worksheet, so that progress comparisons can be done. Every CMName will have an Updated date (date only); which is in column B.

 

[Fluff]

Replace all the code you've posted with this

Option Explicit
Dim coboDict As Object

Private Sub UserForm_Initialize()


    Dim ws1 As Worksheet
    Dim cCMName As Range
    
    
    Set ws1 = ThisWorkbook.Sheets("Data List")
    
    Set coboDict = CreateObject("scripting.dictionary")
    With coboDict
        For Each cCMName In ws1.Range("CMName")
            If Not .exists(cCMName.Value) Then
                .Add cCMName.Value, cCMName.Row
            Else
                If cCMName.Offset(, -4).Value > Range("B" & .Item(cCMName.Value)) Then
                    .Item(cCMName.Value) = cCMName.Row
                End If
            End If
        Next cCMName
        Me.cobo_Name.list = Application.Transpose(.keys)
    End With
    txt_EntryType = "Check In"
End Sub

Private Sub cobo_Name_Change()

    With Sheets("Data List")
        Me.Txt_First = .Cells(coboDict.Item(Me.cobo_Name.Value), "C").Value
        Me.txt_Last = .Cells(coboDict.Item(Me.cobo_Name.Value), "D").Value
        Me.txt_Suffix = .Cells(coboDict.Item(Me.cobo_Name.Value), "E").Value
        Me.txt_Height = .Cells(coboDict.Item(Me.cobo_Name.Value), "H").Value
        Me.txt_Weight = .Cells(coboDict.Item(Me.cobo_Name.Value), "I").Value
        Me.txt_Chest = .Cells(coboDict.Item(Me.cobo_Name.Value), "J").Value
        Me.txt_Hips = .Cells(coboDict.Item(Me.cobo_Name.Value), "K").Value
        Me.txt_Waist = .Cells(coboDict.Item(Me.cobo_Name.Value), "L").Value
        Me.txt_BicepL = .Cells(coboDict.Item(Me.cobo_Name.Value), "M").Value
        Me.txt_BicepR = .Cells(coboDict.Item(Me.cobo_Name.Value), "N").Value
        Me.txt_ThighL = .Cells(coboDict.Item(Me.cobo_Name.Value), "O").Value
        Me.txt_ThighR = .Cells(coboDict.Item(Me.cobo_Name.Value), "P").Value
        Me.txt_CalfL = .Cells(coboDict.Item(Me.cobo_Name.Value), "Q").Value
        Me.txt_CalfR = .Cells(coboDict.Item(Me.cobo_Name.Value), "R").Value
    End With


End Sub

Private Sub cmd_Submit_Click()

Dim ws1 As Worksheet


Set ws1 = ThisWorkbook.Sheets("Client Measurements")


lastrow = ws1.Range("C" & Rows.Count).End(xlUp).Row + 1


ws1.Range("B" & lastrow) = Me.txt_Updated
ws1.Range("C" & lastrow) = Me.Txt_First
ws1.Range("D" & lastrow) = Me.txt_Last
ws1.Range("E" & lastrow) = Me.txt_Suffix
ws1.Range("F" & lastrow) = Me.cobo_Name
ws1.Range("G" & lastrow) = Me.txt_EntryType
ws1.Range("H" & lastrow) = Me.txt_Height
ws1.Range("I" & lastrow) = Me.txt_Weight
ws1.Range("J" & lastrow) = Me.txt_Chest
ws1.Range("K" & lastrow) = Me.txt_Hips
ws1.Range("L" & lastrow) = Me.txt_Waist
ws1.Range("M" & lastrow) = Me.txt_BicepL
ws1.Range("N" & lastrow) = Me.txt_BicepR
ws1.Range("O" & lastrow) = Me.txt_ThighL
ws1.Range("P" & lastrow) = Me.txt_ThighR
ws1.Range("Q" & lastrow) = Me.txt_CalfL
ws1.Range("R" & lastrow) = Me.txt_CalfR




End Sub

 

[reberryjr]

Thank you again Fluff! I've copied the code, changing "Data List" to the actual worksheet name. It does bring over the unique value into the combo box, but it's not presenting the record with the MAX Update Date.

 

[Fluff]

oops, missed a bit

If cCMName.Offset(, -4).Value > [COLOR=#ff0000]Ws1.[/COLOR]Range("B" & .Item(cCMName.Value)) Then

add the part in red.
Also was I correct in thinking that the named range CMName is in col F?

 

[reberryjr]

I incorporated the worksheet reference in red, but the record with the max date still isn't coming over. Yes, you were correct on the location of the CMName field.

 

[Fluff]

Are you getting values in the txtboxes, just not from the latest date, or are they blank?

 

[reberryjr]

Yes, data is coming in. I have test data with 3 different dates. The data coming into the text boxes is from the middle date. It's almost as if it's seeing the min date and stopping at the next highest date.

 

[Fluff]

So if you had something like this. Which set of results would it be pulling in, 1,2, or 3?

Excel 2013 32 bit
	B	C	F
21	21/11/2017	1	Fluff
22	20/11/2017	2	Fluff
23	22/11/2017	3	Fluff
Data List