I have a table with three columns,
ID, Name and Date
then I create a userform with textbox ID and Name.
how could I display the Name of similar ID from the table with latest Date when I key in the ID in the userform? (similar ID will have different names, but I want to display the one with latest date in the table)
thanks in advance for all the help
coding for the textbox1
Private Sub TextBox1_Change()
coding for the getdata module
If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
UserForm1.Controls("textbox" & j).Value = Cells(i + 1, j).Value
i = i + 1
If flag = False Then
For j = 2 To 3
UserForm1.Controls("TextBox" & j).Value = ""
This should do it for you. This routine goes in your userform code module:
Private Sub TextBox1_AfterUpdate() TextBox2 = Evaluate("=INDEX(B2:B999,MATCH(MAX((IF(A2:A999=" & TextBox1 & _ ",1)*(C2:C999)),1),IF(A2:A999=" & TextBox1 & _ ",1)*(C2:C999),))") End Sub
It assumes your data are in columns A, B, and C. It also assumes your data do not extend past row 999; if they do, then increase the 999's in the formula to what is appropriate.
TextBox1 is for the ID. TextBox2 is for the Name.
Note that this code is placed in the
AfterUpdate event procedure. This is different than your sample code. You used the Change event procedure. The difference is that Change fires on each keystroke while AfterUpdate fires only after the full text is confirmed for the textbox.
Note that you should still add error checking for the case where the ID is not numeric and also for the case where the numeric ID does not match. The code above is simply for demonstrating the technique to display the looked-up value. If you wish for me to flesh it out more, please let me know.
I went ahead and fleshed it out with the error checking:
Private Sub TextBox1_AfterUpdate() GetData End Sub Public Sub GetData() Dim v, w On Error Resume Next v = Evaluate("=INDEX(B2:B999,MATCH(MAX((IF(A2:A999=" & TextBox1 & _ ",1)*(C2:C999)),1),IF(A2:A999=" & TextBox1 & _ ",1)*(C2:C999),))") w = Evaluate("MAX((IF(A2:A999=" & TextBox1 & ",1)*(C2:C999)))") If IsArray(v) Or IsError(v) Then v = "ID not found.": w = "" TextBox2 = v TextBox3 = "": TextBox3 = CDate(w) End Sub
UPDATE 2 In the fleshed out version directly above, I added support for the associated date in TextBox3.
You could read the whole range in when the userform opens, sort it, then find the first ID.
Private mvaData As Variant Private Sub TextBox1_AfterUpdate() Me.TextBox2.Text = vbNullString Me.TextBox3.Text = vbNullString GetData End Sub Public Sub GetData() Dim i As Long For i = LBound(mvaData, 1) To UBound(mvaData, 1) If mvaData(i, 1) = Val(Me.TextBox1.Text) Then Me.TextBox2.Text = mvaData(i, 2) Me.TextBox3.Text = mvaData(i, 3) Exit For 'stop after the first one - largest date End If Next i End Sub Private Sub UserForm_Initialize() Dim i As Long, j As Long Dim lId As Long, sDesc As String, dtDate As Date 'store the data in a variable when the forms opens mvaData = Sheet1.Range("A1:C5") 'sort with larger dates on top For i = LBound(mvaData, 1) To UBound(mvaData, 1) - 1 For j = i To UBound(mvaData, 1) If mvaData(i, 3) < mvaData(j, 3) Then lId = mvaData(j, 1) sDesc = mvaData(j, 2) dtDate = mvaData(j, 3) mvaData(j, 1) = mvaData(i, 1) mvaData(j, 2) = mvaData(i, 2) mvaData(j, 3) = mvaData(i, 3) mvaData(i, 1) = lId mvaData(i, 2) = sDesc mvaData(i, 3) = dtDate End If Next j Next i End Sub