Now that we've done most of the functionalities of the Grade Manager project, we're going to do a couple of miscellaneous things. The first thing we're going to do is, we're going to create a functionality that will add a column. So, maybe the user at the beginning of the semester only indicated or chose or set up 10 homework assignments. But now, we want to add a 11th homework assignment. In this screencast, we're going to add a column and what that does is, it has to iterate through all of the workbooks, all the TA section grade sheets, and add that item to all those files. And then in subsequent screencast, we will talk about deleting a column. Actually we might not even talk about deleting a column, because in part two of the course for homework assignment number four, week four, you had to do something very similar. So, it's quite easy to delete a column. But then, we're going to talk about a search and update tool. If you want to search for a user and change their grade, you can do this with this search update tool. So, your Grade Manager project should have all of these things. So, the grade files, the section grade files for each of the sections of the course are going to be in a folder. And for this screencast, I have them in a folder called Add Column Files, and this is available on the course website. These are very similar to the ones I've been working with in previous screencast. The only difference is that I just kind of changed the format here so we have A1, A2, B1, B2. The A's here would represent something like homework assignment, Homework 1, Homework 2. The B's might be something like exams, Exam 1, Exam 2. And there's going to be a space in between there for what you're doing. But essentially what we want to do is we want to either add an A, so we want to add A3 automatically by inserting here in between C and D. If I right click here, I can do Insert and that will insert a column. So, we're going to sort of do that but then we're going to name that first row there. Also, it might, if we put in a second B category, then we would go and put B3 in this one, and it would do that to all of the files in this original directory. So, then the next time the grade book was synchronized, we could bring in all of those new grades. Let me show you what we're going to be making in this screencast. So, let's go ahead and run this. Let me show you what we're going to create. If we click Add New Column, it's going to ask us, "Which type of assignment would you like to add?" And we can select, in this example A or B, where A and B would represent something like homework, or exams, or labs, or a different category of grades. So, I'm just going to select A, I'm going to click Add Item. "Please select the directory/folder that contains your files." So, I'm going to click where my files are. Now, it's going to go through and it's making the changes to each of those. A column for A3, and it's telling us that it's added a third assignment. It's detecting that we already have two. So, we didn't know that beforehand. It's actually automatically detecting that. That column has been added to the files. And I can go ahead and open up one of these and if we look at it, it's got that A3 that's been added between the A's and B's. Let me go ahead and run this again. Let's add a B assignment, another B item on there. So, it's going to ask us again. Now, in your Grade Manager file, you probably don't want to keep doing this. You probably want to have on a hidden sheet. You want to have the file path, so that the user doesn't have to do that every time. It's already stored in a hidden sheet. So, this gives us a message box that says, "A column for B3 has been added to the files!" And I can go ahead and open up one of those files. Let me open up file four and you see that it's added a column B4. Let's do this one more time. Let's just add in another A just to make sure that it's working. We select everything. It's going to go through. A column for A4 has been added to the files and we can open up a file here and you see that it's added A4. And it's done this for all of the workbooks in that folders. So, we're making consistent changes to all of the grade book files. So, let me show you how to make this. I started a lot of this file. I, basically started from the my InitializeSheets product. So, I'm going to start with InitializeSheets, because a lot of what I'm going to do is almost identical to the InitializeSheets subroutine that we did. I've created this new UserForm. It's just pretty basic. It's just got a label and a combo box. This is called ComboBox1. We have an Add Item and a Cancel button. The Cancel button, I just unload it. Now, a lot of times in a user form, I've been telling you in different screencast, that it's okay to hide a user form. But when you have a combo box on there, whenever you quit or shut down a user form, you always want to make sure to unload the user form. Otherwise, it'll hide it. If you bring it up again, you'll have duplicate values for the items that are in the combo box. So, here's the code from the InitializeSheets subroutine that we made in a previous screencast. And I'm just going to use this kind as a base. Let me just, while I, because I just mentioned this, instead of UserForm1.Hide, whenever you have combo boxes on user form, you should do Unload that Userform. All right, so let's start making some modifications. The first thing we're going to do is, we're going to have this category name that's the UserForm1 ComboBox.Text. So, in the user form, that's what this is when they drop down the A or the B, in this example, is what the category name is going to be. We have to dim that as a string. So, I've dimmed CategoryName as string. We're going to do the same thing, where we tell the user to select the directory. We're going to use that with End With, that dialog box here that folder picker, to allow the user to select the path. Now, again in your File Manager project, you've probably going to want to replace a lot of this with just a static path that's maybe hidden in a sheet like we did in a previous screencast. So, you can just say path equals some, you know, cell A1 of that hidden sheet. Now, we're going to make a really big change to this part of the "Do" loop in here. Instead of simply opening up a workbook and then adding labels to the top, we're going to have to do a lot more because we're going to have to detect how many labels there are, how many different categories there are. And we're going to have to sort of be smart about how we do things here, because we're going to have to add things, and insert them in different places, not just at the end, but sort of in order that they already appear. So, I'm going to go ahead and count the number of items that we have in row one. That's the total number of assignments and I have to dim that, I've dimmed that as an integer. And obviously, this is done after I opened up the first workbook. So, we opened up the workbook. We set that as the active workbook. We count the number of items in row one. If this is an example file, and let me just go ahead and delete these to restore it back to what it was. But if this is my sample file, I want to count the total number of items in row one. The next step is just going to be selecting range B1, regardless of which workbook we are in. So, I'm going to select B1 of that workbook. I'm then going to initiate a pretty big "For" loop. And this is going to iterate through all of the items. We just counted the number of items. In this case, there are four, but we're going to have to iterate through all four of them.
