ListBox on a form with vbModeless

[Skippy]

I am a bit cofused about something. I have a ListBox on a form that I fill using RowSource. When I set UserForm1.Show with vbModeless, the data in the listbox starts disappearing when I scroll up and down the list. Setting the form to vbModal doesn't cause this problem. Any ideas why (or how I can prevent this problem using vbModeless)?

 

[Andrew Poulsom]

I can't reproduce that (Excel 2000). What is the RowSource?

 

[Skippy]

I'm reading from a worksheet range (also Excel 2000).

    Set rng = Sheets("Raw data Folio (2)").Range("CA2:CI" & Range("CA65536") _
        .End(xlUp).Row)
    With frmMain.lstSummary
        .ColumnCount = 9
        .RowSource = rng.Address
        cw = ""
        For c = 1 To 9
            cw = cw & rng.Columns(c).Width & ";"
        Next c
        .ColumnWidths = cw
        .ListIndex = 0
    End With
            
    With frmMain
        .Show vbModal
        .lblRecords = "No. of records: " & .lstSummary.ListCount
    End With

 

[Andrew Poulsom]

You probably need to include a sheet reference, otherwise the ActiveSheet will be used:

Private Sub UserForm_Initialize()
    With Sheets("Raw data Folio (2)")
        Set rng = .Range("CA2:CI" & .Range("CA65536").End(xlUp).Row)
    End With
    With frmMain.lstSummary
        .ColumnCount = 9
        .RowSource = "'Raw data Folio (2)'!" & rng.Address
        cw = ""
        For c = 1 To 9
            cw = cw & rng.Columns(c).Width & ";"
        Next c
        .ColumnWidths = cw
        .ListIndex = 0
    End With
End Sub

The same goes for the second part of your Range statement, so I put it in a With ... End With construct.

 

[Skippy]

Thanks Andrew. This was only a snippet of my code. What wasn't showing was that I had set the sheet to be active. So, while good that you pointed out a potential issue with my code, it is not related to my original problem.

 

[Andrew Poulsom]

Thanks Andrew. This was only a snippet of my code. What wasn't showing was that I had set the sheet to be active. So, while good that you pointed out a potential issue with my code, it is not related to my original problem.

Well, I still can't reproduce your original problem, sorry.

 

[Skippy]

Thanks just the same