Selection in ListBox on an Excel worksheet goes bl

Question

So, I have an ActiveX ListBox control named ListBox1 on Sheet1 of my Excel Workbook. I enabled multiple selections on it.

I also put some code in the Sheet1 Object:

Private Sub ListBox1_Change()
    Debug.Print "hello world"
End Sub

Now, if I select three values in my listbox, I see "Hello world" three times in my immediate window. So I guess the Change event triggers correctly.

When I select any cell on the same sheet where my listbox is, and I do something with it (e.g. I type "ABCDE" in it, or I press Delete) the selection I made in the listbox goes blank.

So if I had the first value in the list selected, and then I click cell "A1", type "Hello" in it and press Enter, the very moment I hit the key the first value is unselected from the listbox.

Why the hell is this? This is driving me crazy. Is the Listbox1_Change event not working properly?

It's funny though, because I don't see an extra "Hello world" in the immediate window, so I guess the event didn't actually trigger...

Any thought?

Answer 1

Bruder

As I mentioned earlier this is because the .Listfillrange for both the ListBox is resetting automatically.

You have specified a named range for your .Listfillrange and the formula for the named range is

For FiltroCliente

=IF(CollClientePicker<>1,OFFSET(DatiMaschera!$D$2,0,0,COUNTA(DatiMaschera!$D:$D)-1,1),"")

Now when you are making any change, Excel is re-calculating the named range because of which the .Listfillrange gets automatically updated.

Here is another way to check it.

Scroll your Listbox1 and select an item and then select an item in Listbox2. You will notice that the Listbox1 automatically scrolls to the top because it's .Listfillrange gets automatically updated.

SOLUTION

Instead of a Named Range in .Listfillrange, automatically fill the listbox using .Additem

SAMPLE CODE

'~~> Example : Add values from Cell A1:A10
For i = 1 To 10
    ListBox1.AddItem Sheets("Sheet1").Range("A" & i).Value
Next i

HTH

Sid