Announcement

Collapse
No announcement yet.

Search Data Userform

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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

      New users should read the Forum Rules before posting

      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.

      Comment


      • #4
        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.

        Comment


        • #5
          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?

          Comment


          • #6
            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Search Data Userform

                      If this is a new query perhaps you should start a new thread for it.
                      Boo!

                      Comment


                      • #12
                        Re: Search Data Userform

                        I throught it would quicker and less annoying if i continue this query in the same thread as it will help in the subject at hand???

                        Comment


                        • #13
                          Re: Search Data Userform

                          Unless it's directly related to the original thread I would suggest a new thread.
                          Boo!

                          Comment


                          • #14
                            Re: Search Data Userform

                            I Have done before it keeps coming to the same person giving the post and the answer which is not any help to me. All i want is code that will automatically insert the two vlookup formulas intho the cells when i add a new row. The person keeps telling me to use marco recorder and use that but thats not automatically.

                            Comment


                            • #15


                              Re: Search Data Userform

                              Well that person, whoever they are, is giving good advice.

                              You use the macro recorder to generate code when you do what you want manually.

                              You then need to adapt the code to work in a general situation.

                              By the way I don't see any connection between this thread's subject title and inserting VLOOKUP formulas.
                              Boo!

                              Comment

                              Working...
                              X