Excel VBA Forms Part 7 - ListBox Controls

welcome to part 7 of this excel vba userforms tutorial this part of the series is going to talk about list box controls which are just similar enough to combo boxes that if you've watched the previous part of the series you'll be right at home with the basics already so we're going to cover these fairly quickly how to draw them format list boxes how to populate the list using both the row source and the list properties and then how to refer to the selected value in a single selection this box the thing that makes this box is massively different to combo boxes is the ability to allow multiple selections so I'm going to show you how you can enable that and then how to loop through the list items and identify the items that have been selected that's going to evolve a bit of looping and a bit of a talk about arrays there as well the last little part the video is going to quickly show you how you can add new items to a list while the forms running so changing that at run time so a few things to do here let's get started I'd like to run over the basics of using this box as relatively quickly mainly because they work a lot like combo boxes too at a basic level so if you've watched the previous video in the series which hopefully you have then you'll already know a lot about how this box work so the example were going to use is we're going to add an extra column to our list of film data which stores the film genre or eventually genres and again this list is going to be populated based on data we've already added to the spreadsheet so if I look at my lists sheet I've created a simple genres list with all the possible genres there may be more to Adelaide's run so we'll deal with that as part of the video one thing I haven't done with this example is I haven't given this list a range name we're not going to use range names quite so much to reference cells there's going to be based on a much more dynamic flexible list so the starting point this video is how to draw a list box on the form so I set back to the visual basic editor and then we'll open about form and get to the job of adding a list box I'm going to draw my listbox in a separate section of the form to keep it separate from the basic details so to do that I'm going to increase the width of the form first of all and I'm going to draw a separate frame and the frames not necessary it's just nice way to group together all the controls that will be related to the film genres so having drawn the frame I'm going to give it on a sensible name so I'm going to call this one film genres frame and then we'll also give it a sensible label as well which will be something simple like film genres there we go and here's the caption film genres and then inside there I'd like to draw my list box and that's going to make their the frame itself a bit taller first of all then from the toolbox we're going to find the list box tool which should be sitting right next door to the combo box so you click on the tool as usual and then drawn on the form will get the list box a sensible name I'm going to call this one film genres and then the next step is to populate it with a list of options populating the list box works in exactly the same ways populating a combo box so a simple way to it will be to set the row source property of this box so looking into the properties window I can find the row source and I can just type in if I know the cell references and the name of the worksheet which is lists I can just type in the name of the sheet followed by an exclamation mark and then the cell references which in this case was G - 2 g 18 I believe so soon as I do that I'll see that my list box gets populated I've got some basic formatting properties I can modify as well so just like I have with the with the combo box I can modify the list style so I can modify it from plane to option which makes it look a little bit neater and that's the simplest way by far to populate the list box as we mentioned in the previous video the row source property although it's quick and easy to use makes your list a little bit inflexible once the row source property has been set you can't then modify that list at runtime so you can't add items or remove items from the list for the purposes of this video we need that to be to be allowed so we're going to remove the row source property of the value we've added to the row source property and instead we're going to populate the list using the list property in much the same way as we did for the combo box in the previous video so that involves going to the the initialize event of the form so I can click on the forms background and just hit the f7 key to go to this code view so I've got all the same code that I had in there previously and then all we're going to at this point is we're going to add in a line of code that populates the film genres list so I'm going to use a relative range selection technique to do this I'm going to set the film genres list property to be equal to WS lists dot range g2 that's the top seven little the cell that's got the first genre in it and the bottom sound' list I can reference by saying WS lists dot range g2 or G wanted to really match in this case actually if I refer to G one last that the very top cell in list g1 isn't the cell though interested in of course the cell that I'm interested in is a cell this at the end of the list from g1 in a downwards direction make sure I refer to the value property again these techniques we've discussed in a lot of detail in the previous series the Excel VBA series this is an entire video all about rating selections and why how this works so if you need some more detail on that that's probably the best place to start I'm going to tidy up a little bit just get rid of some some sample code I left in there previously just to keep this a little bit neater and tidier than it currently is so having in all that what we should find now is if we go back to the the Excel workbook and then just head back to the menu sheet and launch the form that little we populated with all of the items from column G effectively from our list sheet the next thing we need to be able to do is refer to the value that the user selected in the list box and once again that works in a very similar way to combo boxes so let's go back to the code behind our add to list button and we'll go to our add data to list procedure and what I'd like to do as well as having added in all the details from the text boxes in the combo box is finally adding the details from the list box as well so here we're going to revert the cell that is five columns across from the from the film's name so add to cell at zero comma five dot value equals then I can simply say film genres dot value just as simple as that exactly the same as for basic comma boxes and text boxes so having done that what I'm going to do is go back to excel and then get this one one quick test having to the menu sheet launching the form let's add in Deadpool again and we'll have a gross of 731 million and then the release date was 12 Feb 2016 and the certificate was 15 in the UK so we're using UK certificates here 15 and then finally I can select one genre from the list let's go with well if it falls into several doesn't it let's let's choose action to start with and choose add to list so we'll see at this point we get the value of a single selected item added into the appropriate cell so that's the absolute basics of referencing values in this box one feature which really just distinguish a list box from a combo box is the ability to select multiple items to do that you can change the properties of this box in the properties window look for the multi select property by default they'll be set to multi slapped single we can change that to one of two different options if I choose multi select multi and then run the form what I can is select any number of individual items one by one by one the other option now was extended so I go back to the design view the form and select list box again and then I'll change the multi select property to extend at this time what I can do this time firing the form is if I select any single item I can then hold down the shift key and click on any other item in the list and it will automatically select everything in between so those are the three different options I'm going to switch this back so it's multi select multi rather than extended and number me to talk about how we can reference the individual items we have selected now although we do have the ability to select multiple items in the list box we can't yet return the values we've selected so I were to check these three boxes and click Add to list we'll find that all of the details get added to the worksheet except for any other genres the reason this is happening is because we're still referring to the value property list box and the value property doesn't actually return anything when you have a multi value list box so we need a different approach let's head back to the visual basic editor and go to the add to list button and we're going to add a bit of extra code now that's going to refer to all the selected items in our list box to do this we're going to write a function which returns a comma-separated string of all the selected items in list box I'm going to put this in just below our Sibley team which adds our data to the list and then we'll call it into here a little bit later on so I'm going to start by declaring my private function which will be called something like select two genres and then we're going to return a string from this so we'll set the data type or the return type as a string now the approach that we have to take here is to loop through all of the items contained within the list and check for each one whether it has been selected this whole approach relies on two separate properties of our list box so I refer to my film genres list box one of the properties we need to refer to is the list property this is this returns an array which contains the values of all the items this contains the name the words action-adventure comedy at cetera the other property we have to align is the practical selected now selected also returns an array containing the same number of items as the list array but all this contains the value true or false that respess lies whether or not the item has been selected so this approach helps a lot of you know something about arrays and again in the original excel vba tutorial series there's an entire video all about how arrays work so we're just going to give you the basics here of how to make this loop work so let's start by declaring a variable which we can use to keep track of the number of the item we're on I'm going to call my variable something nice and simple like I as an integer you might want to give it a more descriptive name like loop counter for instance but I will do for me now we have to begin a loop that will count through all of the individual items in the list so we're going to use a for next loop do that so I'm going to say for I equals then the index number of the first item in the list will have an index of zero it's a zero based index and then we have to carry on looping until we reach the the highest numbered item in the list so one way to refer to this is to refer to the film genres a dot the list count property this counts literally the number of provides in the list problem is because we were using a zero based index we need to subtract one from that number to loop through the correct range now if you already know something about arrays you may be familiar with a couple of functions that can calculate the lower bound and upper bound index numbers of items in an array so rather than having to worry about remembering this is a zero based index and remembering to subtract one from the list count what we could do instead is simply say or I equals L bound and then refer to the list property of our film genres list so film genres are lists to you bound films on this dot list so this will simply work out what the lower bound and upper bound index numbers of the items in the array are so we only need one of these two techniques either will work personally I prefer to use the L bound and new ban functions that means I have less work to do to remember what a what the base of the index numbers are all we've got to do now is close off the loop to say next I and then we can sort out how we refer to the individual items in the list now the test if an item has been selected we can refer to the selected property of list box so we can use an if statement to check if film genres dots selected and the slider property requires the index number of the item you're testing for so our index number is held in the integer variable the I counter variable and this function or this property will return a boolean value true or false so we could say if film genres not selected I equals true then do something but because it's boolean logic we don't need the equals true part the sixth synced version is if film genres stop selected then we'll add in the end if statement and then we can work out how to start building our list to build up the list we're going to reference the current list of items that we've currently got selected and then concatenate to that they're our new item plus a comma at the end so we can use the name of the function to this women prefer two selected genres equals it's self selected genres and the new item that is selected so to refer to the item that has been selected we need to refer to our film genres list box again and then refer to the list property this time and again pass into this the index number the item that we've established has been selected all we have to do then is add on a comma at the end ready for the next item we'll add in to make sure that separated with a comma what that will leave us with at the end is a list with a rogue extra commerce took right at the end so we're going to do is one last a little bit of string manipulation just after we've finished looping through all of the items we're going to say selected genres equals all the characters to the left of selected genres where the length of the string I want to return is equal to the length of selected Yama's minus one so I'll just return everything except for that last rogue comma at the end all we have to do now then is call this function in the Atlas subroutine so where we've previously refer to the films on this value we can replace that with a call to the Select genres function so this will simply change the value of the cell to be equal to the result returned by that function what we can do now is go back into Excel and give it another test so we can go back to the menu sheet and show the form on screen and we'll add in a new film to list so that death will not so new a film after all we've out of this one so many times in this set of videos maybe so I come to avoid having to remember the release dates and gross values for lots of different films so just help me out more than you really so the 15 certificate and then I should be able to select multiple different items here so we'll action a bunch of comedy click the add to list button and finally we end up with a list of all the selected genres one thing we do have a problem with here and in fact something we haven't mentioned yet in this video is what happens if the user doesn't select an item from the list at this point if I clip my add to list button my supper tune is going to fail there's purely to do with how I've tried to trim off that trailing comma at the end of the list of selected genres so we need to solve this issue I'm going to hit the end button to stop running the procedure then head back to the visual basic editor and go back to the code behind that selected genres function now the reason the error occurs in the first place is because if I don't select any items in the list the length of the selected genres text is equal to 0 if I subtract one from that I get minus 1 and then I'm trying to return minus 1 characters from the left of a zero length string which clearly isn't going to work so we're going to need to add in a quick if statement just to check if the Len of the selected genres is already greater than 0 only then do we want to subtract 1 from the end to remove this trailing comma so let's get rid of that adding an end if statement and then that should work so let's give this one a quick little test if we go back to excel and then we shall show our form on screen and I don't really care about what values to type in here as long as they're valid and then we can hopefully absol astre thout selecting any in the listbox and we'll just end up with an empty string passed into the cell this function also gives us any way to apply some validation to the listbox we know that if nothing gets selected in the list box then we return an empty string from the function so we can rely on that and use it as part of our everything filled in function that we created in an earlier part of the series so just scrolling back up a little bit here's the everything built in function we're going right in another little if statement towards the end after we finished looping through the controls in the film details frame we're going to check if selected genres equals an empty string then I'll add in my end if statement so we know that that means that these hasn't selected in the items so the important thing that we can do is say everything filled in equals false so we prevent the user from continuing we could also do some formatting changes to indicate to the user that something's gone wrong now it's possible at this point to change the back color or the fore color of our list box the problem is getting the color to change back afterwards I want to show you quickly what I mean by that I'm going to say film genres dot back color equals RGB pink let's keep it consistent with everything else and we'll just give this one a quick test if I head back into Excel now and then show the form on screen and just click my atlas button without typing in or selecting anything from this box so that color changes to the same same color that we've indicated for all the other controls now we've got to the stage in the previous controls we're changing the values back we'll then we reset the formatting but getting that to work for list box is actually remarkably difficult so I'm going to close down the close on the form and head back to the visual basic editor and we want to have a quick look at why this is so tricky so let's have a look at some of the basic events we could use for the list box if I double click on it to get to the code view it'll generate the click event so there's one possible suggestion we could change the film genres list box back color back to white when we click on it while I'm here I want to quickly look at a couple of the other events so let's have a look out the the previous ones we've seen things like the change event so it's just do exactly the same thing in there we will change that color back to white there we can use the after update event all that before update event as well these are all things we're familiar with we've seen before so it's just adding the same piece of code to every single one of these so that should cover all of our bases and whenever we do anything with list box essentially it's going to try to change background color back to white if we go back to excel then and then just try to do the same thing show the phone screen add something to the list without having so that didn't think when we click the check boxes absolutely nothing seems to happen in terms of the back of those events are definitely firing in the background you can write simple tests to prove that but and but the the back color the the the box doesn't change back which is really frustrating I spend hours of my life trying to solve this little issue and so we need to take a different approach to getting this to work so let's close the form down and head back to the visual basic editor and think about what else we could do one possibility although it doesn't look quite as nice would be rather than to change the background color of the list box change the background color of the frame in which it sits so if I go back to the click event of how to list button and then scroll down to the everything filled in function we can change rather than the background color of the films on this list box we can change the background color of the film genres frame instead a map means that with our our film genres events we can then change the color of the frame back to the way it was before so let's get rid of some of these event procedures I'll get rid of the click event and we'll get rid of their before and after update events instead we'll just rely on the change event so I'll change the film genres frame back color back to RGB white so just to test this out if I head back to excel and then once again show the form on screen and add to this without selecting anything I say it doesn't look quite as nice it looks fairly horrific in fact but the important thing is that we can actually get the frame color to change back on the change of the listbox so it's not the most elegant solution in the world but it kind of works and I think that's what we're going to stick with just for this video the final thing we were going to do in this video is give the user the opportunity to adding new items to our list box should they not find eyes in there after so for instance there's no superhero genre in the list so the user might want to add that so I'm going to achieve that by Odin a text box and a simple button into the same frame so let's go back to the forms design view first of all and adding those extra items we'll start by making this frame a little bit wider just so we've got enough room for the the objects we can write in I'll start with the label at the top so we'll add in a label which will enter will change its name so it's called a new genre label and it's caption can be something simple like a new genre then we'll have a text box below that so we bring about the toolbox and draw a text box below and we'll call this one just new genre then we'll have a button below that that these can click on to add that into our list and we'll change the buttons name so that's called add new genre and the caption can be something similar okay so those are the three new objects we're going to draw all we need to do now is add a bit of code to the add new genre button so that it will add whatever the user typed in into our existing list so we can double click the add new genre button to launch on generators click event handler and then we should check that the text box has been filled in first let's say if new genre dot value is not equal to an empty string then also end if and then we will say film genres dot add item and then all you need to do is specify or refer to the value of the new genre text box or new genre dot value we probably wanna make sure that we don't inadvertently at the same item twice so once we've added this new genre we're going to clear its contents by saying new genre dot value equals energy shrink and that will achieve the basics so if we then go back to test this on the form what we should find when we launch the form is if there's a genre in the list that we don't don't have so for instance superhero as I'm saying let's say sue Barrow if I click the add new genre button that'll get added into the bottom of the list and I can now select it just in the same way that any other item can be selected one small problem with what we've done here is that although we've successfully added an item to our list box if we close the form down and then reopen it but item will have disappeared now simply because this list box is populated based on value stored in cells in the worksheet so what we should also do if we're storing the item in our list box we want to make it permanent we should also add that value to the other cells in the worksheet so I'm going to close down the form and then head back to the visual basic editor and then we can go back to the add new genre button and add a new bit of code before we clear the contents of the new genre text box we should write some code that will add it to the end of the list on the worksheet now the list of genres is held in column G on the ws lists worksheet what I'm gonna do here is say WS lists dot range g1 dot end Excel down plots offset one comma zero dot value equals new genre dot value and I will have the simplified adding it into the list of cells on the worksheet as well so figure if I can test this one there and then launch the form again and I'm going to add in the superhero genre again and add that to the list so it adds itself in at the bottom if I close the form down and this time reopen it again I'll find that superhero is still in the list we also need to be careful that the user doesn't add a genre that already exists so for instance if I typed in superhero yet again spell it properly and then click the add a new genre button and I get super hero added in twice and that is definitely be cause a problem so that not only gets added to list on the on the forum it also gets added to the list in the worksheet as well so I'm going to manually delete these just for the moment and then I'm going to go back and add some more code that checks if that genre already exists in the list so back into the visual basic editor and then back to the code behind the add new genre button and add a little bit more code inside our current existing gift statement so what we're going to try to do is find the value that the users entered in the text box in the range of cells in column G this is probably easy to achieve we have a variable so I'm going to declare a variable called R as a range this represents a cell in which we find that the genre we're looking for so what we can then do is inside the if statement as long as we have type two value into the text box we're going to try to set R to be equal to WS lists dot range G one dots current region current region refers to the entire block of cells in which range g1 sets so it's almost like going into Excel if I can just really quickly demonstrate clicking on cell g1 and then pressing ctrl a on the keyboard so it returns a range of cells in which g1 sits so to everything up to the next blank row and blank column so within that range I'm looking for the value that the user has typed in so I go back to the visual basic editor sometimes I say dot find and then refer to the new genre textbox value so new genre dot value what I'll end up with them is if the value has been found in the list it will return a reference to that cell to that range of clicked and store it in the range variable if I can't find that value then what that means is that our will contain nothing and I only want to continue adding this item into my list if R has not been found so we can nest another if statement now we can say if R is nothing then I want to add in all these lines of code and then don't forget the extra and if statement writes at the end there so that will neatly avoid duplication in our list if we just give this one a quick test pins of Excel and launch our form one more time let's add superhero and then add a new genre and there it goes if we try to do it again it won't get that in more than once we could probably do with some validation code that maybe changes the color of the label or change the background color of the text box or just clear the contents or pops it with a message but you probably have enough tools in your armory at this point in ads here to work out what you could do from this point on the important thing here is that we avoid the duplication one additional neat thing that we could do is once we've added a new item into our list is we could sort that item alphabetically as bar the list on the worksheet so the next time the form gets loaded is in the correct place alphabetically in the list so to do that let's just close down the form again and we'll head back to the lists sheet and we can run just complete the superhero genre again so we can edit it again later on and then let's head back to the visual basic editor and we're going to go back to the click event of the Avenue genre button and add an extra line of code in just below while we've added the new genre to our list so here we're going to take WS lists range g1 current region dot sort and then there's a couple of parameters to fill in so key one refers to the the essentially the first cell the first sort column if you like so key one column equals WS lists range g1 there's only one column to sort by here the next thing we want to do is make sure that we're going to the excel text that there is a column header in that column so by default it the headers is set to no so we're going to set the header parameter to be colon equals Excel yes just a quick little bit of tidying up so you can read all that on one screen with there we go and then we'll get what we can do just to test this is go back into Excel and then back onto our menu sheet and then show the form on screen and let's have superhero added to the list so that should be added into the list of genres again when the forms loaded is or currently still loaded it's going to be sitting in the bossman list but that's okay for now if I close the form down and then show the form again we should find this time that superhero has been sorted alphabetically into the list if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 57,025

Rating: 4.9340658 out of 5

Keywords: excel vba, vba, forms, user forms, listbox, list box, wise owl

Id: Y3HZT5Jg6bU

Length: 28min 47sec (1727 seconds)

Published: Thu Mar 31 2016