Results 1 to 5 of 5

Show all data in spreadsheet via listview (VBA)

Threaded View

  1. #1
    Registered User
    Join Date
    03-08-2011
    Location
    lancs
    MS-Off Ver
    Excel 2007
    Posts
    6

    Show all data in spreadsheet via listview (VBA)

    Hi i have a search facility on a userform that when you specify a column to search in and type part of a word into a text box it will bring up all the records in the spreadsheet that relate to the search via the list view.

    However i want to be able to click a button to show all records in the spreadsheet in the list view.
    this is my code for the search facilty.


    Private Sub OK_Click()
     'SEARCH
     
     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
     
         StartRow = 2
         
          Col = header.ListIndex + 1
        If Col = 0 Then
              MsgBox "Please choose a Search category."
            Exit Sub
            End If
            
           If sch.Text = "" Then
           
              MsgBox "Please Enter a Search Criteria."
              sch.SetFocus
              
              
            Exit Sub
           End If
           
             LastRow = Cells(Rows.Count, Col).End(xlUp).Row
             LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
             
             Set Rng = Range(Cells(2, Col), Cells(LastRow, Col))
             
               Set FoundMatch = Rng.find(What:=sch.Text, _
                                         After:=Rng.Cells(1, 1), _
                                         LookAt:=xlPart, _
                                         LookIn:=xlValues, _
                                         SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, _
                                         MatchCase:=False)
              
              If Not FoundMatch Is Nothing Then
                 FirstAddx = FoundMatch.Address
                 ListView1.ListItems.clear
                 
                 Do
                   Cnt = Cnt + 1
                   R = FoundMatch.Row
                   ListView1.ListItems.add Index:=Cnt, Text:=R
                     For Col = 1 To 9
                       Set C = Cells(R, Col)
                       ListView1.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
                 ListView1.ListItems.clear
                 SearchRecords = 0
                 MsgBox "No match found for " & sch.Text
              End If
              
    End Sub
    
    Private Sub UserForm_Activate()
                                       
      Dim C As Long
      Dim I As Long
      Dim R As Long
        
        ListView1.View = lvwReport
        ListView1.HideSelection = False
        ListView1.FullRowSelect = True
        ListView1.HotTracking = True
        ListView1.HoverSelection = False
      
        ListView1.ColumnHeaders.add Text:="Row", Width:=64
        
          For C = 1 To 9
            ListView1.ColumnHeaders.add Text:=Cells(1, C).Text
            header.AddItem Cells(1, C).Text
          Next C
        
          'For R = 2 To 21
          '  ListView1.ListItems.Add Index:=R - 1, Text:=Str(R)
          '  For C = 1 To 13
          '    ListView1.ListItems(R - 1).ListSubItems.Add Index:=C, Text:=Cells(R, C).Text
          '  Next C
          'Next R
        
    End Sub
    Thanks in advance

    L
    Last edited by martinl4; 03-11-2011 at 11:00 AM. Reason: SELF RESOLVED

Thread Information

Users Browsing this Thread

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

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