Okay, this screencast is just going to introduce you to the basic concepts in the ultimate organizer project. Now, what you're trying to do is to create a pretty generic user form that will organize data on the spreadsheet. And in the next screencast I'm going to actually show you the project that I've created. But this is just an example where you can have a category of perhaps names. So, these are going to be people. And then you can add different elements of information regarding those people. So for example, their city, maybe their address, their phone number, their email, maybe the years that they worked in your company, and so on. It doesn't have to be people, you could have all sorts of different things. And the ultimate organizer can organize all sorts of different things, so you could have different items here, part numbers, what they're made of, how much they weigh, how much they cost, and all this stuff. So, it just a good way to organize data and the learners in this course come from a really big variety of backgrounds, so I wanted to really make this quite diverse and adaptable to all the different facets that you guys are working in or studying. So, I'm going to be going through an example where I got these data here. What this thing does is you have categories in the first row. So, I have name, city, phone number, email, years. So, those are known as categories. I'm going to assume for this project that the leftmost column in my example, name, is always going to be there. So, you can never delete name, but you can add categories. So I can add a category after column E, I could delete categories. Let's say, I no longer needed to know the phone number, your ultimate organizer project should be able to delete categories, so those are categories. We also have records. So, these are the rows of the spreadsheet and in my example, these are people but you could have places, items, all sorts of different things in here. And your organizer should be able to delete record, so you could delete an entire row. You should be able to add a row. Adding a row would put it at the very end of the last item that already exists. And you should be able to modify information on this spreadsheet. So, your general user form should look like this, it's going to have five different functions plus a quit button. So, it's going to have the ability to add a category, that column to the spreadsheet, delete a category, so delete an entire category, add a record, so that's a row, and delete an entire row of data. Whenever you delete anything you should have a confirmation, something like, are you sure you want to delete this category? And then finally, it's going to have a search and replace tool. The search and replace tool is going to allow somebody to quickly search through to get the information that they want. And then of course, you got a quit button. So, let me first talk about this add category. It's going to be pretty simple, so this itself is another userform, so you're going to have the main form, so this would be called maybe something like the main form, and then you're going to have a add category name. So, the user can just put in a new category name and that's going to add a column to the spreadsheet when they press add category. And you might actually want to have a cancel button here in case they wanted to change their mind. The next thing is a delete category. So again, that's just going to look something like this, but it's going to populate this combo box here with all of the categories other than the first column. So, the second column in my example is address. So, address and everything to the right is going to be populated in this combo box. So, the user can just select this from a drop down menu and delete or they can cancel. The next function will be to add a record. A record again is a row, so when they click this, it should bring up a box. Now, this is going to be probably the most difficult thing to do because this itself is a new user form, but you're going to have to detect how many columns on the spreadsheet already have information in them. And you're going to count at the beginning whenever you bring this thing up, you're going to first have to somehow count the number of columns and you're going to have to then make available the boxes here where one could enter a new record. But if there's only three categories on the spreadsheet, like in this example there's only name, phone, and address, it should only bring up three of those boxes. These other greyed out dotted lines, these are hidden labels and input boxes. So, you're going to want to unhide these different labels and text boxes according to the number of categories you have on your spreadsheet. And I've got some screencasts on how to do this. But basically, if you create a new record, the user can enter the items. You should enable this project to have up to 12 different categories on the spreadsheet. And then the user could add record, and again you might actually have a cancel button here too. So next, we're going to create a delete record user form, and it's going to look like very similar to the delete category where it's going to populate this combo box with all of the rows. And this is organized by the first column, so in mine I have names. So, the user will be able to drop down from a combobox here and select, and then click delete and it's also got a cancel option. And finally, the search and replace tool is also going to be quite difficult, and it's going to bring up another user form where you can search for the different records by the first column. So, in my example it's names. And so, you should always be searching through according to the first column, and then you can select which of the other categories other than column A, you would like to search. If you wanted to display Max's address, you would do that and then you click search and what it would do, is it would look through the information and display that. It's got an option here to replace, so if you click replace, it's going to bring up a box that says, okay, enter the new information and then you can confirm that, you can cancel. There's one more thing, if there isn't a current address for example for Max, for example if it's blank, then it's going to detect that it's blank or empty, and it's going to ask the user, would you like to enter something? There currently isn't an address, but would you like to enter it? And then the user can either press yes or no. So, that's the basics of the project. I would really encourage you to do a modular approach, and the way I did this was I just created essentially five different subroutines for all of these five buttons other than the quit button. And each of these I worked independently and made sure they worked independently, and then I combined those all into my final user form. And obviously, you're going to have to make a few changes because of the references and so on that you're making. But once you create these five independent subroutines, it's going to be a lot easier for you to implement and incorporate them into the overall user form project. So, that's sort of just kind of an outline of what you're going to be doing in the next screencast. I will show you this, I'll demonstrate my actual project in Excel.
