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
• State
  
• 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 for Total and Final Grade work correctly.

Once you have your formulas working, 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! Note the special prefix... other standard prefixes are:

• cbo for Combo boxes (ie. cboState)
• lbl for Labels (ie. lblFirstName)
• frm for your forms (ie. frmStudentDetails)
• cmd or btn for Command Buttons (ie cmdOK or btnOK)

For those who get ahead, there's two extra tasks:

First, create a macro that will simply display your form... the only statement your macro will need is:

• frmStudentDetails.Show

Then add a button to your spreadsheet that will call your new macro and display your form. Test it out to make sure it works!

Second, 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. Try adding the following example code to your OK button:

• Cells(2, 1) = txtFirstName

Try it out!