Statement of Input, Processing & Output requirement
The input would be:
Personal Details:
- First name
- Surname
- Gender
- Date of Birth
- Form
- Roll number
Student marks:
Maximum /minimum mark
The Processing would be:
- Calculating the grades through a Vlookup to the grade boundaries table.
- Sorting, grouping and summarising data with a pivot table.
- Calculate the average of total marks
- Using Vlookup work out a grade for the average.
The output would be:
- Pivot Chart/Graph showing comparisons between gender, form-by-form and overall year group achievement.
- Give students their grades using report card.
- Table displaying student grades.
- Creating report for using student with performance.
- School letter, which would be sent out to parent using conditional formatting to highlighting grade attained.
Data Set
Because of Data Production Act, I cannot be able to use the real detail of Ms Gammon’s students. For that reason, I will create my own data set of example student details, which I will work with.
I will also use grade boundaries and colour coded:
Other data that I will use is to convert numerical values to percentages. Each test they will take will have a minimum and maximum marks.
E.g. 3 three test
Design Overview: Global Task Diagram
Detailed Design: Worksheet Storyboards
Detailed Design: Storyboard Commentary
The new system will be based on one workbook. It will consist of 10 main sheets, which will be linked together. The summary report sheet will be a template and will be filled in with cell references to the precise information on the main data store sheet. The user (student) will only need to select students name in the list box and whom the report will be created for.
Sheet 1- Front-end menu
The sheet will have a number of macros to navigate through the system. It will have 8 options. These are to add new student, enter marks, create report summary, edit or delete student, chart summaries, create parent report, edit grade boundary and exit.
The worksheet would not be opened manually; in its place the macros would be programmed to open the sheet automatically.
Sheet 2-Enter Marks
This system will contain list boxes, Vlookups and input boxes which will enable the user to choose each student personal details, enter assignment marks and maximum marks. The assignment and maximum marks will have an input box that will enable the user to enter the individual marks, which will be locate to a certain student. Student marks will then be sent to archive.
Sheet 3-Marking Archive
The marking archive sheet will stores complete record of all marks to archive. The user will not have access to this sheet. However it will be summarised by pivot table B so that user can prepare a report for parent evening or send letters home. Also it will be summarise by pivot table A for charts that will be group in three ways. There will be no macro on this sheet
Sheet 4-Pivot Table A
This will contain a pivot table, which is linked to the “archive” in the marking archive sheet and for the pivot chart. The user will never have access to this sheet. When the user select the chart that she requires the pivot table on the sheet will change to represent that choice. I will record macros sorting the pivot into three ways. These are by gender, Form -by-form and Year group attained (all students). The chart will be created on sheet 5 for whichever setting is on the pivot table.
Sheet 5-Pivot chart
This chart will be connected to the pivot table A. The macro buttons will allow the user to choose student marks chart to view and print it will also have a macro button to go to the front-end menu.
Sheet 6- Report summary
This will have a list box from which the user will select a name and it will display the student roll number in linked cell and from this their average marks will show three sets of lookups. These are
- It will looked up and (calculated) from pivot B
- Student names will be looked up from sheet 10 and
- Comment and grade looked up, using percentages, on sheet 8
Sheet 7-Pivot Table B
The second pivot table will be used to summarise the outcome of student performance. This sheet will therefore help the user to write letter home, individual report for parent evening etc. This will linked to the archive in the marking archive sheet.
Sheet 8- Grade Boundary
This will hold grade boundary table named gradeB. This will be used as a Vlookup to allocate grade to students based on their average marks on sheet 6 and sheet 9. Colour coding will be put as conditional formatting onto cells in sheet 6 and 9.
Sheet 9-Parent Report
This sheet will contain a letter template which will be sent to parent at the end of each term to tell the parent which grade their child has received, or if a student performance is very badly. The report will work by using Vlookup formulae, which will be show in three sets of Lookups. These are: looked up and calculated form pivot table B, student names looked up from sheet 10 and grade looked up, using average mark, on sheet 8
Sheet 10-Student List
The student list will hold all of the students’ details only. The user will be able to add new student using a user form and also be able to edit or delete student details if the student move from her class using a data form. The macro to add new students will open up a user form to allow easy input of data. The data will be placed in the table and the table will then be sorted in ascending order of the number. This is important for all the list boxes on marks number, report summary and parent report to work.
User Form 1
A series of prompts will guide user through exit procedure. The user form will have a question saying: are you sure you want to leave? There will be two user form buttons, one saying yes and the other no. The yes button will exit the system and the no button will take the user back to the front-end menu.
User Form 2
The user Form will appear when the Add New Student macro is triggered. The form will have spaces to enter the student personal details. The gender and form will be combo boxes. The combo boxes on the user form will make it easier for the user to enter information. There will be a macro button to take the user back to the front-end menu.
Combo Street
This sheet contains named ranges for containing data about form and gender. This sheet is hidden, as the user has no use to see the data contained on this sheet. It is hidden to prevent access to the sheet and this reduces the chances of the data being by mistake changed.
Form Range
The user has six form, 11C, 11A, 11S, 11R, 11E and 11D. This is stored a range named Form. This range is used as the fill range for the combo box for form when entering new student details.
Gender Range
The range contains M and F. The range is named Gender. It is used as a fill range for the combo box when entering new student details, which will enable the user to select a new student gender.
Data entry and Validation
Ms Gammon will input the assignment marks and mock exams in to the system. The information may be edited rarely but it may need to be updated after a while, this is why validation checks would be necessary to limit the amount of errors occurring and to make sure the data being inputted meets certain specifications. I will validate all cells, into which marks are inputted as these needs to be as correct as possible. Format checks will be used on cell so that the user inputs the information in the correct format, if the user input the wrong format the spreadsheet would tell the user to correct the mistake. Range check will make sure that the user input data in the correct range; the maximum mark for each assignment will range from 0-100.
Macro Designs
Macros from Sheet I- Front end Menu
‘mcrEnterMarks’
Go to sheet 2
Select A1
‘mcrEdit and Delete Student Detail’
Go to student list
Open data from
Re-sort table
Return to main menu
Select A1
‘mcrAdd New Student’
Select student list
Insert row at row two
Load user form 2
Select columns A: F
Sort roll number ascending,
Return to main menu
Select A1
‘mcrExit’
Load user form 1
‘mcrCharts’
Go pivot A
Refresh
Go sheet 5
Select A1
‘mcrGenderChart’
Go sheet 4
Re-arrange fields
Return sheet5
Edit title
Edit X-axis
‘mcrFormchart’
Go sheet 4
Re-arrange fields
Return sheet5
Edit title
Edit X-axis
‘mcrAllStudent’
Go sheet 4
Re-arrange fields
Return sheet5
Edit title
Edit X-axis
‘mcrchartprint’
Go to sheet 5
Click file, print
Print one copy
Select A1
‘McrIboxAssignment’
Go sheet 2
Select I18
InputBox
Select A1
‘mcrIboxMaximum’
Go sheet 2
Select J18
Inputbox
Enter maximum
Select A1
‘mcrOk’
Go Marking Archive sheet
Select range A3:A13
Insert row at row two
Select enter mark sheet
Select range B18:K18
Select copy
Go to marking archive
Paste
Select enter mark sheet
Select A1
‘mcrViewReportSummary’
Go pivot table B
Refresh data
Select cell A1
‘mcrViewparentReport’
Go pivot table B
Refresh data
Select cell A1
‘mcrReturn’
Hide user form 1
Return to main menu
Select A1
‘mcrCencel’
Hide userform1
Reture to main menu
Select A1
‘mcrAuto_Open’
Click tool, options
Uncheck the gridlines, row and column headings, sheet tabs, scroll bars
Uncheck the formula bar and status bar and click ok
Select A1
‘mcrAuto_Close’
Click tool, options
Uncheck the gridlines, row and column headings, sheet tabs, scroll bars
Uncheck the formula bar and status bar and click ok
Select A1
Test Strategy and Testing Plan
Test Strategy
Before I test that my new system works properly and is achievable I would need to test it in various ways. This system therefore needs to be testing in different ways and various situations so that the user will not get any unexpected problems. The purpose of testing the system is to provoke failure of the system so that it can be improved.
I will use functional testing to test each command button and menu item under different ways, to check on any mistakes and to improve any failures.
I will test the new system by inputting typical, out of boundary, at boundary erroneous and within the boundary, to enable me to see if the system accepts the correct data and rejects the incorrect data.
I will also test each formula to see if it works, I would need to make sure that each formula is linked to appropriate cell, this will help me of check if I have done something wrong.
Another way in which I will test new system, is to test each macro with different ways this will therefore help me to check every effect. I can also monitor that the macro doesn’t work just for one value and then not another.
The last test I will do is to let my user use the new system, by doing this it will help to see if I have achieved the user requirement and also whether the objective of the system has been met.
Testing Plan
Opening the system
Date of Birth
The column of cells will be formatted in the data format, dd/mm/yy
Gender
This will be validated in a list to accept either M or F
Data entry form
Front-End Menu
Chart Summaries
There will be several macro buttons on this sheet in order to automate the process of producing a chart.
User form 1: Exit and save
Enter Marks
This will accept numbers between 1 and 100 only
User form 2: Add new student
Parent Report
Grade boundary
Testing
I have followed my testing plan.
Opening the system
Date of Birth
The column of cells will be formatted in the data format, dd/mm/yy
Gender
This will be validated in a list to accept either M or F
Data entry form
Front-End Menu
Testing chart sheet
Test Jonas
Before adding a test mark
After adding a test mark to archive, the chart refresh.
Chart Summaries
There will be several macro buttons on this sheet in order to automate the process of producing a chart.
User form 1: Exit and save
Enter Marks
This will accept numbers between 1 and 100 only
User form 2: Add new student
Parent Report
Grade boundary
Before
After
Implementation
This is the main menu which the user will see, when she opens the system to take the marks. The main menu consists of Macro’s which will enable my end user to click and go to the specific sheet which she needs. I have also added a password protection to the workbook; therefore whenever the user enters the system he will have to type the password to access the system.
Macro Code
These are the macros I have used on the front-end menu.
McrAddNewStudent
Sub mcrAAddNewStudent()
'
' mcrAddNewStudent Macro
' Macro recorded 12/29/2006 by OFFICE
'Select student list sheet
Sheets("Student List").Select
Range("A2").Select
Selection.EntireRow.Insert
'Loads a userform asking if they are sure to save or cancel
Load UserForm2
UserForm2.Show
'Delete if all the row is not filled in:
Sheets("Student List").Select
If Range("A2") = "" Then
Rows("2:2").Select
Selection.Delete Shift:=x1Up
MsgBox "The new student has not been added to the system at this time. You failed to enter data in one of the required field!", vbOKOnly, "Student Entry Message:"
End If
If Range("B2") = "" Then
Rows("2:2").Select
Selection.Delete Shift:=x1Up
MsgBox "The new student has not been added to the system at this time. You failed to enter data in one of the required field!", vbOKOnly, "Student Entry Message:"
End If
If Range("C2") = "" Then
Rows("2:2").Select
Selection.Delete Shift:=x1Up
MsgBox "The new student has not been added to the system at this time. You failed to enter data in one of the required field!", vbOKOnly, "Student Entry Message:"
End If
If Range("D2") = "" Then
Rows("2:2").Select
Selection.Delete Shift:=x1Up
MsgBox "The new student has not been added to the system at this time. You failed to enter data in one of the required field!", vbOKOnly, "Student Entry Message:"
End If
If Range("E2") = "" Then
Rows("2:2").Select
Selection.Delete Shift:=x1Up
MsgBox "The new student has not been added to the system at this time. You failed to enter data in one of the required field!", vbOKOnly, "Student Entry Message:"
End If
If Range("F2") = "" Then
Rows("2:2").Select
Selection.Delete Shift:=x1Up
MsgBox "The new student has not been added to the system at this time. You failed to enter data in one of the required field!", vbOKOnly, "Student Entry Message:"
End If
'Re-sort table (so that listbox and Vlookups will work):
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sheets("Front End Menu").Select
End Sub
McrEnterMarks
Sub mcrentermarks()
'
' mcrentermarks Macro
' Macro recorded 08/01/2007 by 21bor2413j
'
' Sheets("Marks No").Select
Range("A1").Select
End Sub
mcrGradeBoundary
Sub mcrGradeBoundary()
'
' mcrGradeBoundary Macro
' Macro recorded 19/02/2007 by 21bor2413j
'
'Select grade boundary sheet
Sheets("Grade Boundary").Select
Range("A1:C9").Select
ActiveSheet.ShowDataForm
' Re-sort table in case user edit column A
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'Go to Main Menu
Sheets("Front End Menu").Select
End Sub
Mcredit/deletestudent
Sub mcreditdeletestudent()
'
' mcreditdeletestudent Macro
' Macro recorded 19/02/2007 by 21bor2413j
'
'Select Student list sheet
Sheets("Student List").Select
ActiveSheet.ShowDataForm
'Re-sort table
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
'Go to Main Menu
Sheets("Front End Menu").Select
End Sub
mcrParentReport
Sub mcrParentReport()
'
' mcrParentReport Macro
' Macro recorded 20/02/2007 by 21bor2413j
'
'Select Pivot Table B
Sheets("Pivot Table B").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
' Select Parent Report
Sheets("Parent report").Select
End Sub
McrcGoChart
Sub mcrGoChart()
'
' mcrGoChart Macro
' Macro recorded 29/01/2007 by 21bor2413j
'
'Select chart sheet
Sheets("Pivot A").Select
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Sheets("Chart1").Select
End Sub
McrExit
Sub mcrexit()
'
' mcrexit Macro
' Macro recorded 12/29/2006 by OFFICE
'Loads a userform asking the user if they are sure they want to exit
Load UserForm1
UserForm1.Show
End Sub
I did not come across any problem when creating and designing this sheet.
Input student Marks
This sheet will enable the user to select the student’s name and enter the marks which the students obtained in the test. This system contain listbox, Vlookups and input box which enable the user to choose each student personal details, enter assignment marks and maximum marks
These are the input box, which will enable the user to enter assignment and the marks obtained by the Pupils. There will be a validation on the boxes so that when the user enters wrong data the systems rejects the information.
The list box is where the user selects the student details. It contains student roll number, surname, first name, gender and form.
The properties that attach to the list box
The properties from the list box I change the ColumnCount to 4, to give the columns, for the roll number, surname, first name, gender and form. B22 is the linkedCell. The linked cell is where the data should be inserted. Last of all I used students as ListFillRange. I named the set of data in the student list sheet as students.
This is the screen shot of the properties
Macro codes
These are the macros I have used on the enter marks sheet.
McrenterAssignment/MaximumMark
Sub iboxAssign()
Dim value
Range("G22").Select
value = InputBox("Enter assignment mark", "Marks tracker")
ActiveCell.value = value
'Dim value
Range("H22").Select
value = InputBox("Enter maximum mark", "Marks tracker")
ActiveCell.value = value
End Sub
McrfrontMainMenu
Sub mcrfrontendmenu()
'
' mcrFrontendmenu Macro
' Macro recorded 12/28/2006 by OFFICE
'
'Select main menu
Sheets("Marks No").Select
Sheets("Front End Menu").Select
Range("A1").Select
End Sub
McrArchiveMark
Sub mcrArchiveMark()
'
' mcrArchiveMark Macro
' Macro recorded 29/01/2007 by 21bor2413j
'Stops the screen from flickering and updates after doing all the tasks instead of one by one
Application. ScreenUpdating = False
'Find first blank cell in archive sheet
Sheets("Marking Archive").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Range("A2").Select
'Select mark sheet
Sheets("Marks No").Select
' copy student details
Range("A22:I22").Select
Selection.Copy
Sheets("Marking Archive").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Select mark number sheet
Sheets("Marks No").Select
Range("A1").Select
Application.CutCopyMode = False
End Sub
User Form 1(Exit System)
I used the toolbox to design the user form, and then double clicked on the buttons to create the macro code. I have shown the macro codes behind this the user form below
User Form 2(Add New Student)
When the end user clicks on “add new student" a macro goes to the student list sheet and inserts a new row. It then loads a user form. I chose a user form as the method of entering data as it controls the location of the user enters data and this prevents the user from accessing the student list where they may alter existing records. The user enables the user to enter the student roll number, surname, first name and date of birth. Gender and form are entered using a combo-box. This prevents variations in the data entered as the user can only select from a set list.
The combo box for form Obtains the data from the named range form in the lookup sheet. It is linked to cell E2 in the students
Macros code
These are the macros I have used on the add new students
McrCancel
Private Sub CommandButton2_Click()
Unload UserForm3
UserForm3.Hide
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Sheets("Front End Menu").Select
Range("A1").Select
MsgBox "New student details have NOT been added at this time", vbOKOnly, "Failed Data entry"
End Sub
McrOK
Private Sub CommandButton2_Click()
Unload UserForm3
UserForm3.Hide
Sheets("Front End Menu").Select
Range("A1").Select
End Sub
Data Form (Edit/Delete Student Details)
On the main menu, by clicking the edit/delete student details a macro, it will automatically opens a data form which then the usr can edit or delete the details of the students.
Class Boundary
This possess grade boundary table named grades. Is used as a Vlookups to assign grade and comment to students based on their average mark on parent report. There is no macro on this sheet as the user will nerve see it. This acts as a security measure as the most of the essential functions of the system rely on this sheet. I choose to use data form because it will make it easy for the user to edit/delete if she want to change the grade boundary.
This is the screen shot of Data Form for edit grade boundary
This is the grade boundary sheet, which I name it “Grades”.
Marking Archive
This is the sheet where the grades are going to be stored from the input student’s marks. This is one of the important part of the system.
Pivot Table A
The pivots table A linked the data held in the archive to provide the average mark of each student. This pivot will be used to when the user selects the go chart button on the front end the pivot is updated. This sheet is hidden and the user never accesses this sheet. This prevents the user from altering the pivot table and as a result making it impossible to create charts from the pivot table. This pivot will create three different charts. There are no macros on this sheet.
Chart
When a user clicks on the chart summaries button on the front-end menu, the macro first updates the chart pivot before taking the user to this sheet. This ensures that the chart the user is currently viewing presents accurate data. This prevents incorrect charts being printed, as these would contain outdated data. This sheet the user will be enable to view and print chart summering by form, gender and all student
By Form
When the macro button for this chart is pressed, the macro goes to the chart pivot and uses the average of the column titled mark in the archive. The form names are used as column names. The macro then updates the chart and takes the user back to chart. The chart titles and axis are changed to show that the chart is comparing different students.
This is the pivot table by form
The pivot chart by form
By Gender
This chart compares the average mark of male and female students. When the macro button is pressed, the macro goes to the chart pivot sheet and adds the necessary data to the pivot table. It then updates the chart data and adds the suitable titles for the x-axis, y-axis and the chart title
This is the pivot table by gender
The pivot chart by gender
All Student
When the macro button for all student chart is pressed, the macro goes to the chart pivot and uses the average of the column titled mark in the archive. The student names are used as column names. The macro then updates the chart and takes the user back to chart. The correct titles for the x-axis, y-axis and the chart title
This is the pivot table by all students
The pivot chart by Students.
I chose to use first name for the y-axis because from the data I have collected from my user, every individual has a unique first name. The surnames are common for some students. Therefore, I have used the first name.
I was going to use the roll number for students, as it is their unique identity, however, this will not be possible for my user to remember each number. After my user has tested my system, if she wants me to make any changes to this I will but for now I think first name is suitable.
There is a print, which is used to print the currently displayed report. The macro buttons are not printed. I did this by un-checking the print object option in format control.
Macro Code
These are the macros I used on chart summaries.
McrForm
Sub mcrForm()
'
' mcrForm Macro
' Macro recorded 29/01/2007 by 21bor2413j
'
'removes flash when macro is run
Application.ScreenUpdating = False
'Select Pivot A
Sheets("Pivot A").Select
Range("A4").Select
' sort out pivot to add accurate fields to pivot
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Form"
PivotItems("(blank)").Visible = False
'Add Titles to chart
Sheets("Chart1").Select
With ActiveChart
.HasTitle = True
ChartTitle.Characters.Text = "Average Mark, By Form"
'Edit X axis
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Form"
End With
'Hide chart toolbar
ActiveChart.Axes(xlCategory).Select
ActiveChart.ChartArea.Select
Application.CommandBars("PivotTable").Visible = False
End Sub
McrGender
Sub mcrGender()
'
' mcrGender Macro
' Macro recorded 29/01/2007 by 21bor2413j
'removes flash when macro is run
Application.ScreenUpdating = False
'Select Pivot A
Sheets("Pivot A").Select
' sort out pivot to add accurate fields to pivot
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Gender"
Range("B5:B7").Select
Selection.Style = "Percent"
Range("A7").Select
PivotItems("(blank)").Visible = False
' Select Chart Sheet
Sheets("Chart1").Select
'Add Titles to chart
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Average Mark, By Gender"
'Edit X axis
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Gender"
End With
'Hide chart toolbar
ActiveChart.ChartArea.Select
Application.CommandBars ("PivotTable"). Visible = False
End Sub
McrAllStudent
Sub mcrAllStudent()
'
' mcrAllStudent Macro
' Macro recorded 29/01/2007 by 21bor2413j
'
'removes flash when macro is run
Application.ScreenUpdating = False
'Select Pivot A
Sheets("Pivot A").Select
' sort out pivot to add accurate fields to pivot
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="First"
Range("A13").Select
PivotItems("(blank)").Visible = False
' Select Chart Sheet
Sheets("Chart1").Select
'Add Titles to chart
ActiveChart.ChartTitle.Select
ActiveChart.PlotArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Average Mark, By Student"
'Edit X axis
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "First name"
End With
'Hide chart toolbar
ActiveChart.ChartArea.Select
Application.CommandBars ("PivotTable"). Visible = False
End Sub
McrPrint
Sub mcrprint()
'
' mcrprint Macro
' Macro recorded 08/01/2007 by 21bor2413j
'
'
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
McrMainMenu
Sub mcrfrontendmenu()
'
' mcrFrontendmenu Macro
' Macro recorded 12/28/2006 by OFFICE
'
Sheets("Marks No").Select
Sheets("Front End Menu").Select
Range("A1").Select
End Sub
Pivot B
The pivot table pivots the data held in the archive to provide the average mark of each student. When the user selects to go to either of the reports the pivot is refreshed. There is on macro on this sheet since the user will nerve have access to it.
Parent Report
This sheet the user is able to select different students and print reports showing their average mark and grades each student is working at. The Roll number is used to look up the selected student’s average mark from ‘pivot’. This enables the report to be to refer to a particular student by first name and surname. Once the Student is selected the average mark, grade and comment will automatically change to reflect the correct one. After my designed I realise both parent and summary report are the same. So I chose not to do it because it time consume. . There is a print, which is used to print the currently displayed report. The macro buttons and the list box are not printed. I did this by un-checking the print object option in format control
Formula to find students surname using the roll number (linked cell)Formula to find students surname using the roll number (linked cell)
Auto open and auto close macro
Auto- open
This macro removes all toolbars and sheet tabs when the system is opened. I did this in order to control the way the user navigates the system and keeps the user away from areas he must not access.
Auto-close
This sets the toolbar options back to the default settings. This is to enable the next user to use the program.
McrAutoOpen
Sub mcrautoclose()
'
' mcrautoclose Macro
' Macro recorded 23/01/2007 by 21bor2413j
'
' Removes toolbars and sheet tabs
Range("H7").Select
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayWorkbookTabs = False
End With
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Drawing").Visible = False
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayWorkbookTabs = False
End Sub
McrAutoClose
Sub mcrautopen()
'
' mcrautopen Macro
' Macro recorded 23/01/2007 by 21bor2413j
'
' sets the toolbars back to the default.replaces the sheet tabs
With ActiveWindow
.DisplayGridlines = True
.DisplayOutline = True
.DisplayWorkbookTabs = True
End With
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
ActiveWindow.DisplayHeadings = True
Application.CommandBars("Control Toolbox").Visible = True
Application.CommandBars("Drawing").Visible = True
Application.CommandBars("Formatting").Visible = True
End Sub
Student List
I will used the same list box to generate report parent. There is on macro on this sheet as the user will not have access on it accept when the user want to add new student using user form. This is the source of the data required for the list box. This is the student list sheet, which I name it ‘students’.
Sheet protection
The sheets that the user is able to see are protected to prevent any editing. Not all cells where able to be protected as data entered from input boxes overrides any protection. It was not necessary to protect cells with data entered from list boxes and input boxes they would not work if the linked cell is protected.
Testing
Evaluation
This section makes sure that the result I have created meets the users needs and is what the user required.
In order to evaluate the system I will use the performance indicators and objective to judge how well I have created the solution
Performance Criteria
For the system to be well organized and a helpful tool it should meet the following criteria of performance:
- It should be able to add and delete /edit students if required.
- Ms Gammon will be able to correct mistakes that she might make during marking, this will make the system to be more neatly presented and pleasant.
- The system will generate automated reports of each student by click of a button showing their performance that can be sent to parent.
- It will be easy and clear to use.
- The new system will produce an average performance percentage for each student. And also it will automatically convert numerical marks to percentages.
- It should have protection to prevent accidental deletion of important formulae, numbers, table, chart and text. All the sheet and cells in the system must be protected with security to prevent these.
- Graph and charts should be generated quickly and easily to compare boys and girls performance, All student and by form.
- It should automatically use colour code for student grades.
It should be able to add and delete/ edit students if required.
Achieved
Add New Student
When the user clicks on add new student button on the front-end menu, a user form directly loads enable the user to enter student details.
The user form is clearly laid out and structured. It is clear what data must be put in each box. Entering data for form and gender is made quicker by the use of combo boxes. These save the user time because he does not have to type out the data. When the “OK” button is clicked on the user form unloads and the user is taken to the front-end menu, and the process of adding a new student to the system is complete.
Edit/Delete student details
In order to delete a student there is a button on the front-end menu has a macro that opens a data form to edit the student list.
Although this data form deletes the student from the student list in under a minute, the data form does not delete the student’s entries in the marking archive. This means that data pivoted from the marking archive is sure to be up-to-date as it may contain data for students who have left. The system is in part updated as list-boxes are updated when a change is made to the student list. This was recognized during the Testing stage.
The system will be able to correct mistakes that she might make during marking, this will make the system to be more neatly presented and pleasant.
Achieved
When the user clicks on this button, the enter marks opens immediately:
It is clear what data needs to be put in each cell and there are titles at each stage, so the user knows what they are doing.
The user selects the student for whom she wants to enter marks from a list box and also selects the name of the test from a list box. This means that the user does not have to type anything in except the actual mark, saving her time and reducing the chances of making errors such as spelling mistakes or entering data in the wrong cell.
The mark is entered through an input box. This saves the user time, as they do not have to go through the process of looking for the correct cell.
When the user has entered the marks, she clicks on the ok button, which copies the data to the marking archive.
The system will generate automated reports of each student by click of a button showing their performance that can be sent to parent.
Achieve
There is a macro button for parent report on the front-end menu. The user can select a student on the report sheet using the list box I crated. This is more easy and efficient for the user. The user can easily change the student by clicking on a different name in the list. The report has already been created and will change when the selected student is changed. This sheet is user friendly as the user has to do no typing at all. The use the list box reduces errors and saves the user the trouble of having to manually type in roll numbers
an example of selected student
It will be easy and clear to use
Achieved
All the tasks that the user would want to use on a regular basis have a separate button on the front-end menu, meaning that the user can openly access them. However I found that some of the tasks that are not often carried out have been left out like Pivot tables and marking archive. This acts as a security measure as the most of the essential functions of the system rely on this sheet. If this sheet is ruined, the user may not be able to produce reports and charts, and by hiding this sheet, I have reduced the chances of the data being deleted or altered.
It should have protection to prevent accidental deletion of important formulae, numbers, table, chart and text. All the sheet and cells in the system must be protected with security to prevent these.
When the system is opened, input box appears after enabling the macros. If the wrong password is entered then access will be denied. This meets the user’s needs for security as unauthorised people are unable to view or edit the system. I have therefore achieved that.
On the sheets where data is entered I have tried to control the data that is entered but because most of the data is entered through input boxes, list box and user forms, because of this, I have protected all the sheet in an attempt to control data but this is not efficient because data is entered through input and user forms. When data is manually typed in cells, the system notifies the user of any errors, but when data is entered from input boxes and user forms, the system fails. An example of this is the “Student list sheet”. Gender that can be entered should be F and M and I have put validation in place to prevent incorrect data being entered
If I use the macro button from the front-end menu, a user form is opened and,
when I try to manually type in incorrect data into the combo box the validation restricted. In places where I have locked the cells, typing cannot take place. Mark sheet and parent report sheet I couldn’t able to lock the cell. Because if I lock the cell in parent report and marks sheet the list box there will not work, that sheets are unlocked. However, when I was working through my system I figured out that, it was the cell that linked to the list box had to be unlocked. In spit of this, the rest is locked. The system has very few gateways open to error/ accidental deletion.
Graph and charts should be generated quickly and easily to compare boys and girls performance, All student and by form.
Achieved but need to be improved
I have achieved some part of this successful; however, there is improvement I need to make. Once the user clicks on the button called student summaries on the front-end menu the user will be takes to chart sheet. It is on this sheet that the user will be able to select the type of chart that she wish to view. All she have to do is click on a button, it changes as soon as the button is clicked. Each chart can print out and also chart is also up to date. Eg
I chose to use first name for the y-axis because from the data I have collected from my user, every individual has a unique first name. The surnames are common for some students. Therefore, I have used the first name.
However I think I have to used both roll number and individual first name as it will help to identify her student in case two or more student have the same first name. I will therefore improve this chart to have appropriate Y-axis.
It should automatically colour code student grades.
Achieved
I used conditional formatting on the grade, so that when the user selects a student name it will automatically change the grade colour using the colour code. I used text
Colour to make the report look more professional and attractive.
Weaknesses and limitations
Cell that is linked to the list box on enter mark sheet and parent report sheet cannot be locked. For the list box to work, the linked cell needs to be unlocked. This cause a problem because all the important formulae are on these sheets, and if the user is accidentally deletes this then the sheets will not make logic. This is a minor problem since even if the user does delete the value in the cell, the minute she clicks on another student from the list, the new value is inserted and the roll number will go into that cell allowing the Vlookups to work.
Enter maximum marks
The user find it difficult to enter new maximum mark through the use of input box because I set a wrong validation which means that it only one number that is 50 that can be enter into the input box for maximum. However I didn’t spot this problem until my user test my system.
User has to answer twice to the question ‘do you wish to save and exit’
I created two user forms for the exit macro. The second helps the user the choice of saving and exiting.
But the user clicks on no, excel brings up its own dialogue box asking again if the user wants to save before exiting.
This might upset the user having to keep clicking buttons. The positive side of this is that it helps further protection in case they by mistake clicked the wrong button.
Improvements
I found that when that when the print button is clicked on it immediately prints, and the user does not get a print preview. I would improve the system by having the system show a print preview before printing the chart
The system could be made more user friendly by having cell comments. The user felt confused at times and cell commentary would help a beginner user understand what to do and what exactly each button does.
I also found that the print charts sheet could be improved and made more users
Friendly by having the date printed on the charts. This would enable the user to identify the most up-to date charts. Also I could have put both roll number and first name for chart by all student as it will help the user to identity individual student instead of only first name.
I would improve the system by having a user form load asking the user if they are sure they want to exit. Clicking on yes would load the current user form while clicking on no would take the user back to the front-end menu.
User Manual
The user manual is here to help and guide you to use the system. Also it helps you to overcome any problem you encounter. This will be under the ‘Troubleshooting’ or ‘What to do if’ section.
System Requirements
In order to use the system, you require using standard PC with at least 125Mb of memory. Microsoft office needs to install including the Excel component. The system will work successfully with Excel 2000 or 97. The system uses originally 3517Kb of disk space. A printer is needed, as you need to print out report.
Opening the System
I have installing a shortcut icon on the desktop, so that it is easier for you to open the system. To open the system you need to double click on the file Mark Tracker located on the desktop, and wait for it to open.
When you open it, a message box will appear asking you whether you would like to enable /disable macro, an automated set of instructions. The button that you should click is Enable Macros
A message box then appears saying that the file is protected and asks you to enter a password. The default password is “pass”, all in lower case. It is possible to change this password as is covered in the “what to do if” section. The system cannot be accessed without the correct password.
After the correct password has been entered, another screen comes up asking for the password for write access. Write access means that you are able to save changes made to the system while read access means that you can only view the information but cannot make changes. The default password is “pass”, in lower case. It is possible to change this password and it is covered in the “what to do if…” section. Without the password you will not be able to save any changes to the system
Using the system
After entering the password, the front-end menu will appear on the screen. From here you are able to select all the main tasks by simply clicking on the button that suits your needs.
I will be going through the standard tasks and steps of each macro button on the following pages. However, there will be a troubleshooting section telling you what to do if you come upon a difficulty.
Enter Mark
When you click on the button you are taken to the enter marks sheet.
To enter marks, first you must select the student you wish to enter marks for. To do this, you must go to the section titled “select student ID”. There is a list of students where you select the student you want by clicking on their name.
You then click on the enter marks button the screen below loads
You enter assignment mark in the space provided and click on the OK button. After you click ok, another input box will come saying enter maximum mark. You then ‘enter maximum mark and than click ok’. A percentage is automatically calculated and placed in the percentage cell.
When you are satisfied with the data entered you click on the button. This copies the data and stores it.
There is no confirmation that the action has been carried out.
You have to carry out the same procedure when you want to enter mark for another student.
If you do not want to add any more marks you click on the button, which takes you back to the main menu.
Add New Student
This macro button will load a ‘user form’, which you use to add new student details.
You must type in the data you want in the spaces provided. In order to see all options for form and gender, you click on the button with the triangle pointing down,
This displays all options available. If you are happy with the data you have entered you click on the OK button. You will be taken to the main menu. There is no confirmation that the action has been completed successfully. If you are unhappy with the data entered you click on the cancel button. All the data you have entered is deleted and you are taken to the main menu.
When data is inputted into the students list it can be edited, if essential, by using data entry form in the next section.
However if any field is left blank, the new student details will not be added to the system, as a message box will come up telling you, you failed to enter data in on of the required field.
Edit/Delete Student Details
The macro button will take you to the student’s list sheet, and then a ‘data entry form’ will load and allow you to make change /delete students details.
There are spaces for you to type in the data you want. To scroll back you click on the find prev button. To scroll forward you click on the find next button. To delete a whole record you click on the delete button. If you want to close the screen you click on the close button and you are taken to the main menu.
Important information
You must take this in concern, as any changes made in the data entry form will be stored in the student list table. Example if you make a mistake on the table then you has to edit using this edit student’s form. I recommend that you use the form on page 90 to add new student.
Parent Report
This macro button will take you to the parent report sheet where you can create reports for students and print them out.
To generate a report you only select the student you want the report for from the list box. You scroll down until you see the name you want and click on that. The names in the report will automatically changes the student name, grade, average mark and the comment. All will automatically generated
Once you have selected the student you want, you can then print reports. To do this you click on the print button. Buttons are not printed but the school logos are printed
If you want to go to main menu you click on the main menu button, which takes you back.
Important information
You are not required to type anything in this page.
Edit Grade BoundaryThis macro button will take will take you to grade boundary sheet. The screen below comes up. You then type in the new data in the corresponding spaces provided.
The new button enables you to enter a new grade, mark and comment
The delete button enables you to delete a marks, grade and comment
The find prev button enables you to scroll back to before display marks
The find Next button enables you to scroll forward to the next record
The close button closes the screen and takes you back to the main menu
Important information
You must take this in concern, as any changes made in the data entry form will be stored in the grade boundary sheet. Example if you make a mistake on the table then you has to edit using this edit grade boundary form.
Chart Summaries
This macro button will take you to the chart summaries sheet. This sheet creates charts, which have been summarised by all student average mark, by gender and by form. The charts are automatically updated every time a new mark is made, so all of the charts are accurate and correct and always up to date. You can view and print the charts and also can go back to the main menu.
This is how average mark of all student look like, in order to view this chart you need to click on by all student macro button
This is how average mark; by gender looks like in order to view this chart you need to click on by Gender macro button.
This is how average mark; by gender looks like in order to view this chart you need to click on by Form macro button.
Exit System
This macro button is placed on the main menu this will guide you through exiting the system.
Microsoft Excel will bring this up to make sure that you really want to exit.
Troubleshooting
This section will help you to overcome any problem you uncounted.
Q1. What if the password is not accepted?
Ans. Ensure that you are entering the correct password. The default password is “pass”. If you have changed it you need to use the new password
Verify that caps lock is off. If caps lock is on the caps lock light will be on and you can turn it off by clicking on the caps lock button
Q2. If you want to change password
Ans. To change the password you go to file, save as
The window below comes up. You then go to the tools option
You go general option
You can then type in the new password you want and click on ok
It is also suitable to have the always create backup option checked as it ensure that you have another file.
Q3. What if the data in the list boxes doesn’t appear?
Ans. You may have scrolled too far down. You must scroll back to the top. There is a lot of blank spaces in the bottom of the list box, this make it easy to have enough space to add new student.
Q4.What if the record is accidentally selected?
Q4. What if none of the buttons work?
If the record is accidentally selected the formulae are not going work, therefore you need to click on one of the student details, and you will get the result back.
Q5. What if none of the buttons work?
Ans. Ensure that that you selected enable macros when the system starts up. If not close the system and re-open the system and click on enable macros.
Q6.What if the roll number (linked Cell) is deleted?
Ans. Click on the list box and select a student and the roll number will appear again.
Q7.What if you want to delete all students records and start over again.
Ans. Firstly you must enable sheet tabs to do this you go to tools then options on the menu bar.
Check the option for sheet tabs and click OK
First you must go to the student list
Once you are in the student list you select A2 and press Ctrl Shift and down arrow. This would select all the records there are. You then you delete.
You must not delete the first row, as these are necessary
Also you must delete all the data in the marking Archive. To do this you must be in the marking archive sheet
Once you are in the archive you select A2 and press Ctrl Shift and down arrow. This would select all the records there are. You then you delete.
You must not delete the first row, as these are necessary
Q8. What if I cannot see the change I made on student list
Ans. You must always have to save your work before you exit as any changes would be lost if you do not do so.
Q9. What if I cannot type data into the cell?
Ans.
That cell may be protected, meaning that it is locked. You are unable to type any data into these cells, as there is no reason for you to do so. You should refrain from changing data in protected cells. However if you feel that you must type data into cells you unlock it by going to tools then protection then unprotect sheet