Creating custom forms in Excel

Being the excellent student that you are, a teacher has requested that you develop a simple spreadsheet to record student results for her class.

Open a new spreadsheet and add the following column headings in the third row.

• Firstname
• Lastname
• Title
• D.O.B.
• Town/City
• PostCode
• Test Mark (30)
• Assign Mark (40)
• Total (as a %) (calculated using Test and Assign mark)
  
• Final Grade (F/P/C/D) (calculated based on Total)
  

You might want to add a few students to check that your formulas work correctly. In class we'll discuss a few ways that we can help our teacher to enter the information (rather than entering it directly on the spreadsheet).

But for now see if you can figure out how to create a UserForm from the VBA editor. Try out all the different options and create a form that will be able to be used to add information to our Student Records. Be creative! You can change the colours of the form, you can make the background of your form an image, or even add images to buttons... the sky is the limit! The only caution is to be very careful about how you name your fields! If you create a text box to store the first name of a student, you should call it txtFirstName... this will become very important later on!

For those who get ahead, see if you can add some VBA code to your OK button that will add the info from your form to the spreadsheet. Double click on your forms OK button to create a macro for the button. You'll then need to use the Cells VBA function, which allows you to set the value of a specific cell. For example:

• Cells(2, 1) = txtFirstName

Try it out!