CODEWITHSUNDEEP
vbaexcellistbox
SandPiper
SandPiper

Reputation: 2906

Excel Listbox inconsistently has no value after setting the Listindex

I have a simple UserForm with two Listboxes and two Textboxes that contain the value of the currently selected Listitem. I am having a bizarre bug where one of the boxes will not populate when the form loads. If I close the form and load it again, then the opposite box will not load. If I close and reload a third time, then it loads the first box but not the second again. Repeat ad nauseum.

First Load:

First Textbox loads

Second Load:

Second Textbox loads

The code should have both Textboxes populated at startup. What is the source of this bug?

Private Sub UserForm_Initialize()
    Dim i As Long

    For i = 1 To 4
        ListBox1.AddItem "Item A - " & i
    Next i
    ListBox1.ListIndex = 0

End Sub


Private Sub ListBox1_Change()

    Dim i As Long

    ListBox2.Clear
    For i = 1 To 3
        ListBox2.AddItem "Item B - " & i
    Next i
    ListBox2.ListIndex = 0

    TextBox1.Value = ListBox1.Value
End Sub

Private Sub ListBox2_Change()
    TextBox2.Value = ListBox2.Value
End Sub

Upvotes: 1

Views: 2600

Answers (1)

Wookies-Will-Code
Wookies-Will-Code

Reputation: 735

This answer is from Yow3Ek as much as from anyone. This code runs as tested without error or previous problem. Thanks guys, I learned something today. It was firing on the clear.

Private Sub UserForm_Initialize()
    Dim i As Long

    For i = 1 To 4
        Me.ListBox1.AddItem "Item A - " & i
    Next i
    Me.ListBox1.ListIndex = 0

End Sub


Private Sub ListBox1_Change()

    Dim i As Long

    Me.ListBox2.Clear
    For i = 1 To 3
        ListBox2.AddItem "Item B - " & i
    Next i
    Me.ListBox2.ListIndex = 0


    Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex)
End Sub

Private Sub ListBox2_Change()
    If Me.ListBox2.ListIndex = -1 Then Exit Sub
    Me.TextBox2.Value = Me.ListBox2.List(Me.ListBox2.ListIndex)
End Sub

Upvotes: 3

Related Questions