Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 27

Thread: Search Data Userform

  1. #1
    Join Date
    2nd June 2005
    Posts
    211

    Search Data Userform

    This example.xls may be useful to understand what i need done.

    What i need is Search Product or Data userform, so when i want a product i need i can use it.

    When i select a cell under Product Name column (not column A as the product name column can change position) to insert a new product, i want to be able to press the userform button and the search product userform will appear. I will then be able to type a product name or number and press search and the returning found products under that criteria will then be displayed on the listbox.

    I be then able to select the product i want from the listbox and press the button insert product to the cell selected on the worksheet (the column Product Number and Ba are using vlookup formulas to automatically insert the rest of the products information).

    The product database is under the worksheet Products. Is it posible to serparte the list box into three columns under headings Product Name, Product Number and BA. So if i enter either a name or number they will still display correctly under the list box.

    Please try and help it would be a great help.

    Mini12
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    Re: Search Data Userform

    Hi,
    add the code to form module
    used columns F:H as working column in order to show column header on the Listbox
    You can search with wildcard "*"
    e.g
    *600 or 600* or *600*
    Code:
    Private Sub cmdSearch_Click()
    Dim a(), r As Range, res, i As Long, ff As String, rng As Range
    Dim title
    title = Array("Product Name", "Product Number", "BA")
    res = Replace(Me.txtSearch, "-", "")
    If Len(res) = 0 Then
    Here:
        With Me
            With .ListBox1
                .ColumnHeads = False
                .RowSource = ""
                .Clear
            End With
            .txtSearch.SetFocus
        End With
        Exit Sub
    End If
    With Sheets("Products")
        Set r = .Range("a:b").Find(what:=res, lookat:=xlWhole, MatchCase:=False)
            If Not r Is Nothing Then
                ReDim a(1 To 3, 1 To 1): i = 1
                ff = r.Address: ReDim Preserve a(1 To 3, 1 To i)
                    For ii = 1 To 3
                        If ii = 2 Then
                            a(ii, i) = Format(.Cells(r.Row, ii), "000-000-00")
                        Else
                            a(ii, i) = .Cells(r.Row, ii).Value
                        End If
                    Next
                Do
                    Set r = .Range("a:b").FindNext(r)
                    If r.Address = ff Then Exit Do
                    i = i + 1: ReDim Preserve a(1 To 3, 1 To i)
                    For ii = 1 To 3
                        If ii = 2 Then
                            a(ii, i) = Format(.Cells(r.Row, ii), "000-000-00")
                        Else
                            a(ii, i) = .Cells(r.Row, ii).Value
                        End If
                    Next
                Loop Until r Is Nothing
            Else
                GoTo Here
            End If
            With .Range("f1")
                .CurrentRegion.Clear
                .Resize(, UBound(title) + 1).Value = title
                .Offset(1).Resize(UBound(a, 2), UBound(a, 1)).Value = Application.Transpose(a)
                Set rng = .CurrentRegion
            End With
    End With
    With Me.ListBox1
        .ColumnHeads = True
        .ColumnCount = 3
        .ColumnWidths = "180;70;30"
        .RowSource = rng.Offset(1).Resize(rng.Rows.Count - 1).Address
    End With
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    Re: Search Data Userform

    ake a look at the DataForm in this thread

    http://www.ozgrid.com/forum/showthre...highlight=find
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    2nd June 2005
    Posts
    211

    Re: Search Data Userform

    Thats great it work fine but is there a way in which i could get the *"* wildcard to come up automatically in the text box. Also what can't i use A,B and C for the database without using other columns. Also it does not insert the product to any cell.

    There another thing i found out the products do not appear in the list box when i am in the First worksheet but appear when i am in sheet 2.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    Re: Search Data Userform

    For you equirements 1) & 2)
    Code:
    Private Sub cmdSearch_Click()
        Dim a(), r As Range, res, i As Long, ff As String, rng As Range
        Dim title
        title = Array("Product Name", "Product Number", "BA")
        res = Replace(Me.txtSearch, "-", "")
        If Len(res) = 0 Then
    Here:
            With Me
                With .ListBox1
                    .ColumnHeads = False
                    .RowSource = ""
                    .Clear
                End With
                .txtSearch.SetFocus
            End With
            Exit Sub
        End If
        With Sheets("Products")
            Set r = .Range("a:b").Find(what:=res, lookat:=xlPart, MatchCase:=False)
            If Not r Is Nothing Then
                ReDim a(1 To 3, 1 To 1): i = 1
                ff = r.Address: ReDim Preserve a(1 To 3, 1 To i)
                For ii = 1 To 3
                    If ii = 2 Then
                        a(ii, i) = Format(.Cells(r.Row, ii), "000-000-00")
                    Else
                        a(ii, i) = .Cells(r.Row, ii).Value
                    End If
                Next
                Do
                    Set r = .Range("a:b").FindNext(r)
                    If r.Address = ff Then Exit Do
                    i = i + 1: ReDim Preserve a(1 To 3, 1 To i)
                    For ii = 1 To 3
                        If ii = 2 Then
                            a(ii, i) = Format(.Cells(r.Row, ii), "000-000-00")
                        Else
                            a(ii, i) = .Cells(r.Row, ii).Value
                        End If
                    Next
                Loop Until r Is Nothing
            Else
                GoTo Here
            End If
        End With
        With Me.ListBox1
            .ColumnCount = 3
            .ColumnWidths = "180;70;30"
            .List = Application.Transpose(a)
        End With
    End Sub
    3) Where do you want to add selected item?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd June 2005
    Posts
    211

    Re: Search Data Userform

    Any cell selected under product column B in sheet1.

    Also could i get error message when the serach can not find a product if mispelled or no product containg that name or number.

    Also when i type a product number it goes like this:

    Product Name
    000-000-00
    00

    In the listbox, i want it to go like this:

    Product Name 000-000-00 00

    This only happens when i type in a product number than a product name.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    Re: Search Data Userform

    Code:
    Private Sub cmdAdd_Click()
    Dim x As Long, ws As Worksheet, i As Long, ii As Integer
    Set ws = Sheets("sheet1")
    With Sheets("sheet1")
        x = ActiveCell.Row
    End With
    With Me.ListBox1
        For i = 0 To .ListCount
            If .Selected(i) Then
                For ii = 0 To .ColumnCount - 1
                   ws.Cells(x, ii + 1) = .List(i, ii)
                Next
            End If
        Next
    End With
    End Sub
    
    Private Sub cmdClose_Click()
      Unload Me
    End Sub
    Private Sub cmdSearch_Click()
        Dim a(), r As Range, res, i As Long, ff As String, rng As Range
        Dim title
        title = Array("Product Name", "Product Number", "BA")
        res = Replace(Me.txtSearch, "-", "")
        If Len(res) = 0 Then
    Here:
            With Me
                With .ListBox1
                    .ColumnHeads = False
                    .RowSource = ""
                    .Clear
                End With
                .txtSearch.SetFocus
            End With
            Exit Sub
        End If
        With Sheets("Products")
            Set r = .Range("a:b").Find(what:=res, lookat:=xlPart, MatchCase:=False)
            If Not r Is Nothing Then
                ReDim a(1 To 3, 1 To 1): i = 1
                ff = r.Address: ReDim Preserve a(1 To 3, 1 To i)
                For ii = 1 To 3
                    If ii = 2 Then
                        a(ii, i) = Format(.Cells(r.Row, ii), "000-000-00")
                    Else
                        a(ii, i) = .Cells(r.Row, ii).Value
                    End If
                Next
                Do
                    Set r = .Range("a:b").FindNext(r)
                    If r.Address = ff Then Exit Do
                    i = i + 1: ReDim Preserve a(1 To 3, 1 To i)
                    For ii = 1 To 3
                        If ii = 2 Then
                            a(ii, i) = Format(.Cells(r.Row, ii), "000-000-00")
                        Else
                            a(ii, i) = .Cells(r.Row, ii).Value
                        End If
                    Next
                Loop Until r Is Nothing
            Else
                MsgBox Me.txtSearch & vbLf & vbLf & "Not Found"
                Me.txtSearch = ""
                GoTo Here
            End If
        End With
        With Me.ListBox1
            .ColumnCount = 3
            .ColumnWidths = "180;70;30"
            If i > 1 Then
                .List = Application.Transpose(a)
            Else
                .Column = a
            End If
        End With
    Also when i type a product number it goes like this:

    Product Name
    000-000-00
    00

    In the listbox, i want it to go like this:

    Product Name 000-000-00 00

    This only happens when i type in a product number than a product name.
    I don't understand.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    2nd June 2005
    Posts
    211

    Re: Search Data Userform

    Don't worry i have fixed it, but the add product code still needs some tweaking. One if you to the example, you will see that the two columns B and C have two vlookup formulas in them. #=IF(A7="","",VLOOKUP(A7,ProductLookup,2,FALSE)) and =IF(A7="","",VLOOKUP(A7,ProductLookup,3,FALSE))#
    They are very important and are needed for the sheet for other things, so is there a way when a add a product it just puts in the name of the product in the column A and no product number and ba in column b and c.

    Thanks for the help

    Ben

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    Re: Search Data Userform

    Change
    Code:
        With Me.ListBox1 
            For i = 0 To .ListCount 
                If .Selected(i) Then 
                    For ii = 0 To .ColumnCount - 1 
                        ws.Cells(x, ii + 1) = .List(i, ii) 
                    Next 
                End If 
            Next 
        End With
    To
    Code:
        With Me.ListBox1 
            For i = 0 To .ListCount 
                If .Selected(i) Then 
                        ws.Cells(x, 1) = .List(i, 0) 
                End If 
            Next 
        End With

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    2nd June 2005
    Posts
    211

    Re: Search Data Userform

    Thanks thats great it works now.

    Now heres another query, is there a way when i insert a new row, the two vlookups automatically insert themselves into the corresponding cells.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. UserForm To Search, Edit, Add & Delete Data
    By leemarquis in forum EXCEL HELP
    Replies: 4
    Last Post: March 17th, 2008, 12:02
  2. Macro For Search, Display, Update Data Via Userform
    By Josue Soto in forum EXCEL HELP
    Replies: 10
    Last Post: January 2nd, 2008, 23:19
  3. UserForm To Search, Find & Return Data
    By CDE in forum EXCEL HELP
    Replies: 5
    Last Post: November 19th, 2007, 10:29
  4. Userform Search & Enter Data on Same Row
    By nu@this in forum EXCEL HELP
    Replies: 1
    Last Post: June 30th, 2007, 15:24
  5. Search And Insert Data Userform
    By mini12 in forum EXCEL HELP
    Replies: 2
    Last Post: March 27th, 2007, 22:00

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
  •  
porno