All right, this is the last screencast related to the Grade Manager project, as far as the major functionalities of the Grade Manager project. In this screencast, I'm going to show you how to make a search and update tool. Basically, the user can search through the roster for a student, for a particular grade item to see what they got on that assignment. And then they can also, if they want, they can replace that grade using kind of a nice user form. So let's go ahead and start. I'm going to show you first what we're going to be making in the screencast. This is just a simple spreadsheet representing a basic grade book. Where we might have the students here, we might have the section number in column B. We might have assignments across the top, so homework 1, homework 2, and so on. And then we've got our grades. What we're going to be creating in this screencast is the Search tool. So we click on the Search tool to open it up. And it's already populated these two combo boxes. The first one is populated with all the students that are in column A, and the second combo box here, we've populated with the categories. So going across the top, so we have A, B, C. Now we can use this tool to search. So if I wanted to search to see what Jennifer got on assignment B, we could click Search and it says, Grade on this assignment is a 9. And we can just verify that Jennifer assignment B is a 9. And you can imagine this spreadsheet could be really big, we could have hundreds of students and dozens of different assignments, so this is quite useful. If we wanted to replace the grade, we could go in here and I could select Shelley assignment C. I could do Replace. What is the new grade? Maybe 8. I click OK, and it says, Are you sure you want to replace the grade? Then, I click, Yes. And then says, Grade replaced to an 8. And we can make sure that Shelley on assignment C got an 8. All right, and then we can then quit. So that's what we're going to be creating in the screencast. I've created this user form, and you guys should be comfortable with creating these, you can just create combo boxes. So I have added in a couple of combo boxes, some labels, and three buttons. The Quit button is the easiest to code. So if I go ahead and double-click on the Quit button, this is where we put the code for the Quit button. Whenever you have combo boxes on a user form, you should use Unload. So we can just write in Unload UserForm1. Let's go ahead and look into the Search button. But before we do that, we need to populate the combo boxes, we're going to do that from a central module. So just in a central module, we're going to populate the combo box. I'm just naming this Sub Search. So the first thing that we're going to have to do is populate the combo boxes. But in order for us to know how many items there are, we're first going to have to count the number of rows. So we're going to use CountA of column A to determine how many items there are that we need to populate into the first combo box. And then we need to count the number of items in row 1, in order to know how many items we need to populate into ComboBox2. So I've Dimed, nr, nc, and i, i is going to be an index of iteration. We're going to count the number of rows using the CountA function. Count the number of columns and c using the CountA function of Row 1. We then select Range A2, so we're going to select cell A2, and we're going to iterate one at a time, and populate the ComboBox1 with those names. So we put a For loop here for i = 1 To the number of rows, UserForm1.ComboBox1 and so on. We're going to add each of those items in the first column into ComboBox1. We just add a default value to ComboBox1, which is going to be the first item in the column, and that's Range A2. We do a similar thing with the column headings. We're going to select Range C1, and we're going to go for i = 1 To nc. Then we're ActiveCell offsetting by the columns, so we're taking in all of the columns here, starting in C1, and we're adding those columns to ComboBox2. And then we add in a default value, and that's just the first item in the column headings. And finally, we just need to show the UserForm. So this is all set up to do, the Search functionality of this UserForm. Let's go ahead and run this. So I'm just going to press F8 a couple of times. We count the number of rows that's 11, we have 11 students. We count the number of grade items, that's the number of columns which is 3. We then select A2, so when we run that, we select A2 over here. And now we're going to iterate for all of the rows, there are 11 of them. And we've add those to the ComboBox1. We're going to add a default value, which is going to be Range A2, that's Charlie. And then, we're going to select C1, we're going to do the same thing. We're going to iterate through the three items that are in the spreadsheet. And finally, we're going to show the UserForm, and see how we've populated the combo boxes with those values. So now, we need to code what's behind the Search button. I can go to our UserForm here. I can double-click on the Search button, and this is the code that goes behind when we click the Search button. Now, the Search button is probably a whole lot easier than what you are imagining. The first thing we're going to do is just select Range C2. So we'll select Range C2 here, and then, we're just going to do a simple one line here. We're going to do a MsgBox, Grade on this assignment is, and we're going to use the ListIndex of ComboBox1, remember that starts at zero. We're going to use that as the row number and we're going to use ComboBox2.ListIndex as the column number. Those two indices combined with the ActiveCell Offset because we're starting with cell C2, here. C2 will easily give us then, we can just output the result using this single line of code. So let's go ahead and see if this is working. We start from our original module. I'm going to press F5, and now I'm just going to do a test here. I'll do Dana, assignment B and I do Search, and it just gives us 8 which is correct. So it looks like this Search tool is working, and also the Quit button is working. Now, I need to code the Replace button. So we're going to go ahead and click Replace, and this is the code that goes behind the Replace button. The first thing we're going to need to do is Dim a NewGrade As Double. That's going to be a NewGrade item that the user can input. NewGrade is going to be defined in InputBox. What is the new grade? We are then going to select C2 again. So we're going to select C2 that's kind of, going to be our starting point. And then we're going to have a confirmation here, MsgBox, now this is a message box with a return value. So I need to Dim, answer as an integer. It's going to have buttons on there. Are you sure you want to replace this grade? And there's a vbYesNo, which indicates that the message box has a button for Yes, and a button for No. The return value for a MsgBox, for the Yes button is a 6, so we can say if Ans= 6 Then ActiveCell.Offset. We're going to kind of do the same thing that we did down here in the Search button tool. We're going to Offset by the ListIndex of ComboBox1, that's the rows, that's the student number, and the assessment, or the assignment which is the ListIndex of ComboBox2. And we're setting that equal to the new grade. And finally, we're just going to confirm that in the MsgBox Grade replaced to a, and we're going to say NewGrade. And that's it, that's all the code we need for the replace button. So let's go back to module one, I'm going to press F5 to run this. Let's just select maybe Fernando, category A. We're going to replace and let's just put in something like a 3. And when we click OK, we should see that this 7 here should be replaced with a 3, after we do the confirmation box here. And that's in fact what we see, we've replaced that 2 or 3. So this is how you can make a simple Search tool, and a Replace tool for your Grade Manager project. Good luck, and this is the last screencast related to the Grade Manager project, I wish you all the best of luck. And let me know if you have any issues or questions. It's going to be quite frustrating, and you're going to have to spend quite a bit of time on this, but I think you'll get a lot out of this project. Thanks for watching.
