Working with ListBox Control in Excel VBA

hey guys welcome back to vba 80z my name is lumpamai and in today's video i'll be showing you how to create search autocomplete suggestions also known as predictive search this is a very useful feature to include in your user interface which will definitely help improve your user experience let me give you a very quick demo this is the custom text field that we created from our first video it has a watermark it has an icon and then it has a custom border so once you click on this here and type in any client's name for an example let's say that i am looking for rebecca i'll say re and then it will auto suggest me all these client names for an example well let's say you type in my name or it could be any name starting with v wins it could be mary mary iron mary rose example so this this is what we learned today if you missed our prior videos all these videos will be added to playlists called professional ui slash ux as always project file source codes used within tutorials will be made available absolutely at no cost so you can change them as per your requirement the only request from you is to support my channel so i can keep sharing all these videos and programs so before we begin please do not forget to subscribe to my channel and hit the bell icon for upcoming videos first let me take a few minutes to quickly show you how to use list box control in your user phone i'm gonna quickly insert a new user from here from the toolbox i'm gonna bring in the list box from the properties as usual you can see special effect the border the background color and so on for now i'm gonna make this to each and and here one thing to note here is once you add the list item here to your list box but there are multiple options you can make the user select multiple items single item or extend it extended this basically the user can hold down the control key and select multiple items otherwise multi select will be like as soon as the user clicks on it it's going to get selected if there are multiple items within the user if i load up this form now it's blind add a quick um let me add a button here like this this is a demo and we'll say say me dot list box dot add whatever you add here is gonna be um added to the list box so let's say dummy and then one so once i click on this one item is added right so this is very simple way to add let's say that you have to add multiple items i'll i as long and then we'll loop one to ten maybe this is an example for now like this of one we'll say i so i will become our dynamic number there so before we add it's better to clear out so clear so this will clear out the list box whatever is there like this okay all 10 items so if i click on it it will clear and then re-add them like that okay so that is a simple way to add and let's say that you have to um return the value of what was selected so let's quickly check that as well because you might want to pull what was selected by the user okay so let's see that i have a box here this is an example a text box and whenever the user clicks on this list box item we want to return it into text box one okay one dot value is equal to so we'll say um list box one dot list like this and then here you'll just specify uh since we are already just having uh one item we can just use this list index itself like this okay so it's very simple to use this feature but it's very useful at the same time so i hope that is clear now very quickly now going back to uh we'll change the property of the form now to multi-select if i now as soon as i select it gets selected okay now the other property here the last property that you have here is um extended property so once i load this up and click on this you still select only one if you have to select multiple of them you have to hold down the control key from your keyboard and then click on it it will get selected let's say that you want to return all these selected items uh let me quickly show you that as well i'm just gonna add another button let me just leave this here for now i'm just going to replicate this list box something like that selected items something like that now let's write a quick loop to look through all the items within the listbox one and then we'll try to transfer whatever was selected into the list box too for an example so we'll say dim x as long and then for x equal to so the index starts from zero for the list box um we'll get the count minus one is just to take care because it's just starting from um zero that's why so now we'll say if me dot list box one down selected whatever is selected for that particular index so it's going to look true and then it's going to check if that particular index is selected if it is true then we're going to bring that particular item to this box too we'll say need a listbox 2 dot add item and then here we'll say um need a list box sorry list box 1 dot list and then here was the index is going to be x and then we're going to say zero because we only have one column at the moment okay firing up this again this will add so like this then add here so whatever is selected here you can see it has been brought to this this is how you can add and this is how you can read the data of whatever is selected let's say you want to bring in multiple columns from here uh we can do one thing let's say that we want to add a list like this to the list box three three columns like this okay so we'll just loop from one to ten again and then we'll add this to the list this is an example me dot listbox one dot column column count is equal to uh the number of columns that you want to add you can specify it here like this and then once you have this you can also specify um the the column width and then within this it will just take care by itself so you just don't have to worry too much for this like this with the comma you can specify the width of each of the column very nice now let's say here instead of dummy we'll load the data from um from our excel like this okay so since it's going to start from row number two till let's do it till serial number 10 here so till 11 we'll say this okay this is not x i just changed it from there okay so i'm gonna start it up okay this is not looking good i forgot to add other columns coming back here sorry 30 let's make this 70 each for another one so here this is adding only one item and we're saying this is three right so i just forgot to mention this when you are adding multiple item you know the syntax will change slightly so we'll say add the item and then we'll say um me dot mid.listbox1 dot list and then here you'll specify the index that you want to add that is going to be i since i is starting from 2 we'll just do minus 2 because it's starting from zero okay and then the column number that you want to add to the first will be zero and then it's it's going to be like let me quickly replicate this for three columns right this is for the row and this is for the column so i'll say one and then there's gonna be two like this similarly here this is your axle range the the index row of that we are looking through the ir array and then the column similarly will do two and three let's try this so like this whatever is here is getting added here similarly like we did earlier um you can look through again and get the selected item but in this scenario like this it's gonna just get the first one if you want to bring in um additional columns uh then you'll have to specify what you want to get it from there but in this scenario since we already have only one particular column in the list box too you can specify um just for now we'll just do in this manner just to return the value so there's going to be one then it's gonna be two like this and let's put a breaker here something like this so let's say that these three attempts are selected so you can see four and then the middle name uh the name and then the phone number to demonstrate this let me just delete this um the list box and then i'll re-add them and show you how it's done i'm gonna not gonna go through this one again because we already created this in our first video to this box first i'll get the list box and make it frame here so the idea here is that um when the user form is loaded this list box is going to be hidden okay so we'll make this to flat and we'll make the the background very similar to the user form maybe uh something like this like this yeah uh when you're designing your form let's say that if you have multiple controls here maybe first you put it somewhere you designed them and once you finalize the form you can drag it you know like very close to where the search box is gonna be located okay and the other thing here i think i put a border as well uh it's already set the selection is currently set to signal okay so now this is not going to do anything uh we'll have to write a function to do this search okay so here uh when the text box is clicked this is from uh just for the effects and from our prime video what we're trying to do now is that as soon as any value gets changed within this text box we're gonna use this text box and then we're gonna add all the match items to this particular list box okay so i'll just hold up this now and quickly write one particular very simple function for demonstration ideal way to do is like normally from our prime video if you've seen like there's a uh there's there's a user interface where there are so many lookups happening so many retrievals happening in those scenarios what i would suggest is uh you can use dictionary or you can add those all these items within the memory using like the public object or public variable and then you can read from those uh memories so that way it's like super fun but for now for the sake of learning we're gonna read it from excel file okay so write a function let's call this search text something like that and this is where we're going to take the text to search we'll just put a variable like this text to search as string and then we'll this does not return anything as such okay and now let's look from um we'll declare one more variable here this law let's say 500 rows if you look at this data set it has 500 names here starting from row number two till 501 so we can say 501 and if we just add this now this box we can just it like this um i and then two dot value like this so column number two and then whatever the i is index as it goes the i will keep on looping down like this one by one keep on going down okay so this is by default adding everything but instead of adding everything we want to put a condition here first what i did was here i clear the list box we cleared all the contents within it and there's one handler i put just to take care that if the list box is hidden we want to show that okay there are multiple ways to do that uh doing the comparison in this scenario i'm gonna make the lower case for whatever is there in the text box and whatever is pulled from the excel sheet uh this one as well and then do the quick comparison okay x value is equal to this and then we'll say x value here and then we'll declare this okay and we're gonna keep using this x value down here so l case l case and then list value now we'll say l case is there in the text string this one we're going to compare this so if this lowercase of whatever is passed here it's like this l case of the value okay then we're gonna add the item here to the list box so here the like will be you can either have a star here in the beginning so this will be um just give me one second you got to name that so if you do in this manner uh it's gonna whatever let's say that you're passing um a val you know s then all the names which contain the letter s is gonna return the value but in case you you want to just uh search for the one starting weight whatever you type in so let's say that you type in reb it's going to bring in this value for an example so in that scenario we don't want to put an asterisk in the beginning okay so that is up to you how you want to do it so once it's here we have to add a couple of more handlers we'll say if it's false then this is just to make the our list box visible need a list box dot visible it's called to true and then x we'll just set this true i think this might not be required as well but for the sake of this for now and then we'll this here sometime um you know like you might want to resize it the the list box as per you know like sometimes it will return more names sometimes it's going to return lesser names so if you want to make the the list uh box the height of the list box that is returning the suggestions shorter or you know a taller and you know so you can take care of this in this manner so i'll say me dot list box one dot height so uh for now i did around 260. so if this is around this as long as it's um you know like this is more like a maximum so i want to set uh every time an item is added i want to increase this height to by 10 something like this and then once it reaches you know like around this then it stops adding down okay in the beginning i also set the height standard height to i guess around 50 54 okay so let's give it a try to this one going back to text box one double click on it here we want to use this uh as soon as any value within this text box type in exchange okay so let's search this text and then we'll say um me dot xbox one dot value something like that okay so let's give it a try so as soon as i click on this and say reb doesn't seem to be working just give me a moment let me quickly change certain things i'm gonna switch this and let's bring in all this within this condition only when the condition is true let's do this this value is not blank i think we missed something this is fine this one here's doing the comparison of the string from the from excel whatever is pulled in here based on the loop and if the condition is true if the text box is hidden we turn it to visible if the value is not blank we want to add that value to the list box and we are adjusting the height of the list box based on the number of items that is being added let's start it again okay now it seems to be working okay now we can also quickly set the font color maybe something not completely white but something like this starting with p starting with e r v okay one last thing to take care of here is just to hide the list box here which was commented out earlier once you load this up the list box is gonna be hidden you type on this you start typing in the name it's gonna return it's going to resize based on the list there's a maximum set and then if the list is lesser it's going to sort into this box in our next video we'll visit more design and customization ideas and start creating real life applications if you found this video informative and helpful please do not forget to leave a like and a comment it will mean a lot to me and please do subscribe for our upcoming videos thank you so much for your support i'll see you in our next video bye

Channel: VBA A2Z

Views: 4,484

Rating: 4.9761906 out of 5

Keywords: vba, marcos, microsoft, excel vba, excel vba - list box, list box listbox, excel userform, Autocomplete, auto-complete, in Userform, Suggestions, Predictive Search, texbox Autocomplete, Combobox Autocomplete, List Autocomplete, Dropdown Combobox Autocomplete, Create, Advanced, Userform, VBA UserForm Design, Develope UserForm, user interface, create form, advanced form design, slim, thin, excel ui, latest userform, design, form effects

Id: XjGQ06wyuHo

Length: 20min 43sec (1243 seconds)

Published: Sun Dec 27 2020