Listbox list of items 'disappears'

Question

I require a listbox to hold items temporarily. Then, when an item is selected, it is used elsewhere and needs to be removed from the list, with the other items being moved up the list to fill the gap. To manipulate the list, I copy it to a temporary range of cells, modify it and then reload it into the listbox. When it is loaded back into the listbox, the listbox does not show any of the items. They are in it, as, if the list is copied back to a range, the items are displayed correctly in the range. The problem only occurs when a list item has been selected and used in the listbox click event. I am very new to using VBA in Excel and would much appreciate help. What am I doing wrong?

I created a very small spreadsheet with a short list in a range of cells, and two form controls: a listbox and a command button. Each of the two controls had a click event. The command button copied the list into the listbox and then ran a short subroutine. The listbox click event copied the selected item into a cell and then ran the same short subroutine. The subroutine simply copied the listbox list to a second range of cells, cleared the listbox, and then reloaded the listbox from the second range of cells. After this, the subroutine cleared the second range of cells, and then copied the listbox list into them again. When the command button was clicked, all worked as expected, but when the listbox was clicked, the selected item appeared in the cell but the listbox list disappeared, so there was no way to select any of the other items.

The code used was this:

Private Sub cmdInsertList_Click()

    lbxTest.List = Range("I1:I7").Value
    Change_List
    
End Sub

Private Sub lbxTest_Click()

    Range("$F$8").Value = lbxTest.Value
    Change_List
    
End Sub

Private Sub Change_List()

    Range("K1:K7").Clear
    Range("K1:K7").Value = lbxTest.List
    lbxTest.Clear
    lbxTest.List = Range("K1:K7").Value
    lbxTest.ListIndex = -1
    Range("K1:K7").Clear
    Range("K1:K7").Value = lbxTest.List
    
End Sub

Answer 1

Edit

After reviewing i suspect that the issue is with the Clear method during an event handling of the listbox. I also found that all the data are stored correctly in the Listbox, this means that ListCount and List(x) displays the correct values, but on the screen in the listbox it is not visible. Suspect, that the deletion of the rows during the Click event, disturb the process. But if use AddItem iterating through the range will work.

Private Sub Change_List()

    Range("K1:K7").Clear
    Range("K1:K7").Value = lbxTest.List
    lbxTest.Clear
    For i = 1 To 7       
    lbxTest.AddItem (Range("K1:K7")(i))
    Next i
    lbxTest.ListIndex = -1
    Range("K1:K7").Clear
    Range("K1:K7").Value = lbxTest.List

End Sub

Supplement

Further debugging results:

• Changing different parameters of controls invokes different events for the control
• This can yield recursive calls of events if the modifications are made inside a control's event handler.

As a consequence try the avoid using modifications of the controls properties within their event handler subs.

This can be avoided if all the changes are made outside of the userform's methods.

1. Set the default parameters of the controls to a userform
2. Apply Show method.
3. Inside the event procedures use flags to mark changes made if necessary.
4. Apply Hide method.
5. Make changes to the properties and contents of the controls in the main method.
6. Apply Show method to display changes on the form.

In this question this is the Clear method.

To resolve this here is an approach

This is the main routine in a module (can be embed in a loop and test a Button to quit.)

Sub formact()

UserForm1.Show
UserForm1.ListBox1.Clear
UserForm1.ListBox1.List = Range("K1:K7").Value
UserForm1.ListBox1.ListIndex = -1
UserForm1.Show

End Sub

Within the Userform1 code module leave out the "change_list" call, and insert the Hide method

Private Sub ListBox1_Click()

    UserForm1.Hide
    Range("$F$8").Value = ListBox1.Value
    'change_list

End Sub