VBA - User Form: Search as you Type using TextBox and ListBox | Search by criteria

foreign [Music] search results in a list box on a user form in Excel VBA the user chooses a Criterion from a combo box then he enters a keyword in the text box and the search results are displayed in the list box the combo box is populated with the column names of the table that contains the data foreign to begin we copied this data into a new Excel spreadsheet and we name this sheet data this data is information about customers and the orders they have placed there are nine columns click on file then save as to save the Excel workbook enter a name and select a location for your workbook click the save as type drop down arrow a list of file types appears select Excel macro enabled workbook Excel adds the xlsm extension to the file name click save now open Excel Visual Basic Editor to create user form and write VBA code click the developer tab in the code group click on Visual Basic but the easiest way to open the Visual Basic editor is to use the keyboard shortcut alt Plus f11 this shortcut works as a toggle so when you use it again it will take you back to the Excel application without closing the VB Editor to create a user form click on the insert user form button on the toolbar you can also click user form on the insert menu a newly created user form will appear anytime you want to access this user form you can double-click on the user form name in the project window the toolbox dialog should also be visible next we will set the properties of the user form to open the properties window click the toolbar button or select your user form and press the F4 shortcut key click the alphabetic tab to display properties in alphabetic order or choose the categorized tab to display object Properties by category to change the dimensions of the user form use the height and width properties the caption property specifies the text displayed in the title bar specify the background color using the back color property now add controls to the user form select the label icon on the toolbox then on the user form where you want to place this control draw a rectangle of the desired size similarly add three more controls a combo box a label and a text box select all the controls then open the properties window and change their heights to 24. you can improve alignment always keep the controls selected and right click on one of them click align then top a label is used to display static text to the user so select the first label and go to the caption field of the properties window now change the text label 1 to Criterion follow the same process to name the second label search select the two labels again and use the font property to modify the size style and font of the text use the text align property to specify how text is aligned in the control now drag listbox control on the user form from the toolbox use the properties window to customize the list box you can change the size of the user form using the sizing handles as soon as the user form is created it is possible to perform a quick display by pressing the Run sub user form button or the F5 shortcut key now let's move on to the VBA code we want this combo box to display all the column headers that are in the data sheet we'd like the combo box lists to be created automatically when someone opens our user form to do that we'll use the initialize event for the user form double-click the user form to open the code in the drop down at the top left of the vbe select user form it may be selected already from the procedure drop down at the top right choose initialize the initialize event code is added to the Excel VBA code module with sub and N sub lines only where the cursor is flashing in the initialize procedure we'll Define a variable to be used in the procedure next we'll add a loop that will visit each cell in the range A1 to i1 on the data worksheet the add item method adds an item to the combo box and our code tells Excel to use the value from the current cell in the range A1 to i1 test the user form with this initialization code list box is a control used to display the list of items in a list in the initialize procedure of the user form add the following code to Define some properties of the list box set the number of columns in the list box to 9. use the column width property to set column widths zero results in a hidden column since the search is done by criteria we need to know and then save somewhere the name of the column in which we are going to search this action occurs when the content of the combo box control changes so double-click on the combo box and it will take you to the VBA macro procedure to be able to use a variable in all the procedures of a module all you have to do is declare it at the beginning of the module declare a variable name Criterion then declare a c variable of type integer at the start of the procedure finally declare a one-dimensional array containing nine letters of the alphabet ranging from a to I if the user chooses an element from the combo box then a search is launched for this element in the range from A1 to i1 in this example the element saw is in the sixth column the it of this column is the capital letter F and we need to keep this in somewhere to use it later the procedure to follow here is to look for the letter F in the column headers table then save it in the Criterion variable you notice that the first index of the column headers array is zero therefore the letter F is in index column C minus 1. here is the code just for testing We'll add a line of code that displays the columnid in cell K1 add this line of code to clear items from list box when combo Box contents change when the user selects a new Criterion it is better to empty the text of the text box for a new entry finally use the set Focus method to move Focus to text box 1 so that all user input goes to that object Let's test this code now write the code that enables the search by typing some text into the text box this is because you would need to add a script in the text box change event which means it will run whenever a character is added or deleted double-click on the text box the change event procedure will be default an error resume next allows your code to continue running even if an error occurs resume next does not fix an error it just ignores it as soon as the change event fires clear the list box next declare two integer variables R and last row in the last row variable put the value of the last non-empty line of the table found in sheet 1. now establish a for Loop the loop starts with the row variable assigned the value 2 and incremented on each pass until it reaches the value last row next we want to count the number of characters typed by the user in text box 1. so use the Lin function which returns along containing the number of characters in a string then assign it to a variable a before entering text in text box 1 the user selects a Criterion we therefore know in advance in which column the search will be carried out in this example the variable Criterion is equal to f it is assumed that the user has entered in text box when a string of length 2. so the variable a will contain the value 2. to access this cell which is in sheet 1 we use the variable R which contains the row number and the variable Criterion which contains the name of the column let's add the value property to get the value of this cell then we use the left function to extract a substring of length 2. the first argument is the string you want to extract the substring from the second argument is the integer to specify the length of the substring the resulting substring is pH if we compare this substring with the text entered by the user in text box 1 we see that they are different so we go to the next cell with r equal to 3. here we see that the two strings are equal so let's add line three to list box one in the code part use the if statement to test if the two strings are equal if the expression is true then use add item property to add a new row for each record and populate column zero here is the result of this code remember list boxes are zero based so the first item is really at position zero use list method to populate the remaining columns you have a total of nine columns to complete let's run this code that works well you notice here that list box 1 is filled despite emptying text box one to solve this go back to the VBA code and add these lines of code right after the on error resume next line check if text box is empty if so empty list box 1. then add the exit substatement to exit the procedure without executing the rest of the code that follows we want to search for a city whose name begins with y typing a capital yields four results but when we write a lowercase y the list box displays no results we therefore conclude that the comparison is case sensitive to solve the problem use the VBA function UK switch converts the letters of a text or a string to uppercase here is the result the list box only displays column headers if you use row source otherwise they are not available the best way to add column headers and it's not a great way is to add labels above the list box columns one advantage is that you can use the click event of the label if you want to implement something like sorting foreign [Music]

Channel: hay kel

Views: 38,378

Keywords: excel vba userform, search vba, vba listbox, combobox, save macro excel, initialize event, criterion, vba ucase

Id: wwlVYiJxRAw

Length: 13min 23sec (803 seconds)

Published: Wed Jun 14 2023