After a test is completed and scored, the results are added to the Students worksheet and the XML file is automatically updated (discussed later). Results can be viewed from the Students worksheet, where the XML list that holds all test results has been created in columns I through K. Although there is no need to allow the user to export the results, they are allowed to clear the data from the XML file.
Users may clear the list in the worksheet manually or by clicking the Command Button control labeled Reset. This triggers the Click() event procedure that follows. To clear the list, I first activate its range of cells before using the InsertRowRange property of the ListObject object to determine the next available row in the list. The list's range must be active or the InsertRowRange property fails—generating a runtime error. Data in the list is deleted using the Delete() method of the Range object and shifting cells up. Note that I do not update the XML document file after clearing the range. It's not necessary since it will be updated with the next completed test.
Private Sub cmdResetResults_Click() Dim insertRow As Integer Dim IsObj As ListObject
'Clear the list.
Set lsObj = ActiveSheet.ListObjects("Results") If Not lsObj.Active Then lsObj.Range.Activate End If insertRow = lsObj.InsertRowRange.Row Range("I1").Select If insertRow <= 2 Then Exit Sub Range("I2:K" & insertRow - 1).Delete xlShiftUp End Sub
Students are added to or removed from the data base by editing the corresponding XML document (students.xml) via the Students worksheet. When the Click() event procedure of the Command Button control labeled Update is triggered, the data in the list overwrites the data in the XML document file. Again, I have used the Export() method of the XmlMap object to update an XML file. The UpdateStudentXml() sub procedure was also entered into a standard code module because it is called from more than one object module.
Private Sub cmdUpdate_Click()
UpdateStudentXml True End Sub
Public Sub UpdateStudentXml(Optional UpdateCmbList As Boolean) Dim mapStudents As xmlMap Dim pathStudents As String
On Error GoTo UpdateError
'Update student XML file.
pathStudents = ActiveWorkbook.path & "\Students\students.xml" Set mapStudents = ActiveWorkbook.XmlMaps("students_Map") If mapStudents.IsExportable Then mapStudents.Export URL:=pathStudents, Overwrite:=True
MsgBox "XML map is not exportable!", vbOKOnly, "XML Map" End If
'Update combo box if this procedure was called from 'Update button on sheet 3.
If UpdateCmbList Then ListStudents Exit Sub
MsgBox "Student list not updated." & Err.Description, _ vbOKOnly, "File Save Error: " & Err.Number
The ListStudents() sub procedure is called from UpdateStudentXml() and the Open() event procedure of the Workbook object. The procedure serves to update the list of students listed in the Combo Box control on the Math Game worksheet. Notice that I use the ListObject object to retrieve the student names. This is another advantage of Excel lists—the Range property of the ListObject object makes it easy to access the content of the list, so you don't have to search through the rows to find the last item. It is also worth noting that in order to access the Combo Box control, I had to qualify the worksheet name in the object path because the ListStudents() sub procedure is not in the same code module as the control.
Public Sub ListStudents()
Dim studList As ListObject Dim student As Range Dim I As Integer
'Add student list to combo box.
Set studList = Worksheets("Students").ListObjects("Students") For I = 2 To studList.Range.Rows.Count
MathGameSheet.cmbStudents.AddItem studList.Range.Cells(I, 1).Value Next I End Sub
Was this article helpful?