Why is my ListBox so big?

Reset

Board Regular
Joined
Apr 16, 2010
Messages
227
I wrote some VBA code for Excel on a desktop. I tried to transfer it to a laptop but the ListBox keeps changing size. On startup the macro goes through all the sheets except for certain named ones. Then it goes row by row and if the cell in column 9 is blank it puts the value in the first column from that row into an array. When a sheet is done it goes to the sheet "dat" and puts the value, sheet name, and combo of the two in adjacent columns. For example: data "10" from Sheet "2011" gives a row with three values of "10" "2011" "10, 2011". The ListBox populates from the third column. But when it runs the ListBox resizes to height 15728625.75 and width 786245.25. I cannot see anything wrong and it works on the desktop. Does anyone know what is wrong?
Code:
Private Sub Workbook_Open()
Sheets("dat").Visible = True
Sheets("dat").Select
Range(Columns(1), Columns(3)) = ""
c = Sheets.Count
For Z = 1 To c
If Sheets(Z).Name = "Sheet1" Then GoTo line1
If Sheets(Z).Name = "dat" Then GoTo line1
If Sheets(Z).Name = "bunker" Then GoTo line1
Sheets(Z).Select
    a = Application.WorksheetFunction.CountA(Columns(1))
    b = Application.WorksheetFunction.CountA(Columns(9))
    
    If a - b < 1 Then GoTo line1
    ReDim q(a - b) As Integer
    inc = 0
    For x = 2 To a
        If Cells(x, 9) = "" Then q(inc) = Cells(x, 1): inc = inc + 1
    Next x
    Sheets("dat").Select
        v = Application.WorksheetFunction.CountA(Columns(1))
        For y = 1 To inc
            Cells(v + y, 1) = q(y - 1)
            Cells(v + y, 2) = Sheets(Z).Name
            Cells(v + y, 3) = Cells(v + y, 2) & ", " & Cells(v + y, 1)
        Next y
        
line1: 'jump out
Next Z
Sheets("dat").Visible = False
Sheets("Sheet1").Select
Cells(1, 1).Select
End Sub

For the record it is a Dell desktop running Vista and a HP laptop running Windows 7. Both using Excel 2007.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,224,521
Messages
6,179,289
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top