Need help clearing a listbox (I get errors) 1

[JESTAR]

Okay, I can clear listboxes normally with ListBox.Clear, or with a For loop removing items as it goes. But this one gives me some error message that I can't get past.

I have several textboxes: Height, Weight, Age etc. There's 1 listbox with 2 columns. The first column is for Names, the second filled with height; weight; age etc. depending on which textbox is entered. So if you enter TextHeight, then ListRange will have...

Bob 5"9'
Bill 6"0'
Frank 5"11'

etc.

If you enter TextWeight, the listbox should clear, then be filled in with the weights (as displayed above). The data is taken from a spreadsheet by reading from the different columns, that much is fine. But I can't get the Listbox to empty between entering/exiting each different textbox without error. Without clearing, it just keeps adding the data to the bottom and the list gets huge. Here's my code....

Private Sub TextHeight_Enter()
Dim Row, I as Integer
Public Data as Object

Set Data = Sheets("Sheet1&quot.Cells

ListRange.Clear

LabelRange.Caption = "Height range"
ListRange.Visible = True

Row = 2
While Not IsEmpty(Data(Row, 1))
ListRange.AddItem (Data(Row, 1))
ListRange.Column(1, I) = (Data(Row, 4))

I = I + 1
Row = Row + 1
Wend

End Sub

That will give the error : "Could not set column property. Invalid property array index."

I also tried using....

Dim Total as Integer
Total = ListRange.ListCount
For I = 1 to Total
ListRange.Removeitem 0
Next I

.... in place of the ListRange.Clear, but get the same error. I've tried it in the TextBox_Enter(), TextBox_Exit() things, but always it messes up.

Any ideas appreciated

 

[BruceG]

When removing multiple items from a listbox in a loop, you must loop BACKWARDS. Try modifying your last example as follows:

Dim Total as Integer
Total = ListRange.ListCount
For I = Total to 1 Step -1
ListRange.Removeitem I
Next I

 

[JESTAR]

YES, many thanks Bruce, you've ended 2 days of violent computer abuse. BTW the it's "ListRange.RemoveItem 0"

 

[BruceG]

No problem. I'm a "straight VB" programmer, so I'm used to slightly different syntax.