Add a select all option to a list box without accidentally triggering a cascade of change events which leaves you stuck in an infinite loop so we'll take a look at a solution to.
That problem in this video but just before we get started i wanted to take the opportunity to say a big thank you to mark and to everyone else who's chosen to support the channel by.
Becoming a channel member guys everyone here at wiseal sincerely appreciates the support and we hope you're enjoying the content we're producing in return and i'm really enjoying seeing these little.
Icons appear next to people's names in the channel comments anyway onto the solution itself i've started with a brand new blank workbook for this example so you don't need any.
Files to follow along with the video i've saved mine as a macro enabled file and i've got the visual basic editor already open so i'm going to head into there and then start by inserting a new.
Attractive i'm not even going to bother changing the form's name in this example all i'm going to do is add a list box control by finding that in the toolbox click and drag on the form to draw my.
List box and again i'm not going to bother changing the list box name in the properties window but there are two basic properties i do want to change so scrolling down a little bit further i'm.
Going to find the list style property first and i'm going to change the list style from plane to list style option so that will prevent presenters with some check boxes to check when we um.
When we have the multi-select list box displayed i'm then also going to find and change the multi-select property so change multi-select from single to multi-select.
Multi and then the final thing we need to do before we can interact with that list box is populate it with some options there are multiple ways we can do this.
But i'm going to take the simple
Approach for this video we're just going to add some code to the initialize event of the form to add some items to that list box.So i'm going to right click on the background of the form and choose to view its code and then i want to access the initialize event rather than the click event which.
Is the default forms event so from the drop down list up in the top right hand corner i can choose initialize and then i can get rid of the click event entirely i shall need that and.
Then in the initialize event i'm going to start by saying with and then look for my list box 1 object and then inside there i'm going to apply the add item method.
The first item i want to add is going to be the select all option i always want that one to appear at the top of my list and then i can just copy and paste the add item line i'm just going to add.
Three more options it really doesn't matter what you choose to add in here make it the names of your pets or your children or your favorite pizza toppings i'm gonna go with a really inventive add.
Items a and b and c if you can think of anything more exciting than that and you hopefully you won't have to think too hard then go with those instead.
I'll close off my width block by saying end with and then just to give this a quick basic test i'm going to go back to the design view of the form by double clicking on it and then select the forms.
Background and then run it by clicking the run button or pressing the f5 key and hopefully we should see a list of four options that we can check and we can have a multi-selection or a single.
Selection but the basic form is set up
And working next we need to make something happen whenever we select an option from the list box so to get started with that.Let's double click on the list box in design view to generate the default event handler for the list box which happens to be the click event now this would be perfect if we were.
Dealing with a single select list box but with the multi-select list box the click event isn't actually triggered what we need instead using the drop down list at the top right hand corner is the.
Change event so i'm going to select the change event from the drop-down list then we can delete the click event entirely and then just add some basic code that's triggered whenever we change.
Something in the list box let's add a message box that'll be nice and obvious to start with and it's just gonna say list clicked it's gonna be quite an annoying message.
It's gonna pop up every single time we select or deselect an option in the list let's run the form again by clicking the run button or pressing the f5 key and then if we check the select all box.
It says list clicked if we uncheck select all it says list clicked select b unselect b etc so the the message pops up and is triggered every time something changes in this list box.
Now for this example i only want something to happen when i select the select all option at the top of the list so let's add a bit more code to identify the item that we've selected in the list.
Box if we head back to the same procedure we were just working on the change event of list box 1 rather than just showing list clicked let's replace that with a few.
Bits of information from the list box itself so i'm going to refer to list box 1 and then i'm going to refer to the list index property first of all.
So list index gives us the index number of the item we've picked i'll then concatenate that with a space in some double quotes and then i want to concatenate that with.
Whether or not the item i've selected is checked or unchecked so i can do that by saying list box 1 dot selected this returns either true or false depending on whether the item is checked.
Or not and in order to specify the item i'm interested in i've got to pass in the index number of the item i want to interrogate so i'm going to do that by passing in the list index property that.
I've just calculated there so list box one dot selected list box one dot list index and then finally just to finish this off let's also show the value of this item.
So the text we've typed in next to it so to do this we can refer to list box one dot list and once again we've got to specify the index number of the item we we're.
Interested in so i'm going to copy and paste once again list box one dot list index i can just about squeeze all that onto a single line i think and keep it in view.
There we go okay so having done that we could head back to run the user form again so let's run the form.
And then if we check the a box it gives us the index number whether it's checked and what its value is and we can uncheck that again and it switches the the selected property back.
To false so we can clearly see now that we can determine which item we've picked using those properties so to narrow this down to make sure that.
We're only going to run our code when we check the select all box if i close down the form and then head back to the code we were writing we can simply write an if statement that.
Checks if list box one dot list index equals zero so it's a zero based index rather than a one based index then show that message box.
Otherwise we won't do anything so i'll just add in the end if statement run the user form again and we'll see that if we check abc nothing happens but if we check the select all option it.
Triggers our code and shows us that message so there's our basic logic working next we need to add the code that will process all of the other items in the.
List box if we've determined that we've selected or deselected the select all option to do that we can write a simple for next loop to count through the remaining.
Items in the list box so let's start by declaring a simple integer variable at the top of the subroutine so we can say dim i as integer.
And then inside the if statement we can begin our four next loop by saying for i equals and then set the number that you want to start counting from.
I don't want to bother including the select all option so i'm not going to start counting from zero here i'll start counting from number one which will be the second item in my list box.
Then i want to carry on counting to the index number of the last item in the list box and to do that i can say list box one dot list count which will tell me the total number of items so that'll.
Be four and if i subtract one from that property that will give me the index number of the last item let's just close off that loop then i'm.