excelvbalistboxafter-update

How to return blank fields in textfields after listbox update in Excel VBA


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

Img1

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.

Img2

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.

Img3

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.

Img4


Solution

  • 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