Writing VBA Procedures for the Students and Exams Custom Form

The custom form Students and Exams contains about a dozen VBA procedures, which are shown below. The code of these procedures has to be entered in the form module. To activate the form module, double-click the form background.

1. From the combo box at the top left-hand side of the Code window, choose (General). The Procedure selection combo box on the right should display (Declarations). Type the following variable declarations:

'Declarations Dim r As Integer Dim nr As Integer Dim indexPlus As Integer Dim YesNo As Integer

2. Enter the code of the UserForm_Initialize procedure to set the form's initial settings:

Private Sub UserForm_Initialize() 'select first page of the MultiPage control 'page numbering begins from zero (0)

Me.MultiPagel.Value = 0

'choose the New option button optNew.Value = True

'hide three controls on startup lblLast.Visible = False refNames.Visible = False lboxStudents.Visible = False

'populate the Year combo box With Me.cboxYear .AddItem "1" .AddItem "2" .AddItem "3" .AddItem "4" End With

' populate the Major combo box With Me.cboxMajor

.AddItem "English" .AddItem "Chemistry" .AddItem "Mathematics" .AddItem "Linguistics" .AddItem "Computer Science" End With

' populate a combo box with grades With Me.cboxGrade .AddItem "A" .AddItem "B" .AddItem "C" .AddItem "D" .AddItem "F" End With

'display date in the lblDate label control Me.lblDate.Caption = Me.Calendar.Value

'activate the first tab in the TabStrip control

Me.TabStripl.Value = 0

'activate the SSN text box Me.txtSSN.SetFocus End Sub

3. Enter two procedures to control the option buttons (optNew_Click and optActive_Click):

Private Sub optNew_Click() lblNames.Visible = False refNames.Visible = False lboxStudents.Visible = False Me.MultiPage1(1).Enabled = False

If lboxStudents.RowSource < > "" Then Me.txtSSN.Text = "" Me.txtLast.Text = "" Me.txtFirst.Text = "" Me.cboxYear.Text = "" Me.cboxMajor.Text = "" Me.txtSSN.SetFocus End If Me.txtSSN.SetFocus End Sub

Private Sub optActive_Click() lblNames.Visible = True refNames.Visible = True refNames.SetFocus

If lboxStudents.RowSource < > "" Then lboxStudents.Visible = True Call lboxStudents_Change End If End Sub

4. Enter the code of the lboxStudents_Change and refNames_Change procedures. These control the behavior of the RefEdit and list box control placed on the Students page:

Private Sub lboxStudents_Change() indexPlus = lboxStudents.ListIndex + 3

With ActiveWorkbook.Worksheets("Sheet2")

Me.txtSSN.Text = Range("A" & indexPlus).Value Me.txtLast.Text = Range("B" & indexPlus).Value Me.txtFirst.Text = Range("C" & indexPlus).Value Me.cboxYear.Text = Range("D" & indexPlus).Value Me.cboxMajor.Text = Range("E" & indexPlus).Value Call TabStrip1_Change Me.MultiPage1(1).Enabled = True

End With End Sub

Private Sub refNames_Change()

lboxStudents.RowSource = refNames.Value lboxStudents.ListIndex = 0 lboxStudents.Visible = True Call lboxStudents_Change End Sub

5. Enter the code to control the command buttons OK (cmdOK_Click) and Cancel (cmdCancel_Click):

Private Sub cmdOK_Click()

If Me.optNew.Value = True Then Me.Hide

ActiveWorkbook.Sheets("Sheet2").Select r = ActiveSheet.UsedRange.Rows.Count nr = r + 1

Range("A" & nr).Value = Me.txtSSN.Text Range("B" & nr).Value = Me.txtLast.Text Range("C" & nr).Value = Me.txtFirst.Text Range("D" & nr).Value = Me.cboxYear.Text Range("E" & nr).Value = Me.cboxMajor.Text

Me.txtSSN.Text = "" Me.txtLast.Text = "" Me.txtFirst.Text = "" Me.cboxYear.Text = "" Me.cboxMajor.Text = "" Me.txtSSN.SetFocus

'redisplay the form Me.Show

Else

MsgBox "This control is currently unavailable." End If End Sub

Private Sub cmdCancel_Click() Unload Me

Set Students = Nothing End Sub

6. Enter the procedure cboxGrade_Click to control the Grade combo box located on the Exams page:

Private Sub cboxGrade_Click()

YesNo = MsgBox("Enter the grade in the worksheet?", _

vbYesNo, "Modify Grade") If YesNo = 6 Then

Me.lblGrade.Caption = cboxGrade.Value Select Case TabStrip1.Value

Case 0

Range("F"

&

indexPlus)

.Value

= Me.lblGrade.Caption

Case 1

Range("H"

&

indexPlus)

.Value

= Me.lblGrade.Caption

Case 2

Range("J"

&

indexPlus)

.Value

= Me.lblGrade.Caption

Case 3

Range("L"

&

indexPlus)

.Value

= Me.lblGrade.Caption

End Select

cboxGrade

.Value = ""

7. Enter the Calendar1_Click procedure, as shown below: Private Sub Calendar1_Click()

YesNo = MsgBox("Enter the date in the worksheet?", vbYesNo, _ "Modify Date") If YesNo = 6 Then Me.lblDate.Caption = Calendarl.Value Select Case TabStripl.Value Case 0

Range("G" & indexPlus).Value = Me.lblDate.Caption Case l

Range("I" & indexPlus).Value = Me.lblDate.Caption Case 2

Range("K" & indexPlus).Value = Me.lblDate.Caption Case 3

Range("M" & indexPlus).Value = Me.lblDate.Caption End Select End If End Sub

8. Enter the TabStrip1_Change and MultiPage1_Change procedures, as follows:

Private Sub TabStrip1_Change() indexPlus = lboxStudents.Listlndex + 3

With ActiveWorkbook.Worksheets("Sheet2") Select Case TabStripl.Value Case 0 ' English Me.lblGrade.Caption = Range("F" & indexPlus).Value Me.lblDate.Caption = Range("G" & indexPlus).Value Case l 'French Me.lblGrade.Caption = Range("H" & indexPlus).Value Me.lblDate.Caption = Range("I" & indexPlus).Value Case 2 'Math Me.lblGrade.Caption = Range("J" & indexPlus).Value Me.lblDate.Caption = Range("K" & indexPlus).Value Case 3 'Physics Me.lblGrade.Caption = Range("L" & indexPlus).Value Me.lblDate.Caption = Range("M" & indexPlus).Value End Select End With End Sub

Private Sub MultiPage1_Change()

Me.lblWho.Caption = Me.txtLast.Value & ", " _

& Me.txtFirst.Value Call TabStrip1_Change End Sub

0 0

Post a comment