ListBox - Value List as Row Source Type

O

onefootswill

New member

Local time

Today, 10:38

Joined

Nov 4, 2007

Messages

3

• Nov 4, 2007

• #1

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

Click to expand...

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

 

Zigzag

Registered User.

Local time

Today, 18:38

Joined

Aug 19, 2007

Messages

386

• Nov 4, 2007

• #2

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

 

O

onefootswill

New member

Local time

Today, 10:38

Joined

Nov 4, 2007

Messages

3

• Nov 4, 2007

• #3

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"

Click to expand...

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

 

O

OlcayM

Registered User.

Local time

Today, 20:38

Joined

Oct 30, 2007

Messages

47

• Nov 4, 2007

• #4

Hi,

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

HTH
OlcayM

 

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 18:38

Joined

Sep 12, 2006

Messages

15,956

• Nov 4, 2007

• #5

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

 

O

onefootswill

New member

Local time

Today, 10:38

Joined

Nov 4, 2007

Messages

3

• Nov 5, 2007

• #6

OlcayM, your suggestion did the trick.

The new code is:

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

Click to expand...

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.