Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    8,641

    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*
    VB:
    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
    18,509

    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
    8,641

    Re: Search Data Userform

    For you equirements 1) & 2)
    VB:
    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
    8,641

    Re: Search Data Userform

    VB:
    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
    8,641

    Re: Search Data Userform

    Change
    VB:
    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
    VB:
    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