Databases Reference
In-Depth Information
FIGURE 6.5
Creating a drop-down list of values in a cell.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=CmtList$
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub
In this particular instance, only five comments were assigned. The user could modify the array
of the subroutine to allow as many comments as desired. Notice that all the comments to be made
available in the drop-down box are stored within a string (CmtList$) and that they are separated
from one and other by means of commas (Chr(44)
=
“,”). The preceding subroutine creates an
effect illustrated in Figure 6.5.
6.5
ANALYSIS WITHIN REPORT WORKSHEETS
The created report, as it currently stands, is useful for the application it was created for; however,
some additional functionality can be added that enhances its value to the user. Recall that the
instructor utilized the highest of three grading methods to calculate the final grade for each student.
The instructor would probably like to know how often each grading method was utilized to
determine which grading schemes work best for a particular class. Does it benefit the student more
to drop all the quiz scores or the lowest exam grade? The answers to such questions could help
the instructor make the class more conducive to learning.
A simple routine can be set up within this report to highlight the grading method that was
utilized for each student. This will instantly give the instructor a visual indication as to the relative
use of each grading scheme. If the user presses the “Grade Analysis” button on the ClassGrades
Worksheet, the grading method (1, 2, or 3) will be highlighted on the ClassGrades Worksheet as
illustrated in Figure 6.6.
Private Sub Button_Grade_Click()
'Determine Which Grading Scheme (1,2, or 3) was utilized
For ii = 12 To LastRow("SampleReport.xls", "ClassGrades")
For jj = 0 To 2
