Video instructions and help with filling out and completing Form 2220 Toggle

Instructions and Help about Form 2220 Toggle

Welcome to part 12 of this tutorial series on Excel VBA userforms this video is gonna explain how to use toggle buttons it's a fairly short video because toggle buttons are fairly straightforward there are a lot like checkboxes in terms of the way they work so explain how you can draw a toggle button on the form and then how to change some of its basic properties explain how to write code that tests the value of a toggle button to perform different actions and then write was the end of like so now to set the default value with the toggle button when the form first launches so there's not much to do but let's get started so here's a quick example of a toggle burn this one lets the user choose whether the film they're adding into the list should be sorted by its ID number which I'll show you in just a moment or by its title so you'll see that if I click on the toggle button it switches between one of two different states either sorting by title or by ID so although they look quite different they're functionally identical to a checkbox they've got a true state and the false state they can also be set up to be triple States which elastins have a null value although I haven't done that one for this particular example so I do choose sort by title and click add to the list I'll see the new phone gets added in sorted alphabetically by is title we've also indicated that the title column has been sorted by adding an asterisk to the end of the column name this is picked upon when the form is not loaded if I click OK and show the form again it detects either the title column has been sorted to set the default value of the toggle boom so they're not particularly complicated I'm a few little things to do here let's get stuck him to start with I need to add an extra column of data to the worksheet so I'm gonna pick column a for this I'm gonna call it ID and then just type in the numbers one two three four five so these just represent the current positions of the films in list and that will help us to get back to the original sort order later on when we start sorting by the title column instead we also need to make sure that when a new film does get added to the list by the form then it calculates the next ID number for the film as well so that's about to be visual basic editor and we'll go to the code behind the apps list button and then find the code for the app data to list procedure so we can right click and choose definition then we just need to add in an extra line of carriage that adds in the next ID number in the sequence so at this point I'll be in the next available blank cell in column B and I want to have the ID number two column a so I need to say active cell dots offset zero comma minus one so that's one column to the left dot value is equal to active cell dot offset minus one comma minus 1 so that's the cell that's one column to the left and one row above the current l dot value plus one what that will do is out in the next ID number in the sequence just to get that a really quick test if I switch back into Excel head back to the menu sheet and show them the form on screen I'll just fill in any old random details it genuinely does not matter at this point just to make sure it works I'll just choose a couple of options and then click Add to list I'll find the next idea number of the sequence gets added in automatically so let's click OK how about sheet one and we'll just delete that Tesoro now what we can use get on and draw the toggle button on the form so back into the visual basic editor and we'll get on with actually drawing the button you'll find the toggle button in the tool box just as usual so I can click on the tool and then click and drag in the form to draw one I'll then change a couple of its basic properties so I'm going to change its name property first of all so it's called sort by title I'll change its caption so that it's called sort by ID so in the form first appears the caption will be saying sort by ID but I'll set it up so that when we click on the button the text of the caption changes to say sort by title instead the default value of the toggle bone will be set to false by default just like with a checkbox you can change that to true if you want to I want to set this to false so I want it to be not too sorted by title by default you can also set up a toggle button to be triple staged just like with a checkbox so there's a triple state property there I'm not allows you to have a null value for the check for the tail bone as well as my choose true I can choose to remove the default value altogether and the toggle normal have a and null value but for this particular example I want it not to be a triple state toggle bone I want its default value to be false let's start in the code that will change the buttons caption when its value changes so the simplest thing to do here is to double click on the button to get the default event handler for that