Results 1 to 4 of 4

Thread: set ListView on userform

  1. #1
    Junior Member
    Join Date
    Jul 2011
    Posts
    8
    Rep Power
    0

    set ListView on userform

    PFA workbook.
    Please help me to set the ListView on userform as per mention in workbook
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    Replace the userform module code with the following.

    Code:
    Private Sub CommandButton1_Click()
        'SEARCH
        Dim Cnt As Long
        Dim FirstAddx As String
        Dim FoundMatch As Range
        Dim LastRow As Long
        Dim R As Long
        Dim StartRow As Long
        Dim Wks As Worksheet
        Dim Rng    As Range
        Dim lstItem As ListItem
        
        
        StartRow = 2
        Set Wks = Sheets(1)
            
        If TextBox1.Text = "" Then
           MsgBox "Please enter a search term."
           TextBox1.SetFocus
           Exit Sub
        End If
           
        Set Rng = Wks.Range("A2:g" & Wks.Range("a" & Wks.Rows.Count).End(3).Row)
        Set FoundMatch = Rng.Find(What:=TextBox1.Text, LookAt:=xlPart, MatchCase:=False)
        
        If Not FoundMatch Is Nothing Then
            FirstAddx = FoundMatch.Address
            ListView1.ListItems.Clear
            For R = 1 To Rng.Rows.Count
                ListView1.ListItems.Add R
            Next
            Do
                Cnt = FoundMatch.Column
                R = FoundMatch.Row
                If Cnt = 1 Then
                    ListView1.ListItems(R - 1).Text = FoundMatch.Text
                Else
                    Set lstItem = ListView1.ListItems(R - 1)
                    lstItem.SubItems(Cnt - 1) = FoundMatch.Text
                End If
                Set FoundMatch = Rng.FindNext(FoundMatch)
            Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
            SearchRecords = Cnt
        Else
            ListView1.ListItems.Clear
            SearchRecords = 0
            MsgBox "No match found for " & TextBox1.Text
        End If
              
    End Sub
    
    Private Sub UserForm_Activate()
    
        Dim C As Long
        Dim I As Long
        Dim R As Long
        Dim Wks As Worksheet
      
        With ListView1
            .Gridlines = True
            .View = lvwReport
            .HideSelection = False
            .FullRowSelect = True
            .HotTracking = True
            .HoverSelection = False
        End With
        
        Set Wks = Sheets(1)
        
        For C = 1 To 7
          ListView1.ColumnHeaders.Add Text:=Wks.Cells(1, C).Text
        Next C
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Jul 2011
    Posts
    8
    Rep Power
    0
    thank u so much Admin for u r help and time..!

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Thanks for the feedback
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Double click can edition in ListView
    By marreco in forum Excel Help
    Replies: 1
    Last Post: 02-18-2013, 10:09 PM
  2. VBA Validation List set
    By xander1981 in forum Excel Help
    Replies: 3
    Last Post: 02-15-2013, 04:07 PM
  3. Set Chart size using VBA
    By LalitPandey87 in forum Excel Help
    Replies: 3
    Last Post: 11-08-2011, 08:54 AM
  4. Listview not working in Windows 7 - 32 bit
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 07-09-2011, 07:40 PM
  5. Using ListView - a Listbox on steroids
    By Rasm in forum Download Center
    Replies: 3
    Last Post: 04-09-2011, 03:34 AM

Tags for this Thread

Posting Permissions

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