How to correctly initialize and extract Listbox Value - Userform VBA

Question

I have a Userform in which I want the user to select a word in each of two listbox and then save them in a sheet. The user can also (in theory) leave the pre-selected word. Here's the code I have written :

Private Sub UserForm_Activate ()

With ListBox1 'This list is about the stake
    .AddItem "Essential"
    .AddItem "Important"
    .AddItem "Not interesting"
End With
'then I try to initialize the value of the stake. 
ListBox1.Value = "Important"

With ListBox2 'This is a second list, about priority
    .AddItem "Auto"
    .AddItem "Yes"
    .AddItem "No"
End With
'then I try to initialize the value of the priority
Listbox2.Value="Yes"

End Sub ()

But my problem is that, even though the two list seem to have been initialize correctly (correct word highlighted in the list when I run the UserForm), I can't extract the value of one of the list. When I run the following code :

Private Sub CommandButton2_Click()
    Feuil1.Cells(1,1)=Listbox1.Value
    Feuil1.Cells(1,2)=Listbox2.Value
End sub ()

Excel is able to extract the value of Listbox2 (Priority) : "Yes" but not the value of Listbox1 (Stake) : "Important". And I don't get why the code would work for one but not the other!

One more element : if I manually select a word in the list, then Excel is able to give me the value of both listbox.

Any clue?

Answer 1

How to get the current value of a ListBox

Seems that the .Value property recognizes the correct list row, but doesn't react to the second listbox unless it gets focus or is activated manually. So a brute (and not recommendable) work around would be to set focus each time you have to get the current value of the 2nd listbox, too. (This seems to be nerve-racking btw and ressembles to a certain extent to permanently selecting or activating cells instead of recommended direct referencing fully qualified ranges.)

'...
Me.ListBox2.SetFocus
Feuil1.Cells(1, 2) = Me.ListBox2.Value

You are on the sure side, however using the listboxes' .List property. .ListIndex as first argument indicates the current "row" by a zero-based index (0 equals row 1, 1 the 2nd one row, etc.); the 2nd argument 0 indicates the row index (i.e. column 1; btw the only one here).

Private Sub CommandButton2_Click()
Feuil1.Range("A1:B1") = vbNullString
With Me.ListBox1
    If .Listindex >-1 then Feuil1.Cells(1, 1) = .List(.ListIndex, 0)
End With
With Me.ListBox2
    If .Listindex >-1 then Feuil1.Cells(1, 2) = .List(.ListIndex, 0)
End With