+ Reply to Thread
Results 1 to 3 of 3

Userform Search to return results in columns in listbox

Hybrid View

  1. #1
    Registered User
    Join Date
    12-20-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Userform Search to return results in columns in listbox

    Hello

    I've scoured the internet trying to find a solution but havent been able to find a suitable solution. I found a search macro that almost suits what i need to do.
    Currently the results are displayed in one column in a listbox. What i would like to happen is the complete row (columns A:F) displayed in 6 columns, with headers.

    this is the macro i'm currently using to search

    Private Sub CommandButton2_Click()
     'SEARCH
     
         StartRow = 2
         
           Col = ComboBox1.ListIndex + 1
             If Col = 0 Then
                MsgBox "Please choose a category."
                Exit Sub
             End If
            
           If TextBox1.Text = "" Then
              MsgBox "Please enter a search term."
              TextBox1.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:=TextBox1.Text, _
                                         After:=Rng.Cells(1, 1), _
                                         LookAt:=CInt(CheckBox2.Tag), _
                                         LookIn:=xlValues, _
                                         SearchOrder:=xlByRows, _
                                         SearchDirection:=xlNext, _
                                         MatchCase:=CheckBox1.Value)
              
              If Not FoundMatch Is Nothing Then
                 FirstAddx = FoundMatch.Address
                 ListBox1.Clear
                 
                 Do
                                  Cnt = Cnt + 1
                   R = FoundMatch.Row
                   ListBox1.AddItem "Row " & R
                     For Each C In Range(Cells(R, "A"), Cells(R, "F"))
                       ListBox1.AddItem C.Text
                     Next C
                   Set FoundMatch = Rng.FindNext(FoundMatch)
                   A = FoundMatch.Address
                 Loop While CheckBox3 = True And FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
                 Label1.Caption = "Matches =" & Str(Cnt)
                 SearchRecords = Cnt
              Else
                 ListBox1.Clear
                 Label1.Caption = ""
                 SearchRecords = 0
                 MsgBox "No match found for " & TextBox1.Text
              End If
              
    End Sub
    Hope i'm making sense, any help would be great

    thanks Goffa

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Userform Search to return results in columns in listbox

    This change should do it. You need a 7 column listbox, one column for "Row n" and the other 6 for columns A-F

    '...
    If Not FoundMatch Is Nothing Then
                 FirstAddx = FoundMatch.Address
                 ListBox1.Clear
                 
                 Do
                                  Cnt = Cnt + 1
                   R = FoundMatch.Row
                   With ListBox1
                       .AddItem "Row " & R
                       .List(.ListCount - 1, 1) = Cells(R, 1)
                       .List(.ListCount - 1, 2) = Cells(R, 2)
                       .List(.ListCount - 1, 3) = Cells(R, 3)
                       .List(.ListCount - 1, 4) = Cells(R, 4)
                       .List(.ListCount - 1, 5) = Cells(R, 5)
                       .List(.ListCount - 1, 6) = Cells(R, 6)
                   End With
                   Set FoundMatch = Rng.FindNext(FoundMatch)
                   A = FoundMatch.Address
                 Loop While CheckBox3 = True And FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
                 Label1.Caption = "Matches =" & Str(Cnt)
                 SearchRecords = Cnt
              Else
                 ListBox1.Clear
                 Label1.Caption = ""
                 SearchRecords = 0
                 MsgBox "No match found for " & TextBox1.Text
              End If
    '...
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    12-20-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Userform Search to return results in columns in listbox

    That worked perfectly

    Thanks MikeRickson

+ 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. Userform:Search through all ws / show results in Listbox
    By wali in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-22-2015, 09:56 PM
  2. Transfer Data From One Userform Listbox to Another Userform Listbox with 11 columns
    By sparkoft in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-18-2013, 10:54 AM
  3. Userform Search Function Autofilter Results and Repopulate Userform
    By cindy71 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-16-2012, 03:46 PM
  4. Replies: 15
    Last Post: 02-07-2012, 11:39 AM
  5. UserForm to search sheet, show results on separate userform?
    By egemenkepekci in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-25-2010, 01:06 PM

Tags for this Thread

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