none
[VBA] How to search with keywords RRS feed

  • Question

  • I have my excel which is used to record inventory movement. I have set a data validation list, so that the user has to select a pre-defined item from the inventory in the list. But, having a huge collection of items within the inventory list frustrates the user as he has to go through the entire list from the drop down box in a particular cell.

    Is there a way in which, I could let the cell only show those entries from the list based on the keyword the user enters in the cell. It could also work in a userform, but the primary motive is to have the list dynamically show only items from the list based on the keyword. Someone suggested jquery, but I do not think it works to link data to excel. 

    Any help would be appreciated

    Monday, April 27, 2015 2:41 PM

Answers

  • As an addition, could you explain how did you work out the formula's ( just an overview is enough as i'm also learning) as you used the combo box to generate a list of items containg the keyword in sheet 1.

    If this answers your query satisfactorily then please mark it as the answer so that the moderators know that the thread has been satisfactorily handled.

    The DropDown list is created with code using AutoFilter. It emulates what you could do manually like the following.

    1. On worksheet "Stock" Turn on Autofilter
    2. Select DropDown on column B and select Text filters (above search field) then select Contains
    3. Enter keyword in the field (eg. dry)
    4. All data containing the word "dry" is displayed
    5. The visible data is then copied to column D (Select the data then select Find & Select -> Go To Special -> Visible cells only and copy then select cell D1 and Paste).
    6. Data from row 2 is the list used for the ComboBox RowSource.
    7. You can also add your own wild cards. dry*screws returns all rows containing dry and screws
    8. Note that you can use the wild card the same way in the ComboBox.

    Not sure of your expertise but to display the code associated with the Userform:

    1. Alt and F11 to open the VBA editor
    2. Right click Userform in the Project Explorer (Left column)
    3. Select View code.

    Below is a copy of the code with comments added. I think this is the best way to explain so I hope you can follow but please get back to me if you still have more questions.

    You could delete all of the code in the Userform module and replace with the code below and it will be easier to read with the comments all showing up in green. (Very difficult to read with all the same color.)

    The RowSource for the ComboBox is set to a Named range. This named range gets updated by the code when the new list containing the key word is created. The Initialize event for the Userform tests if the named range exists and if not then it creates it. This only occurs once and then the name remains saved with the work book.

    Option Explicit

    Const strDateFormat = "dd-mmm-yyyy"     'Edit date format to required format to display on Userform

    'Following line is probably superfluous because we don't have
    'any change or after update events but is included in case of
    'further modification of the code that includes these events.
    Dim suppressCbo As Boolean             'Used to suppress recursive calls to the event code

    Private Sub UserForm_Initialize()
        Dim rngName As Range
        With Sheet2
            '****************************************************
            'Code between asterisk lines tests if name CboList
            'has been initialized and if not create it.
            On Error Resume Next
            Set rngName = .Range("CboList")
            On Error GoTo 0
            If rngName Is Nothing Then
                .Range("E4").Name = "CboList"
            End If
            '****************************************************
           
            .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Clear
        End With
        Sheet1.Select   'Display the sheet that is being edited

    End Sub

    Private Sub cboProduct_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Me.cboProduct.Value <> "" Then       'Don't procss blank field
            With Sheet2     'CodeName used so if worksheet is renamed it does not affect code
               
                'Next line tests if the value in the ComboBox matches a value in the main list
                'If it does match then exit the sub because it has been selected from the dropdown
                If WorksheetFunction.CountIf(.Columns("B:B"), Me.cboProduct.Value) > 0 Then
                    Exit Sub
                Else
                    'Prevent recursive calls to the ComboBox due to changes made by the code
                    suppressCbo = True
                   
                    'Next line calls the code to populate the ComboBox list
                    'with values that contain the key word
                    Call PopulateCboList(Me.cboProduct.Value)   'Passes the key word to the called sub
                   
                    'Next line tests if the RowSource of ComboBox is assigned
                    If Me.cboProduct.RowSource <> "" Then
                        'If RowSource assigned then adjust the number of options
                        'displayed in the dropdown to the number of available options
                        'but limit the drop down list to a maximum of 20
                        If Me.cboProduct.ListCount < 21 Then
                            Me.cboProduct.ListRows = Me.cboProduct.ListCount
                        Else
                            Me.cboProduct.ListRows = 20
                        End If
                        'Next line Auto display the dropdown list
                        Me.cboProduct.DropDown
                    End If
                    'Cancels Exit from the ComboBox (Occurs if only key word entered)
                    'and entire value in comboBox not found in the full list in column B
                    Cancel = True
                End If
            End With
        End If
      suppressCbo = False
    End Sub

    Sub PopulateCboList(strTofind)  'strTofind is the key word entered in the combo box
       
        Dim rngFiltered As Range    'Dimension variable for the visible data after filtering
       
        With Sheet2     'Sheet with tab named "Stock"
            'Next line clears column D.
            .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Clear
           
            'Next line turns Off AutoFilter (Ensures filters are cleared)
            .AutoFilterMode = False
           
            .Range("B1").AutoFilter     'Turn on AutoFilter (Ready for applying filter)
           
            'Next line filters on the Keyword using asterisk wild cards
            .AutoFilter.Range.AutoFilter Field:=2, Criteria1:="*" & strTofind & "*"
           
            'Next line ensures that at least one line is displayed after filtering
            '(ie. key word actually exists in the list)
            If WorksheetFunction.Subtotal(3, .AutoFilter.Range.Columns(2)) > 1 Then
           
                'Following line assigns the visible data (after filtering) to a range variable
                Set rngFiltered = .AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible)
               
                'Following line turns AutoFilter Off.
                '(Does not affect the visible range already assigned to the range variable)
                .AutoFilterMode = False
               
                'Copy and paste the Filtered data to column D on same worksheet
                rngFiltered.Copy Destination:=.Cells(1, "D")
               
                'Update the defined name to the new pasted range (in column D)
                .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Name = "CboList"
               
                'Reset the RowSource of the ComboBox
                Me.cboProduct.RowSource = "CboList"
            Else
                'Following code if a key word is not found in the list
                MsgBox "Key word " & strTofind & " not found in valid list."
                Me.cboProduct.Value = ""
                Me.cboProduct.RowSource = ""
            End If
        End With
    End Sub

    Private Sub btnSave_Click()
        Dim r As Long
       
        r = LastRowOrCol(Sheet1, True) + 1
        With Sheet1      'CodeName used so if worksheet renamed it does not affect code
            If IsDate(Me.txtDate) Then
                .Cells(r, "A") = DateValue(Me.txtDate.Value)
            End If
            .Cells(r, "B") = Me.txtInvoice.Value
            .Cells(r, "C") = Me.txtCustName.Value
            .Cells(r, "D") = Me.txtD_O.Value
            .Cells(r, "E") = Me.cboProduct.Value
            .Cells(r, "F") = Val(Me.txtQuantity)
            .Cells(r, "G") = Val(Me.txtRate)
           
            Call ClearForm
           
            'Following code will scroll rows so that last 10 rows are visible
            If r - 10 > 0 Then
              r = r - 10
            Else
              r = 4
            End If
           
            ActiveWindow.ScrollRow = r
           
        End With
       
    End Sub


    Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Me.txtDate <> "" Then        'Don't test for date if blank field
            If IsDate(Me.txtDate.Value) Then
                Me.txtDate = Format(DateValue(Me.txtDate), strDateFormat)
            Else
                MsgBox "Invalid date. Please re-enter."
                Cancel = True
            End If
        End If
    End Sub

    Sub ClearForm()

            Me.txtDate = ""
            Me.txtInvoice.Value = ""
            Me.txtCustName.Value = ""
            Me.txtD_O.Value = ""
            Me.cboProduct.Value = ""
            Me.txtQuantity = ""
            Me.txtRate = ""

    End Sub

    Function LastRowOrCol(ws As Worksheet, bolRowCol As Boolean, Optional rng As Range) As Long
        'Finds the last used row or column in a worksheet
        'First parameter is Worksheet
        'Second parameter is True for Last Row or False for last Column
        'Third parameter is optional. Use to find the last row or column in a specific range
       
        Dim lngRowCol As Long
        Dim rngToFind As Range
       
        If rng Is Nothing Then
            Set rng = ws.Cells
        End If
       
        If bolRowCol Then
            lngRowCol = xlByRows
        Else
            lngRowCol = xlByColumns
        End If
       
        With ws
            Set rngToFind = rng.Find(What:="*", _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=lngRowCol, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
        End With
       
        If Not rngToFind Is Nothing Then
            If bolRowCol Then
                LastRowOrCol = rngToFind.Row
            Else
                LastRowOrCol = rngToFind.Column
            End If
        End If
       
    End Function


    Regards, OssieMac

    • Marked as answer by huzzug Sunday, May 3, 2015 4:25 PM
    Thursday, April 30, 2015 10:15 PM

All replies

  • I am thinking that you could use a textbox for the keyword and then populate the dropdown from the main list based on the keyword.

    I would need to see the list and think more about how to populate the dropdown. Could be done with a Find loop or maybe advanced filter depending on the probable number of matches.

    If you want some example code then can you upload a copy of the list and then answer the following questions.

    What type of DropDown do you prefer? (Select from below. If more than one option is suitable then indicate in order of preference.) 

    1. Data validation on a worksheet
    2. Forms DropDown (Forms ComboBox) on a worksheet
    3. ActiveX ComboBox on a worksheet
    4. ComboBox on a Userform
    5. ListBox on a worksheet
    6. Listbox on a Userform.
    7. Other (if there is another option then please provide details)

    A few examples of popular key words that might be used so that I can get an idea of how many options I might be looking at.

    Guidelines to upload a workbook on OneDrive:

    1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Do NOT fill in the form; Select "Get a Link" on the left side.
    10. Click the button "Create a Link"
    11. Click in the box where the link is created and it will highlight.
    12. Copy the link and paste into your reply on this forum.

    Regards, OssieMac

    Tuesday, April 28, 2015 12:55 AM
  • Here's the file. I want it so that within sheet 2, under product name, when the user enters a product name, the list automatically filters the list from sheet 1 based on the keyword. I initially thought it to make it so that the person enters like normally he would line by line, but if it creates a lot of stress on the system, then I could switch to a userform where the user enters under appropriate headings, and with a command button the details get populated in the sheet 2 line by line. 

    Either which ways, I want the feature to dynamically alter the drop downbox / userform of inventory based on the keywords entered.

    Tuesday, April 28, 2015 5:38 AM
  • Just giving you an update.

    I have downloaded the file and am well under way with the solution. Just needs a little tweeking but it is getting towards bed time in my part of the world so it will be tomorrow before I finish.

    I have developed it so you will be able to enter the key word in a combo box on a userform and then it will display the dropdown list of the options that contain the key word. All fields will then be populated on the Userform and Saved to the worksheet with a button.


    Regards, OssieMac

    Tuesday, April 28, 2015 11:16 AM
  • Test the file at the link below and see if it will satisfy your requirements.

    Not sure of your expertise with VBA but if the workbook you uploaded is just an example from another workbook then you can export the Userform and Import to another workbook. Simply select the Userform in the Project Explorer (left column in VBA editor) and select File -> Export and follow the prompts. Then open the new workbook and select File -> import and follow the prompts. (Export and Import includes the Code with the Userform)

    If you do Export and Import then you will need to edit the code with the worksheet names. I have used the CodeNames (Sheet1 and Sheet2) for the sheet names because it allows the user to change a worksheet tab name without affecting the code. To see the CodeNames, in the Project Explorer (left column of VBA editor) the code name is the name NOT in brackets and the user given tab name is the name in the brackets. If you need to change the CodeNames in the code then use Edit -> Replace to ensure you get all instances.

    http://1drv.ms/1Jz5SZe


    Regards, OssieMac

    Tuesday, April 28, 2015 11:53 PM
  • As an addition, could you explain how did you work out the formula's ( just an overview is enough as i'm also learning) as you used the combo box to generate a list of items containg the keyword in sheet 1.
    Thursday, April 30, 2015 1:49 PM
  • As an addition, could you explain how did you work out the formula's ( just an overview is enough as i'm also learning) as you used the combo box to generate a list of items containg the keyword in sheet 1.

    If this answers your query satisfactorily then please mark it as the answer so that the moderators know that the thread has been satisfactorily handled.

    The DropDown list is created with code using AutoFilter. It emulates what you could do manually like the following.

    1. On worksheet "Stock" Turn on Autofilter
    2. Select DropDown on column B and select Text filters (above search field) then select Contains
    3. Enter keyword in the field (eg. dry)
    4. All data containing the word "dry" is displayed
    5. The visible data is then copied to column D (Select the data then select Find & Select -> Go To Special -> Visible cells only and copy then select cell D1 and Paste).
    6. Data from row 2 is the list used for the ComboBox RowSource.
    7. You can also add your own wild cards. dry*screws returns all rows containing dry and screws
    8. Note that you can use the wild card the same way in the ComboBox.

    Not sure of your expertise but to display the code associated with the Userform:

    1. Alt and F11 to open the VBA editor
    2. Right click Userform in the Project Explorer (Left column)
    3. Select View code.

    Below is a copy of the code with comments added. I think this is the best way to explain so I hope you can follow but please get back to me if you still have more questions.

    You could delete all of the code in the Userform module and replace with the code below and it will be easier to read with the comments all showing up in green. (Very difficult to read with all the same color.)

    The RowSource for the ComboBox is set to a Named range. This named range gets updated by the code when the new list containing the key word is created. The Initialize event for the Userform tests if the named range exists and if not then it creates it. This only occurs once and then the name remains saved with the work book.

    Option Explicit

    Const strDateFormat = "dd-mmm-yyyy"     'Edit date format to required format to display on Userform

    'Following line is probably superfluous because we don't have
    'any change or after update events but is included in case of
    'further modification of the code that includes these events.
    Dim suppressCbo As Boolean             'Used to suppress recursive calls to the event code

    Private Sub UserForm_Initialize()
        Dim rngName As Range
        With Sheet2
            '****************************************************
            'Code between asterisk lines tests if name CboList
            'has been initialized and if not create it.
            On Error Resume Next
            Set rngName = .Range("CboList")
            On Error GoTo 0
            If rngName Is Nothing Then
                .Range("E4").Name = "CboList"
            End If
            '****************************************************
           
            .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Clear
        End With
        Sheet1.Select   'Display the sheet that is being edited

    End Sub

    Private Sub cboProduct_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Me.cboProduct.Value <> "" Then       'Don't procss blank field
            With Sheet2     'CodeName used so if worksheet is renamed it does not affect code
               
                'Next line tests if the value in the ComboBox matches a value in the main list
                'If it does match then exit the sub because it has been selected from the dropdown
                If WorksheetFunction.CountIf(.Columns("B:B"), Me.cboProduct.Value) > 0 Then
                    Exit Sub
                Else
                    'Prevent recursive calls to the ComboBox due to changes made by the code
                    suppressCbo = True
                   
                    'Next line calls the code to populate the ComboBox list
                    'with values that contain the key word
                    Call PopulateCboList(Me.cboProduct.Value)   'Passes the key word to the called sub
                   
                    'Next line tests if the RowSource of ComboBox is assigned
                    If Me.cboProduct.RowSource <> "" Then
                        'If RowSource assigned then adjust the number of options
                        'displayed in the dropdown to the number of available options
                        'but limit the drop down list to a maximum of 20
                        If Me.cboProduct.ListCount < 21 Then
                            Me.cboProduct.ListRows = Me.cboProduct.ListCount
                        Else
                            Me.cboProduct.ListRows = 20
                        End If
                        'Next line Auto display the dropdown list
                        Me.cboProduct.DropDown
                    End If
                    'Cancels Exit from the ComboBox (Occurs if only key word entered)
                    'and entire value in comboBox not found in the full list in column B
                    Cancel = True
                End If
            End With
        End If
      suppressCbo = False
    End Sub

    Sub PopulateCboList(strTofind)  'strTofind is the key word entered in the combo box
       
        Dim rngFiltered As Range    'Dimension variable for the visible data after filtering
       
        With Sheet2     'Sheet with tab named "Stock"
            'Next line clears column D.
            .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Clear
           
            'Next line turns Off AutoFilter (Ensures filters are cleared)
            .AutoFilterMode = False
           
            .Range("B1").AutoFilter     'Turn on AutoFilter (Ready for applying filter)
           
            'Next line filters on the Keyword using asterisk wild cards
            .AutoFilter.Range.AutoFilter Field:=2, Criteria1:="*" & strTofind & "*"
           
            'Next line ensures that at least one line is displayed after filtering
            '(ie. key word actually exists in the list)
            If WorksheetFunction.Subtotal(3, .AutoFilter.Range.Columns(2)) > 1 Then
           
                'Following line assigns the visible data (after filtering) to a range variable
                Set rngFiltered = .AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible)
               
                'Following line turns AutoFilter Off.
                '(Does not affect the visible range already assigned to the range variable)
                .AutoFilterMode = False
               
                'Copy and paste the Filtered data to column D on same worksheet
                rngFiltered.Copy Destination:=.Cells(1, "D")
               
                'Update the defined name to the new pasted range (in column D)
                .Range(.Cells(2, "D"), .Cells(.Rows.Count, "D").End(xlUp)).Name = "CboList"
               
                'Reset the RowSource of the ComboBox
                Me.cboProduct.RowSource = "CboList"
            Else
                'Following code if a key word is not found in the list
                MsgBox "Key word " & strTofind & " not found in valid list."
                Me.cboProduct.Value = ""
                Me.cboProduct.RowSource = ""
            End If
        End With
    End Sub

    Private Sub btnSave_Click()
        Dim r As Long
       
        r = LastRowOrCol(Sheet1, True) + 1
        With Sheet1      'CodeName used so if worksheet renamed it does not affect code
            If IsDate(Me.txtDate) Then
                .Cells(r, "A") = DateValue(Me.txtDate.Value)
            End If
            .Cells(r, "B") = Me.txtInvoice.Value
            .Cells(r, "C") = Me.txtCustName.Value
            .Cells(r, "D") = Me.txtD_O.Value
            .Cells(r, "E") = Me.cboProduct.Value
            .Cells(r, "F") = Val(Me.txtQuantity)
            .Cells(r, "G") = Val(Me.txtRate)
           
            Call ClearForm
           
            'Following code will scroll rows so that last 10 rows are visible
            If r - 10 > 0 Then
              r = r - 10
            Else
              r = 4
            End If
           
            ActiveWindow.ScrollRow = r
           
        End With
       
    End Sub


    Private Sub txtDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        If Me.txtDate <> "" Then        'Don't test for date if blank field
            If IsDate(Me.txtDate.Value) Then
                Me.txtDate = Format(DateValue(Me.txtDate), strDateFormat)
            Else
                MsgBox "Invalid date. Please re-enter."
                Cancel = True
            End If
        End If
    End Sub

    Sub ClearForm()

            Me.txtDate = ""
            Me.txtInvoice.Value = ""
            Me.txtCustName.Value = ""
            Me.txtD_O.Value = ""
            Me.cboProduct.Value = ""
            Me.txtQuantity = ""
            Me.txtRate = ""

    End Sub

    Function LastRowOrCol(ws As Worksheet, bolRowCol As Boolean, Optional rng As Range) As Long
        'Finds the last used row or column in a worksheet
        'First parameter is Worksheet
        'Second parameter is True for Last Row or False for last Column
        'Third parameter is optional. Use to find the last row or column in a specific range
       
        Dim lngRowCol As Long
        Dim rngToFind As Range
       
        If rng Is Nothing Then
            Set rng = ws.Cells
        End If
       
        If bolRowCol Then
            lngRowCol = xlByRows
        Else
            lngRowCol = xlByColumns
        End If
       
        With ws
            Set rngToFind = rng.Find(What:="*", _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=lngRowCol, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
        End With
       
        If Not rngToFind Is Nothing Then
            If bolRowCol Then
                LastRowOrCol = rngToFind.Row
            Else
                LastRowOrCol = rngToFind.Column
            End If
        End If
       
    End Function


    Regards, OssieMac

    • Marked as answer by huzzug Sunday, May 3, 2015 4:25 PM
    Thursday, April 30, 2015 10:15 PM