Hi All,
I'm new here and this is my first post. So please excuse any errors in procedure.
I'm trying to make a Macro which allows the user to enter a keyword in a text box, click a command button and the macro then filters the results from a dataset (in a table on another sheet) using Slicers and returns all entries which have the word (or a near match).
I'm using a separate column in the data set which is fed from the text box with the formula - ISNUMBER(SEARCH(Keyword,[@Description])) where Description is the field in which to search. This returns a True / False basis the word being available in the description and the Macro selects the True slicer.
The problem I face is when a word which is not in the description field is entered. What I want to understand is how to do the following -
1) Allow near matches - if the person searches for "Airlines" instead of "Airline" the result should show cases with the word airline present
2) Error handling - If the person enters a word not present in the description field, the marco should through up a pop-up saying the word is not present and ask the person to enter another word.
The code I am using is given below. I have also attached a sample file. Any help would be appreciated.
I'm new here and this is my first post. So please excuse any errors in procedure.
I'm trying to make a Macro which allows the user to enter a keyword in a text box, click a command button and the macro then filters the results from a dataset (in a table on another sheet) using Slicers and returns all entries which have the word (or a near match).
I'm using a separate column in the data set which is fed from the text box with the formula - ISNUMBER(SEARCH(Keyword,[@Description])) where Description is the field in which to search. This returns a True / False basis the word being available in the description and the Macro selects the True slicer.
The problem I face is when a word which is not in the description field is entered. What I want to understand is how to do the following -
1) Allow near matches - if the person searches for "Airlines" instead of "Airline" the result should show cases with the word airline present
2) Error handling - If the person enters a word not present in the description field, the marco should through up a pop-up saying the word is not present and ask the person to enter another word.
The code I am using is given below. I have also attached a sample file. Any help would be appreciated.
Code:
Private Sub getresults_Click()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
Worksheets("VBPDeals").Activate
Application.GoTo ActiveSheet.Range("A1"), True
iskeyword
End Sub
Sub iskeyword()
With ActiveWorkbook.SlicerCaches("Slicer_IsKeyword")
.SlicerItems("TRUE").Selected = True
.SlicerItems("FALSE").Selected = False
End With
End Sub