I have another form here that displays data of Sheet1 during Initialize. When there's Listbox item selection or Listbox afterupdate event, listbox returns values from Sheet2 of selected Digits (unique with a letter D) in the text fields:
Sheet1
A | B | C | D |
---|---|---|---|
Digits | Date1 | Date2 | Version |
D-12300 | 3/16/2025 | 3/16/2025 | 1-50-02 |
D-12347 | 3/17/2025 | 3/17/2025 | 1-50-03 |
D-12348 | 3/18/2025 | 3/18/2025 | 1-50-04 |
Sheet2
A | B | C | D |
---|---|---|---|
Digits | Description | Date | Version |
D-12345 | Description1 | 2/15/2025 | 1-50-01 |
D-12346 | Description1 | 3/16/2025 | 1-50-02 |
D-12347 | Description2 | 3/17/2025 | 1-50-03 |
D-12348 | Description3 | 3/18/2025 | 1-50-04 |
D-12349 | Description1 | 3/19/2025 | 1-50-05 |
Listbox1
Private Sub ListBox1_AfterUpdate()
Me.txtSheet1Digits.Value = ""
Me.txtSheet1Date1.Value = ""
Me.txtSheet1Date2.Value = ""
Me.txtSheet1Version.Value = ""
Me.txtSheet1Digits = ListBox1.Column(0)
Me.txtSheet1Date1 = ListBox1.Column(1)
Me.txtSheet1Date2 = ListBox1.Column(2)
Me.txtSheet1Version = ListBox1.Column(3)
Matches
End Sub
A snippet code used from previous post:
Sub Matches()
Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
Dim i As Long
Dim arr: arr = ws2.Range("A1").CurrentRegion.Value
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim targetDate As Variant
Dim targetVersion As Variant
For i = 2 To UBound(arr)
dict(arr(i, 1)) = Empty
Next
If dict.exists(Me.txtSheet1Digits.Value) Then
targetDate = Application.VLookup(Me.txtSheet1Digits.Value, ws2.Range("B1").CurrentRegion.Value, 3, False)
targetVersion = Application.VLookup(Me.txtSheet1Digits.Value, ws2.Range("B1").CurrentRegion.Value, 4, False)
Me.txtSheet2Date = targetDate
Me.txtSheet2Version = targetVersion
End If
If Not Me.txtSheet1Date1 = Me.txtSheet2Date Or _
Not Me.txtSheet1Version = Me.txtSheet2Version Then
labelSheet1Date1.ForeColor = vbRed
txtSheet1Date1.ForeColor = vbRed
labelSheet1Date2.ForeColor = vbRed
txtSheet1Date2.ForeColor = vbRed
labelSheet2Date.ForeColor = vbRed
txtSheet2Date.ForeColor = vbRed
labelSheet1Version.ForeColor = vbRed
txtSheet1Version.ForeColor = vbRed
labelSheet2Version.ForeColor = vbRed
txtSheet2Version.ForeColor = vbRed
Else
labelSheet1Date1.ForeColor = vbBlack
txtSheet1Date1.ForeColor = vbBlack
labelSheet1Date2.ForeColor = vbBlack
txtSheet1Date2.ForeColor = vbBlack
labelSheet2Date.ForeColor = vbBlack
txtSheet2Date.ForeColor = vbBlack
labelSheet1Version.ForeColor = vbBlack
txtSheet1Version.ForeColor = vbBlack
labelSheet2Version.ForeColor = vbBlack
txtSheet2Version.ForeColor = vbBlack
End If
End Sub
I am just wondering what could be the error below:
When I click the first item of the listbox, it reads the first If condition, and nothing matches in Sheet2 which is correct and returns blanks to two textfields. Next it reads the second condition where it goes all red - correct.
When I click the second item of the listbox, same reading and it goes now to else condition of the second If condition so it's all black - correct.
Now, when I click back again to the first item, it is reading the same pattern but does not returning blanks to two textfields anymore. 2 textfields with arrows keep having the values from the previously selected items - wrong. It should be returning blanks.
Adding Else
clause to clear the textbox if nothing matches in Sheet2.
If dict.exists(Me.txtSheet1Digits.Value) Then
targetDate = Application.VLookup(Me.txtSheet1Digits.Value, ws2.Range("B1").CurrentRegion.Value, 3, False)
targetVersion = Application.VLookup(Me.txtSheet1Digits.Value, ws2.Range("B1").CurrentRegion.Value, 4, False)
Me.txtSheet2Date = targetDate
Me.txtSheet2Version = targetVersion
Else
Me.txtSheet2Date = ""
Me.txtSheet2Version = ""
End If