'Overflow' Error when looping through listbox

[JoeMicro]

hi:
what i m trying to do is as follows:

i have got a user form on an excel sheet, where i could select several lines, for each line sheet1 is being filtered, criteria1 is the line criteria2 is a textbox, copied onto another sheet and printed out, deselect the line, and move on to the next line, and do the same,

my code is:

Private Sub CheckBox1_Click()
Dim iloop As Integer

For iloop = 1 To ListBox1.ListCount
 ListBox1.Selected(iloop - 1) = CheckBox1.Value
Next
End Sub

Private Sub CommandButton2_Click()
    Dim iloop As Integer
    
    [COLOR=red]For iloop = 1 To ListBox1.ListCount[/color] 
        If ListBox1.Selected(iloop - 1) = True Then
            Call stmt
            ListBox1.Selected(iloop - 1) = False
        End If
    Next
End Sub

Sub stmt()

    Dim rngFiltered As Range

    Application.ScreenUpdating = False
   'Select Sheet To filter
   With Sheets("records")
    With .Range("A1").End(xlUp)
        .AutoFilter
        'Select Criteria 1 & 2
        .AutoFilter Field:=2, Criteria1:=ListBox1.List(iloop - 1, 0)
        .AutoFilter Field:=1, Criteria1:=">=" & DTPicker3
    End With
   End With
    'Copy Filterd Data on to Statement Sheet
    Set rngFiltered = Sheets("records").Range("A1").End(xlDown).SpecialCells(xlCellTypeVisible)
    rngFiltered.Copy Worksheets("sheet1").Range("a1")
    'Clear Clumn B
    Sheets("sheet1").Range("B:B").ClearContents
    'Enter Customer Name
    Sheets("sheet1").Range("A5").Value = ListBox1.List(iloop - 1, 0)
    'Clear Filter's on Records Sheet
    Sheets("records").ShowAllData
    
    Sheets("sheet1").PrintOut
    Range("G1").Select
    
    Application.ScreenUpdating = True

End Sub

but then i get a "RunTime Error '6': 'Overflow' and it highlights line 15

any help would be appreciated

Thank you for your valuable time

 

[GlennUK]

And when in debug mode you might want to test what ListBox1.ListCount is at that point. ( use the immediate window maybe )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Listbox indexes start at 0

so

For iloop = 1 To ListBox1.ListCount

needs to be

For iloop = 0 To ListBox1.ListCount -1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JoeMicro]

Thanks for replying.

as for - GlannUK: it gives me

ListBox1.ListCount=65536"

and for - xlbo: i did as you wrote but still the same thing.


the list box has a range in the sheet assigned to it as the "row source", and it's full with empty boxes because the range on the sheet is supposed to gate gradually fuller as data is being entered, (as i saw the info debug gave me i thought this info might help)

 

[rmikesmith]

The Overflow error is occurring because a variable declared as an integer has a maximum value of 32767 in VB/VBA. Do you really have 65536 entries in your Listbox?? Far too many. Since that is also the number of rows on a worksheet (XL versions prior to 2007) I'm wondering whether you have somehow populated this ListBox from all possible rows in a worksheet column.


Regards,
Mike

 

[JoeMicro]

that's exactly the case, the named range assigned to the list box includes the whole column.

thanks

 

[rmikesmith]

OK. I'll reiteriate my point about 65536 entries in a ListBox being far too many. Not only would it be painful to use but it's asking for trouble. Can I assume you don't actually have that many items to assign? If so, then you need adjust the named range to take into account only the rows with data. If for some reason you actually do have that many items and want to keep it that way, then you'll need to DIM iloop as a Long.


Regards,
Mike

 

[ck1999]

You could use:


nrange = "helpme" 'defined name of row source for listbox
ActiveWorkbook.names.Add Name:=nrange, RefersToR1C1:= _
"=Sheet1!R20c7:r" & Range("g20").End(xlDown).Row & "c7"
ListBox1.RowSource = nrange

This uses data from G20 down until the last entry in column G

place this in your activate procedure of the userform

This is I think what rmikesmith was talking about

ck1999

 

[JoeMicro]

Thank you "rmikesmith" that simply did the job, i did

 dim iloop as long

and i don't get that error (i do have a few small other problems, which if i cant fix it, i will address it in another tread)

and thanks "ck1999" that's actually a good idea, but i think it's different from what mike was talking about.

thank you again.

 

[xlbo]

JoeMicro - ck1999's post is exactly what Mike was talking about. Please don;t use an entire column as the row source for your listbox - you will be hated by your users!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question