+ Reply to Thread
Results 1 to 9 of 9

Listview add an index column at form initialization

Hybrid View

  1. #1
    Registered User
    Join Date
    09-11-2015
    Location
    Hamilton, ON Canada
    MS-Off Ver
    2013
    Posts
    32

    Question 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

  2. #2
    Registered User
    Join Date
    09-11-2015
    Location
    Hamilton, ON Canada
    MS-Off Ver
    2013
    Posts
    32

    Re: Listview add an index column at form initialization

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

    BigD

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Listview add an index column at form initialization

    Hi BigD

    Welcome to the Forum!!!

    I've worked with ListView a bit and have done as I believe you're describing (capture a Row Reference). However, I don't understand this...the Index simply does not exist upon Initialization...
    I cannot find a solution to load the index when the userform initializes
    or this...
    'THIS WORKS BUT ONLY AFTER SEARCH CRITERIA IS RETURNED
    There is no Row Reference until the Search Criteria is returned, so, you have me a bit confused.

    Please attach a sample workbook the Structure of which is the same as your actual workbook.

    INCLUDE ANY CODE YOU'RE USING OR HAVE TRIED.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    09-11-2015
    Location
    Hamilton, ON Canada
    MS-Off Ver
    2013
    Posts
    32

    Re: Listview add an index column at form initialization

    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

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Listview add an index column at form initialization

    Hi BigD

    You can zip the File and attach it if you wish.

    Alternately, you can Email the File so I can look at it...your choice.

  6. #6
    Registered User
    Join Date
    09-11-2015
    Location
    Hamilton, ON Canada
    MS-Off Ver
    2013
    Posts
    32

    Re: Listview add an index column at form initialization

    Hi jaslake,

    I made a test form to show what is happening and it is attached. It's close, but still needs something.

    BigD






    Post Edit: This File contains the search operation
    Attached Files Attached Files
    Last edited by BigDaddy01; 09-28-2015 at 07:48 AM. Reason: Updated File

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Listview add an index column at form initialization

    Hi BigD

    In the attached I've replaced these lines of Code...
    'Fill the ListView
       For s = 2 To RowCount
          Set LstItem = Me.ListView4.ListItems.Add(Text:=rngData(s, 1).Value)
          For j = 2 To ColCount
             LstItem.ListSubItems.Add Text:=rngData(s, j).Value
          Next j
       Next s
    with this...
    'Fill the ListView
       k = 1
       For s = 2 To RowCount
          Me.ListView4.ListItems.Add Index:=k, Text:=s
          For j = 1 To ColCount
             Me.ListView4.ListItems(k).ListSubItems.Add Text:=rngData(s, j).Value
          Next j
          k = k + 1
       Next s
    Let me know of issues.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-11-2015
    Location
    Hamilton, ON Canada
    MS-Off Ver
    2013
    Posts
    32

    Re: Listview add an index column at form initialization

    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

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Listview add an index column at form initialization

    You're welcome...glad I could help.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Chart initialization!!
    By meus in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-23-2015, 05:19 PM
  2. Multi Column Listview
    By Jovillanueva in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2014, 09:34 AM
  3. Display data in ListView upon specific text in column Y
    By ciapul12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-23-2014, 05:51 PM
  4. [SOLVED] I need to send a target.row variable to a User form Initialization sub. Is it Possible?
    By sdavison in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-26-2013, 07:47 AM
  5. [SOLVED] Textbox enter, select item in ListView and pass the dice to Another Form
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-20-2013, 06:16 AM
  6. Listview checked row delete line in Listview and in worksheet data
    By madforit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2012, 01:00 PM
  7. Max number of item in a form (in a ListView)
    By Pierre in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2007, 07:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1