Listview add an index column at form initialization

Hello,


I have a form I developed in Excel 2010 that has a Listview control and is populated from a sheet with 20 columns and 396 rows. I want to have and Index column that gets populated with row numbers at UserForm_Initialize(). The index column populates when I do a search using a combobox, textbox and a command button with the following code, but after working on it all day I cannot find a solution to load the index when the userform initializes. Also I add a column for the index during initialization (code below). Any help would be extremely appreciated.

 Private Sub cmbVendorSearch_Click()
 ' search for part by entering a catagory and a search term
  
 Dim Cnt As Long
 Dim Col As Variant
 Dim FirstAddx As String
 Dim FoundMatch As Range
 Dim LastRow As Long
 Dim r As Long
 Dim StartRow As Long
 Dim Wks As Worksheet
 'Start in row 2 in sheet 11
     StartRow = 2
     Set Wks = Sheets(11)
     Col = ComboBox1.ListIndex + 1
       'If nothing is entered in category box then display message
         If Col = 0 Then
            MsgBox "Please choose a category."
            Exit Sub
         End If
        ' if nothing is entered in search box then display message
       If TextBox1.Text = "" Then
          MsgBox "Please enter a search term."
          TextBox1.SetFocus
          Exit Sub
       End If
       'if user access level is greater than 1, enable listview dblclick function
       If frmSearchDatabase.LblAccessLevel.Caption > 1 Then
           ListView4.Enabled = True
           End If

         LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
         LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
         
         Set rng = Wks.Range(Wks.Cells(2, Col), Wks.Cells(LastRow, Col))
         ' Finds a match to text entered in search box - LookAt: x1Whole=exact match, x1Part=Partial Match
           
           If cbVendorMatchText = False Then
           Set FoundMatch = rng.Find(What:=TextBox1.Text, _
                                     After:=rng.Cells(1, 1), _
                                     LookAt:=xlPart, _
                                     LookIn:=xlValues, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext)
            Else
            Set FoundMatch = rng.Find(What:=TextBox1.Text, _
                                     After:=rng.Cells(1, 1), _
                                     LookAt:=xlWhole, _
                                     LookIn:=xlValues, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext)
            End If
            
          ' Clear listview if nothing is found
          If Not FoundMatch Is Nothing Then
             FirstAddx = FoundMatch.Address
             ListView4.ListItems.Clear
             
             Do                                                    'THIS WORKS BUT ONLY AFTER SEARCH CRITERIA IS RETURNED
               Cnt = Cnt + 1
               r = FoundMatch.Row
              ListView4.ListItems.Add Index:=Cnt, Text:=r      
                 For Col = 1 To 20
                   Set c = Wks.Cells(r, Col)
                   ListView4.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=c.Text
                Next Col
Set FoundMatch = rng.FindNext(FoundMatch)
             Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
             SearchRecords = Cnt
             
          Else
             ListView4.ListItems.Clear
             SearchRecords = 0
             MsgBox "No match found for " & TextBox1.Text
             
          End If
            
End Sub

With ListView4
      .Gridlines = True
      .View = lvwReport
      .HideSelection = False
      .FullRowSelect = True
      .HotTracking = True
      .HoverSelection = False
      .ColumnHeaders.Add Text:="INDEX", Width:=10
      .Sorted = True
      .SortOrder = lvwAscending 'will sort Ascending from first column
      .Enabled = True
    End With

<bump> Still working on it, not making any progress.

BigD

Hi jaslake,

Thank you for the reply. I was able to get Listview to load the Index values into the first column in the UserForm_Initialize about 1/2 an hour ago, however now I can't get the list to display the data in columns. Its either list is populated with data and no Index or Index and no data. The application is too large to post and contains company data. Here is were I'm at with the Initialization. It appears it will work, its just getting it set up right.

BigD

Private Sub UserForm_Initialize()
'Declaration
    Dim r As Long
    Dim Wks As Worksheet
    Dim rngData As Range
    'Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim s As Long
    Dim j As Long
   
'Initilize mix/max window buttons
    InitMaxMin frmVendorList.Caption
    Ht = frmVendorList.Height
    Lg = frmVendorList.Width
     With ListView4
      .Gridlines = True
      .View = lvwReport
      .HideSelection = False
      .FullRowSelect = True
      .HotTracking = True
      .HoverSelection = False
      .ColumnHeaders.Add Text:="INDEX", Width:=25
      '.Sorted = True
      '.SortOrder = lvwAscending 'will sort Ascending from first column
      .Enabled = True
    End With
      
'allow dbl_click to edit vendor info if access level is >1
       'If frmSearchDatabase.LblAccessLevel.Caption > 1 Then
           'ListView4.Enabled = True
           'lblDblClick.visable = True
           'End If
      
'populate listview from sheet 11 on open
   
'Set the source worksheet
    Set Wks = Worksheets("Sheet11")
   
'Set the source range
    Set rngData = Wks.Range("A1").CurrentRegion
       
'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
    
'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
    
'test for counts
    Label4.Caption = RowCount
    Label5.Caption = ColCount
         
'Fill the ListView

       For s = 2 To RowCount
        Set LstItem = Me.ListView4.ListItems.Add(Text:=rngData(s, 1).Value) ' displays Index only when (s, 1)
        For j = 1 To ColCount                                               ' change to (s, 2) displays sheet data but no Index Data
            LstItem.ListSubItems.Add Text:=rngData(s, j).Value              ' In both cases Index header = "INDEX" and column is displayed
        Next j
    Next s
    
   
' get column headers from sheet 11 columns 1 to 8 only and display in ListView4 header
      For c = 1 To 8
         ListView4.ColumnHeaders.Add Text:=Wks.Cells(1, c).Text
 'add the column headers to search vendor combobox1
        ComboBox1.AddItem Wks.Cells(1, c).Text
      Next c
      
 '-------------------------------------------------------------------
'add index to listview column 1
  
    For iRow = 2 To RowCount
    Me.ListView4.ListItems.Add Index:=1, Text:=iRow
    
        Next iRow
 '--------------------------------------------------------------------
 'set the column header width
      
      ListView4.ColumnHeaders.Item(2).Width = 200 'Company
      ListView4.ColumnHeaders.Item(3).Width = 100 'Vendor Code
      ListView4.ColumnHeaders.Item(4).Width = 100 'Contact 1
      ListView4.ColumnHeaders.Item(5).Width = 80 'Phone 1
      ListView4.ColumnHeaders.Item(6).Width = 80 'Mobile 1
      ListView4.ColumnHeaders.Item(7).Width = 80 'Fax 1
      ListView4.ColumnHeaders.Item(8).Width = 140 'Email 1
      ListView4.ColumnHeaders.Item(9).Width = 140 'Web Site
      
    End Sub

Hi jaslake,

It works!!!

That is just great, thank you so much for your time.

One thing if anyone plans on using this make sure the SORT property of ListView is set to FALSE or else the first time the form is displayed it will not be in order. From what I've read it's because ListView sorts Alphanumerical and not Numerical on the first column.

Thanks again to jaslake

BigD