ListBox - Value List as Row Source Type

[onefootswill]

Hi There,

This is not a huge problem, but it has me intrigued. I am new to VBA and am experimenting with populating/depopulating listboxes using Value List as the Row Source Type. Here's the idea:

When I click the Empty Listbox button, i get the following error:

The code for that button is:

Private Sub cboEmpty_Click()

For intListRow = 0 To lboListCtl.ListCount - 1
lboListCtl.RemoveItem (intListRow)
Next intListRow

lboListCtl.AddItem Item:=header, Index:=0

Can anyone identify why it cannot find the 6th item in the list?

 

[Zigzag]

Hi,

For a test, what happens if you replace

For intListRow = 0 To lboListCtl.ListCount - 1

with

For intListRow = 0 To 10

just to see if it works then or still fails on No6

Garry

 

[onefootswill]

I can confirm that it still fails on 6 when I change the For loop to:

For intListRow = 0 To 10

It is really quite weird. The easy workaround is:

'lboListCtl.RowSourceType = "Table/View/StroedProc"
'lboListCtl.RowSource = ""
'lboListCtl.RowSourceType = "Value List"

But I am still intrigued as to why it fails on 6 with the RemoveItem approach.

 

[OlcayM]

Hi,

Change the line
"lboListCtl.RemoveItem (intListRow)"
with this line
"lboListCtl.RemoveItem (0)"

HTH
OlcayM

 

[gemma-the-husky]

i thnik vba doesnt allow you to add/remove items from lists in this way, a it is query based

to fill a list other than via a query, you need to look at callback functions

 

[onefootswill]

OlcayM, your suggestion did the trick.

The new code is:

For intListRow = 0 To lboListCtl.ListCount - 1
lboListCtl.RemoveItem (0)
Next intListRow

So with every iteration, you take the bottom item (index 0) out. I experimented a bit and that is how is seems to work. Each time you remove index 0, they all shuffle up one.

Thanks.