On VBA (excel), is it possible to clear a listbox's display values without clearing the datasource?

Question

Here is how I establish the listbox:

Dim iRow As Long 
iRow = [counta(Tally!A:A)]
With TallySheet
If iRow > 1 Then
        .lstdatabase.RowSource = "Tally!A2:E" & iRow
        Else
        .lstdatabase.RowSource = "Tally!A2:E2"
        
        End If
End With

However the issue I am facing is that the only ways I have found of resetting the listbox display involve clearing the actual data stored in the excel sheet. I haven't been able to find any information on how to simply reset the display but leave the original values. Would I need to use two worksheets for this?

What I am aiming for is being able to reset the display without deleting the information stored in the excel sheet. I have tried things like Listbox1.Clear and ListBox1.RowSource = "" I also tried lstDatabase.RowSource = vbNullString which I found in another post, but it clears the whole data source instead of just clearing the list box.

Answer 1

I found what the problem is: You used a Form control (as opposed to a more usable ActiveX control) ListBox.

But no problem you can access it like this (I tested this code from a Module):

Public Sub TestSub()
    Dim iRow As Long: iRow = [counta(Tally!A:A)]
    iRow = 0 ' to test if emptying works
    With ThisWorkbook.Worksheets("Tally").Shapes("lstdatabase").ControlFormat
        If 1 < iRow Then
            .ListFillRange = "Tally!A2:E" & iRow
        Else
            .ListFillRange = ""
        End If
    End With
End Sub

You didn't mention but I suppose that not only emptying did not work but the line to fill it up (.lstdatabase.RowSource = "Tally!A2:E" & iRow) was wrong, as well.

So, setting ListFillRange to empty will clear the ListBox, however, you should think over the logic of your code: If you count entries in A:A, then you cannot clear the ListBox without clearing A:A. But that is not because its not possible just because you refer to A:A here: iRow = [counta(Tally!A:A)].

Also note that "Tally!A2:E" & iRow doesn't make much sense, only the first column is taken into consideration by Excel, so it should be "Tally!A2:A" & iRow.