In this screencast, I'm going to be revisiting a topic that I've already covered in a previous screencast. But I wanted to show you how we can bypass some of the problems associated with it. That is, when we have conditional drop-down lists, we end up with an issue that I'll show you here in a minute. But in this screencast, I'll show you how we can reset these conditional drop-down lists. In a previous screencasts, I showed you an example related to this, where we've got a category. We have categories here, appetizer, beverage, main course, and dessert. We want the user to be able to choose that category based upon the category that they choose. We want to have a second drop-down list, or Data Validation that's selected from either appetizer, beverage, main course, dessert. Menus here, depending upon the category that they choose here in cell H4. By the way, this is found in a file called reset conditional drop-down lists. What I showed you in the previous screencast is how we can do this. Let's go ahead and first name these different groups. We can do that easily. If I select these cells, I can go Create From Selection. It automatically detects that the name is in the top row, and I can click okay. I'm just going to go ahead and do this quickly for these others. Appetizer, beverage, main course, and main course is special because it's got two words. Just keep that in mind, that Excel Name, that main underscore course, and that's important when we go ahead and do the Data Validation in a minute. Finally, let's name this desert. Now, we can go up here to cell H4. Again, I showed a lot of this in that previous screencasts. But I go in here to Data Validation, and I'm going to choose list, and the source is just equal to categories. Remember, categories is the vector, or the named range, appetizer, beverage, main course, and dessert. When I press enter, we now have this drop-down lists where the user can select from each of those items. Now, what we want is depending upon the category that they choose in cell H4, we want to sort by these different categories. I want the items available in H5, in a drop-down list to only be in the beverage column here, wine, beer, cola, and milk. To do that, again, as I showed you this in a previous screencasts, we can go ahead and create a list here. This list we can use the indirect function, and we have to use substitute in this addresses our issue with named ranges that have two words. Wherever we have a range and I'm going to do substitute H4 because whatever they put here is also a named range. Wherever we might have a space in that named range, we're going to replace it with an underscore, and I can go ahead and click okay. Now, the second drop-down list here in H5 only depends upon the range that is put in H4 and that's wine, beer, cola, and milk. That's how we can do this. As I showed you in that previous screencasts. Now the main problem with this, and I address this in some follow-up screencasts, you can actually use conditional formatting to identify that there's an error. But I wanted to show you how we can reset this conditional drop-down lists. Because the main problem is now if the user goes back and chooses something like main course, the item does not automatically clear or change, and the user has to go back in here and select that. But again, if we change back to something like a beverage, casserole is not a beverage. I'm going to show you in this screencast how we can reset whenever the user changes that Root menu here, that Root drop-down, H4, then this should automatically reset, and we're going to display a little message there. To do this, we're going to implement VBA. VBA is Visual Basic for applications. Go up here to the Developer tab. If you do not have the Developer tab, then you can go over here to File, Options, and customize ribbon. Then you can just make sure that developer is checked here. When you do that, you should now have the Developer tab. Now we can click on the Developer tab. Go over here to Visual Basic, and this brings up the Visual Basic Editor. We're not going to go too much into VBA, but it's a programming language that's inherent in Excel. You have to be using a Windows version of Excel. You cannot be using a Mac version. It might appear that VBA is there, but it really doesn't work that well. Now what I'm going to do is, I'm going to go into our sheet over here, make sure that you double-clicking on the sheet that has our information. This is Sheet 1. I can go ahead and double-click on that, and it brings up this form here. You may or may not have Option Explicit at the top, it's not a big deal for this example. But now what I'm going to do is I'm going to click from this little arrow. This is something that's known as an event handler. I can click on worksheet it automatically because it's the most popular it adds in this selection change. You see this selection change from the drop-down menu up here. I'm just going to put in a simple test line here, which is message box "Hello". What this means is that every time somebody changes the selection on the worksheet. Now remember this is an worksheet one over here. It's going to display a message box "Hello". Let's go back over here. I just click into a different cell and it displays what is known as a message box. I can click okay, I change the selection, it does the same thing. This is not tremendously useful by itself. But we can change the option up here instead of a selection dot change, I can select one of a bunch of different options over here. Again, these are known as event handlers. Let's go ahead and select Change. This means that whenever something is changed on the worksheet, this is what's going to happen, whatever is inside of this private Sub. I'm just going to move this up here, and I'm just going to delete this because we're no longer going to use that. I'm going to go ahead and delete a couple lines here is to clean it up a little bit. What this means now is that whenever we change anything on the worksheet, not one or changing the selection, but whenever we're changing something on the worksheet, we're going to display that message box. You see as I change the selection, nothing's happening. But now if I change cell H4, it displays that message. Now we don't want to display this message. What we want to do is we want to change the contents of cell H5 now, to display something like please choose item. Let's go ahead and instead of our message box, we can delete this. I'm going to put an ''if'' statement. This is a conditional statement. If and I'm going to write in Target. Address. This means that the target address that we're changing. If that happens to be, and now in quotation marks, I'm going to put the cell that we want to detect, and that's cell H4. If cell h four and you got to make sure you put those dollar signs for the absolute reference. If the target address is that, then and I like to put in an end if and if you guys are interested more in VBA, I've got a whole specialization on it. You'll learn all about these types of if-then statements and different programming structures and VBA. But if the target happens to be H4 that we change, then I want the cell below it. The way that we referred to the cell below it is by using Range. This is cell H5. We want H5, and you can either include or not include those dollar signs. We want that to equal something like, I'll just say double dash. Please choose item. Now let's go back. You see that if I change things on the worksheet, even if I change a cell here, that cell is not the target cell that we specified of H4. But if and when we change cell H4 to beverage than it says please choose item. I can make this a little bit wider. Then we can choose that. I can change that secondary cell. But whenever we make a change to cell H4, then it's going to ask us to choose the item. That's how we can reset conditional drop-down lists. Now, there's one thing that's really important with this. If you are creating VBA code, which we did, you have to make sure that you're saving this file as a Macro Enabled Workbook. The starter file that I gave you was an XLSX file. If we've added VBA code, you have to go down here and we get the save As. Now, we're going to make sure to select Macro Enabled Workbook, that's the second option here. Then we can go ahead and save that. That's how you can reset conditional drop-down lists. It's important again that this is a Macro Enabled Workbook. The next time somebody opens this workbook up, it's going to ask if they want to enable the macros, be sure to have them do that. Hopefully, you learned a little bit more about how you can reset conditional drop-down lists in this screencasts.
