All right, the next step in the process, the Grade Manager project, is to add column headings to the individual spreadsheets. For example, for the homework assignments or homework one, homework two, or homework three, maybe lab one, lab two, quiz one or exam one, and so on. So, what we're going to be working through in this screencast is how we can initialize those files. Now, the first four boxes here of this flowchart are the initialization process. This will only happen once for each class. Once everything is initialized and set up, then the only things to be done are to sync the roster and then we're going to create backup files. Another thing that I don't have shown in here, but that you're required to do for the grade manager project is to make a search tool. And so, you can search through for different students and you can change their grades, update their grades. And we'll talk about that more later on. So, the results of the previous screencast was to make these section files. And I'll just open up one of these. And you're going to adapt this code it's going to be similar but not identical to what I'm working through in these different screencast. But here's just one example of one of the files we made. I've actually modified it a little bit to start writing in cell A2 rather than the top one. The reason for that is we want to be able to put in the first row, we want to be able to put things like homework one and homework two. We want labels for the different columns. So, we need to make sure that we're leaving that blank. There's a couple of ways to do this and I will leave that up to you to figure out how to do. So, what we're going to be creating in the screencast is this UserForm and this process for initializing the sheets that are in this directory. So, I'm just going to click on here. I'm going to show you what happens. You're going to come up with a UserForm, this is going to be UserForm, and the user can select, let's just say up to 15 different homework assignments or whatever. Here, I've got homework and exams, you might have others, you're going to have to put others in here, but let's just choose maybe eight homework assignments. I haven't read this up yet to work with exams, but you can figure out how to do that. So, the user selects the number of homework assignments and then we initialized. And when I initialize, it's going to say please select the directory that contains your files. So I'm going to go to the same directory that I've been going to this Section Files or the section rosters. I select Okay, and it's going through. You can see it kind of flashing and it's adding those assignment headings to the files and it tells us that, and I can click Okay. And then the UserForm quits. If I open up one of these files, you see that what it has done, is it's added homework one through homework eight. And if I had selected the other assignments which you have to enable, it would then start after that like Lab one, lab two, and so on, up to how many labs they selected. And whatever their problem statement says, they haven't really finalized that, but that's what you're going to have to do. So, let me show you how to do this and I've got this file, the product of this. I'm going to post this file on the course website, it's called Initialize Sheets. I've created this UserForm here. It's a pretty basic UserForm. I've just added in two Combo Boxes. So, the Combo Boxes are down here and I have named that, so this is named combo Box one. This is name Combo Box two. I've got a Initialize Button and a Quit Button. I've also added in some some labels here. The Quit Button, let's go ahead and put it in first. That's just going to be UserForm1.Hide or you could do unload UserForm1. Let's go back to the UserForm. Before we open the UserForm, we have to populate the Combo Boxes and I've got a lot of this information leading to this in part two of the course. So, what we're going to do is I'm creating this Initialize Sheets and I'm going to call, It's going to be called Populate Combo Boxes. It's going to be a sub routine and I'm going to put that subroutine down here. I've got to Combo Boxes. I have Combo Box one and Combo Box two. So, in my module here, we're going to populate those Combo Boxes. Again, see part two of the course for what I'm doing here. I've Dimmed i as an integer I'm iterating For i = 1 To 15. I'm adding the numbers one through 15 in each of those Combo Boxes. And then, the user can select a number between one and 15. So, I'm just iterating For i = 1 To 15. The Combo Box one of UserForm1, we're going to AddItem i, which is going to be one to 15. So, we're going to add one through 15. We're also going to do that for Combo Box two, so that populates the Combo Boxes each with one through 15 and then the Combo Box Text just puts a default value of one in the first item in the Combo Box. So, when we call or run this Initialize Sheets subroutine, it's going to call, populate Combo Boxes. What we want to happen after that is UserForm1.Show. We want to show UserForm1 and that will then populate the Combo Boxes, it will open up this UserForm and it will be ready to go. We've already coded the Quit Button, but now what we need to do is code the initialize spreadsheet. Now, remember that when a user selects an item from a dropdown list like a Combo Box, it provides something known as a List Index, where the first item is always a zero. Here, we're going to have one through 15, so we're going to have to add one to each of those items. For example, they choose eight from the drop down menu that's really List Index seven, so we're going to have to add one to get to the actual homework assignment. So, let's go into initialize spreadsheets. This is where we put the code for when the user clicks that initialize button. First thing we do, is Dim a couple of things, we're going to select a file path like we have done in the couple of the previous screencast, define folder as a string. We're going to be working with different workbooks, so I'm going to just define wb as Workbook, FileName as string, the active workbook as a workbook. I could also define twb, which is this workbook. That's probably a good idea, but I'm actually not going to be doing that this time. i is going to be dimmed as an integer because we're going to be iterating. Now, what I'm going to do is display a message box "please select a directory that contains your files." So, this is the directory that contains the roster files, similar to what I've done in previous screencast. Also, we're going to use this Folder Picker to allow the user to select the folder. Now, this next part, we're going to set this file name that we've dimmed up here as a string equal to, There's a directory function where you can open up the file path and choose the first Excel file. And then, when we follow that up with this 'Do Loop', see part two of the course with working with workbooks for kind of what we're doing here, but we're going to open up the workbook to the first workbook, the first Excel file in the in the folder. We're going to set that to the active workbook, we are then going to use the List Index and UserForm1. So, whatever they selected, if they selected eight, then we're going to iterate For i = 1 To-. Now, remember the List Index, if they selected eight would be seven, so I'm adding one to that. So, we're going to iterate from one to eight, if they selected eight. Now, what we're going to do is starting in range B1. So, let me go ahead and open up one of these files starting in range B1, we want to put homework one, then we we want to offset by a call B2 or homework two, homework three and so on. So, that's what this does. It takes that active workbook on a first sheet, we're assuming everything's just on the first sheet, the roster, we're going to start in range B1 and we're going to offset. So, we're going to offset by zero rows, we want to be in the first row and offset by i minus one columns. So, the first one when i = 1, we don't want to offset at all. We want it to be in the zero, zero offset, which is cell B1. And then into that cell we're going to put homework, space & i. So it'll put homework one and then it'll offset by one column. When i = 2, it'll say homework two and so on. So, what it will do, is starting B1, it'll then go one at a time and put homework one, homework two, homework three, and so on. After we put those labels for the various columns and your project, yours going to be a lot more involved because you're going to have not just homework assignments, you're going to have exams in labs and possibly others. Once we've done that, then we're going to close the workbook. It's important to make this equal true. We want to save the changes and then this FileName = Dir just bumps to the next FileName in our folder. So, we're going to loop until we're out of the FileName. So, basically what this part does, these lines of code, they just go through all the Excel files in our folder that we've defined and adds these labels to those columns. And then, the last thing I've done is put a message box the assignment headings have been added to the files that we're going to hide UserForm1. Let's go ahead and step through this. I'm going to put a couple of breakpoints points in here. So, I'll just put one there and I'm going to just press F8. Now, we don't start from here, we have to start from a module. We're going to start from here because we have to first populate the Combo Boxes. So, I'm going to press F8, we call this. Then we go through here, I'm going to just run to cursor. So, we don't have to go through all those high values and then we populate the Combo Box. We go back to our Sub here, and then we're going to show UserForm1. Now, we populate the Combo Boxes and I'm just going to do 10. We're going to add 10 homework assignments and again I have not coded for this second one, that's more for you to do and you're going to have more assignments here. So, then I can press the Initialize spreadsheets and we go into this subroutine and I'm going to press F8. We have the message box, "please select the folder" and then we go into this File Picker and I'm going to do Section Files, I click OK. And then, we go through the 'Do Loop' and you notice here to open up that first file. Now, if I continue we're going to set the active workbook and now I'm going to go through now our List Index. If I run that line and I have over is nine, but I selected 10. That's because the List Index always give you one less. It always starts with zeros, we the add one. So, we're going to iterate For i = 1 To 10. Now, the active sheet into range one I press F8. We're going to put homework &1, press F8, I go back to my sheet here and we put homework one. I just put them side by side here so we can see what's going on. Press F8 again, we now eyes two, so we're going to put homework two, and we keep going, homework four, five, six, seven, eight, nine and 10. And then we're done. We're going to close, save changes and then we're going to bumped to the next file. So, it'll bump up to the next file, it'll do file two, three, four and file until we've gone through all files in this folder. So, I'm just going to go here and debug, run the cursor. So, it's going to go through those and then we can message box assignment headings have been added and then we're going to hide the UserForm. And then we end the original Sub. So, that's how you can go through and initialize the various sheets, and again, yours is going to be a little bit more complicated than this.
