ListBox on a form with vbModeless

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
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)?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
I'm reading from a worksheet range (also Excel 2000).
Code:
    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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You probably need to include a sheet reference, otherwise the ActiveSheet will be used:

Code:
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

Board Regular
Joined
Mar 3, 2002
Messages
194

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Skippy said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,673
Messages
5,573,594
Members
412,537
Latest member
Mohamed_5966
Top