ListView Subitems messed up

X

xiioviii

New Member
Joined
Jul 17, 2014
Messages
2
Hello,

I've been trying to create a List View control in my User Form but I've been having trouble with the data. When the loop reaches List Item #4, subitems of #4 become subitems of #3.

Please help.

Code: 
Private Sub PopulateListViewCases()    Dim startRow As Integer 'beginning of data
    Dim lastRow As Integer 'end of data
    Dim actualRow As Integer 'actual row
    Dim lv_item As Integer 'no of the listview item
    
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("OpenCases")


    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    actualRow = 1
    lv_item = 1
    startRow = 2


    With lv_openCases
    
        .View = lvwReport
        .ListItems.Clear
        .HideColumnHeaders = False
        .Appearance = cc3D
        .FullRowSelect = True
        
        With .ColumnHeaders    'gives me headers at the top
            .Clear      'defining the columnheaders
            .Add , , "Date Received", 65
            .Add , , "Time Received", 65
            .Add , , "Name", 150
            .Add , , "Department", 75
            .Add , , "Request", 100
            .Add , , "Code", 30
            .Add , , "Time Left", 60
        End With


            
        For x = startRow To lastRow
            
            .ListItems.Add , , Worksheets("OpenCases").Cells(x, 1)


           
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("OpenCases").Cells(x, 2)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("OpenCases").Cells(x, 3)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("OpenCases").Cells(x, 4)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("OpenCases").Cells(x, 5)
                .ListItems(lv_item).ListSubItems.Add , , Worksheets("OpenCases").Cells(x, 6)
                .ListItems(lv_item).ListSubItems.Add , , Format(Worksheets("OpenCases").Cells(x, 9), "hh:mm")
        
            
            lv_item = lv_item + 1
            actualRow = actualRow + 1




        Next x
        
    End With
    
End Sub
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
It works for me. Does it help if we re-write the code as follows?

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateListViewCases()
    
    [COLOR=darkblue]Dim[/COLOR] startRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] [COLOR=green]'beginning of data[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] lastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] [COLOR=green]'end of data[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    
    [COLOR=darkblue]Set[/COLOR] ws = ThisWorkbook.Sheets("OpenCases")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    startRow = 2

    [COLOR=darkblue]With[/COLOR] lv_openCases
    
        .View = lvwReport
        .ListItems.Clear
        .HideColumnHeaders = [COLOR=darkblue]False[/COLOR]
        .Appearance = cc3D
        .FullRowSelect = [COLOR=darkblue]True[/COLOR]
        
        [COLOR=darkblue]With[/COLOR] .ColumnHeaders    [COLOR=green]'gives me headers at the top[/COLOR]
            .Clear      [COLOR=green]'defining the columnheaders[/COLOR]
            .Add , , "Date Received", 65
            .Add , , "Time Received", 65
            .Add , , "Name", 150
            .Add , , "Department", 75
            .Add , , "Request", 100
            .Add , , "Code", 30
            .Add , , "Time Left", 60
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            
        [COLOR=darkblue]For[/COLOR] x = startRow [COLOR=darkblue]To[/COLOR] lastRow
            .ListItems.Add , , ws.Cells(x, 1)
            [COLOR=darkblue]With[/COLOR] .ListItems(.ListItems.Count).ListSubItems
                .Add , , ws.Cells(x, 2)
                .Add , , ws.Cells(x, 3)
                .Add , , ws.Cells(x, 4)
                .Add , , ws.Cells(x, 5)
                .Add , , ws.Cells(x, 6)
                .Add , , Format(ws.Cells(x, 9), "hh:mm")
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Next[/COLOR] x
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
Domenic said:
It works for me. Does it help if we re-write the code as follows?

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateListViewCases()
    
    [COLOR=darkblue]Dim[/COLOR] startRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] [COLOR=green]'beginning of data[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] lastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] [COLOR=green]'end of data[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    
    [COLOR=darkblue]Set[/COLOR] ws = ThisWorkbook.Sheets("OpenCases")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    startRow = 2

    [COLOR=darkblue]With[/COLOR] lv_openCases
    
        .View = lvwReport
        .ListItems.Clear
        .HideColumnHeaders = [COLOR=darkblue]False[/COLOR]
        .Appearance = cc3D
        .FullRowSelect = [COLOR=darkblue]True[/COLOR]
        
        [COLOR=darkblue]With[/COLOR] .ColumnHeaders    [COLOR=green]'gives me headers at the top[/COLOR]
            .Clear      [COLOR=green]'defining the columnheaders[/COLOR]
            .Add , , "Date Received", 65
            .Add , , "Time Received", 65
            .Add , , "Name", 150
            .Add , , "Department", 75
            .Add , , "Request", 100
            .Add , , "Code", 30
            .Add , , "Time Left", 60
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            
        [COLOR=darkblue]For[/COLOR] x = startRow [COLOR=darkblue]To[/COLOR] lastRow
            .ListItems.Add , , ws.Cells(x, 1)
            [COLOR=darkblue]With[/COLOR] .ListItems(.ListItems.Count).ListSubItems
                .Add , , ws.Cells(x, 2)
                .Add , , ws.Cells(x, 3)
                .Add , , ws.Cells(x, 4)
                .Add , , ws.Cells(x, 5)
                .Add , , ws.Cells(x, 6)
                .Add , , Format(ws.Cells(x, 9), "hh:mm")
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Next[/COLOR] x
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...



I figured out why I couldn't get mine to work. I changed the Sorted property to TRUE. Any idea how to make the sorting of data an option?
By the way, I like your code. It's a lot cleaner than mine :) thanks.
 
Upvote 0
You can add a commandbutton to your userform, and place the following code in the userform code module...

Code: 
Private Sub CommandButton1_Click()
    Me.lv_openCases.Sorted = True
End Sub

Change the name of the commandbutton, accordingly. Note that you can also set the sort key and sort order.

Hope this helps!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
ListView Filter
Replies
7
Views
557
James006
James006
X
  • Question
Vba: Change list view data
Replies
0
Views
923
Xlacs
X
V
  • Solved
Display Listview data in Userform
Replies
9
Views
2K
smozgur
smozgur
V
  • Question
Filter a listview with combobox
Replies
2
Views
2K
van0912
V
R
  • Question
ListView Column Width
Replies
8
Views
2K
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,216,115
Messages
6,128,915
Members
449,478
Latest member
Davenil

Share this page

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