Two ListBoxes - one returning an empty value

Question

I have a UserForm in VBA with two ListBoxes. Each box gets filled during UserForm_Initialize(). I use a loop for this, so that only text items are added to the UserForm and empty cells are ignored:

Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = 1 To 8
        If Sheets("Data").Cells(2 + i, 10) <> "" Then
            ListBox1.AddItem Sheets("Data").Cells(2 + i, 10)
        End If
    Next i

    For i = 1 To 7
        If Sheets("Data").Cells(11 + i, 10) <> "" Then
            ListBox2.AddItem Sheets("Data").Cells(11 + i, 10)
        End If
    Next i
End Sub

The user can either (single-) select one entry in each ListBox or alternatively use a button to load/select the previous values from two cells in a sheet:

Private Sub CommandButtonLoad_Click()
    ListBox1.Value = Sheets("Input").Cells(15, 1)
    ListBox2.Value = Sheets("Input").Cells(17, 1)
End Sub

Another button will then write the selected ListBox values in the (same) cells that contained the previous values.

Private Sub CommandButtonConfirm_Click()
    Sheets("Input").Cells(15, 1) = ListBox1.Value
    Sheets("Input").Cells(17, 1) = ListBox2.Value
End Sub

The problem: As long as I select the ListBox items by hand in the UserForm, everything works as intended an the third code will re-write the correct values (which are text by the way) in the cells (15, 1) and (17, 1). However, if I use the button (code 2) to fill the ListBoxes with previous values from the sheet, only one ListBox gets assigned with the correct value (output "Item XYZ"). The other one gets an empty value (output: "").

The issue vanishes when I use tab to select the ListBoxes in the UserForm. I guess this is equal to selecting the item with the mouse cursor.

I don't know how to solve this problem. Especially since one of the two ListBoxes works as intended. Anyone know the cause for this?

Edit: To clarify: Using the "Load" button, both values get correctly selected in the ListBoxes. But the assigned value of one ListBox ist empty (tested using msgbox ListBox1.value and msgbox ListBox2.value). One more thing: If I add ListBox1.SetFocus and ListBox2.SetFocus to CommandButtonLoad the problem gets solved. But I still don't know what caused it.

Is the other one a numeric value? If yes use Cstr(Sheets("Input").... — EvR
I cannot reproduce the issue. I added 2 listboxes and 2 buttons to a userform and added your code, if I press the confirm button the selected values get wirtten into the input sheet, if I press the load button, then in both listboxes the correct values are selected. I used A, B, C, … as example data and it all worked like expected. — Pᴇʜ
When you hit the CommandButtonLoad, do the correct values in the listboxes get highlighted/selected? — Stavros Jon
@horst no differece @ EvR Nope. All cells have the same format. @StavrosJon Nope. The value for Sheets("Input").Cells(15, 1) is correct ("Item XYZ"), but the value for ListBox1.Value ist wrong (""). — blowcake

EvR EvR · Accepted Answer · 2019-08-01T11:41:11

Try:

Private Sub CommandButtonConfirm_Click()

    Sheets("Data").Cells(15, 1) = ListBox1.List(ListBox1.ListIndex)
    Sheets("Data").Cells(17, 1) = ListBox2.List(ListBox2.ListIndex)

End Sub

Two ListBoxes - one returning an empty value

1 Answers