Listbox1.clear error


Posted by M Walker on August 08, 2001 6:44 AM

Hello,
Ivan kindly gave me this code which is intended to check a range of stock values and add them to a list if they are below 5. It all goes well until i run the macro and it appaers with the error on the Listbox1.clear command. I'm using excel 97, should this affect it at all? ANy body got any idea whats wrong??

Thanks in advance.

Matt


Dim Stocklevels As Range
Dim cell As Range
Dim vData() As Variant
Dim vDataCount As Double
Dim x As Double, y As Integer

Set Stocklevels = Sheets("Totals").Range("b4:b336")

ListBox1.Clear
ListBox1.ColumnCount = 2
vDataCount = Application.WorksheetFunction.CountIf(Stocklevels, "<5")
ReDim vData(vDataCount - 1, 1)

x = -1

For Each cell In Stocklevels

If cell <> "" Then
If cell.Value < 5 Then
x = x + 1
For y = 1 To 2
vData(x, 0) = cell.Offset(0, -1)
vData(x, 1) = cell
Next

End If
End If
Next

ListBox1.List = vData



Posted by Ivan F Moala on August 08, 2001 9:54 PM

Try adding

ListBox1.ControlSource = ""

before listbox1.clear ??


Ivan