Using the Students and Exams Custom Form

Now that you've prepared all of the required VBA procedures, let's see how the form responds to the user's actions:

1. Switch to Microsoft Excel window and activate Sheet2.

2. Click the Display Form button.

Clicking the Display Form button runs the DoStudents procedure. This procedure displays the Students and Exams custom form. Before the form appears on the screen, Visual Basic executes each statement entered in the UserForm_Initialize procedure. The result is the form shown in Figure 10-19.

Vba Final Exam
Figure 10-19: The Display Form button on the worksheet lets you quickly access the Students and Exams custom form to view or enter data. When the form is loaded, only the controls that apply to the selected option button are shown.

After the form is displayed, you can enter a new student and click OK to transfer the student's data to the worksheet. When you click the OK button, the cmdOK_Click procedure is executed. Notice that you can't enter the exams taken by the new student because the second page (Exams) of the MultiPage control is disabled at this time. Once the new student's data is written to the worksheet, the form is redisplayed. You can continue entering the data, or you can click Cancel to remove the form from the screen. When you click the Cancel button, the cmdCancel_Click procedure is run.

3. Using the Students and Exams custom form, enter data for two new students. At any time, you can click the Active option button and load the data for the existing students. When you click the Active option button, the Name Range label and the RefEdit control become visible.

4. Click the Active option button, and then click the minus button in the RefEdit control.

5. Select the range of names in the worksheet, as shown in Figure 10-20.

SJChaplO.Kls

BTTD

A j B

c

D

E

F

G

H

1

J

K

L M

"i

Display Form

I 2 Mathematics

English

Fi ene Ii

Math

Physics

'A1! Last Ii.in.•

Fil st Ihme

Am 1

Grade

Oírte

Grade

Pate

Gr,«le

Date

Grade Date

jlI

ll 27-71 -99¡)Vlvtka

4

aau-/,r-J4'Droll

Robert

t

Linguistics

7

231.12-3fliCrúmwsll 453-43.65ÍBromsfcy 142-92-1 ^McDonald

/vcnnc

1

EniiNcü Ctemistry Computer Scienc

I

Aden William

«

Sh5et2l$P$3:$CÍ8

m

8

429-77-54íPosanm

Maris

2

Linguistics

9

IO

jlC

M H

1 > n \ mfo íl.vfy liShsetï/sheets

/

i.il

i un

Figure 10-20: Using the RefEdit control, you can specify the range of cells containing the data that you want to work with.

Using the RefEdit control, you can select a range of cells in a worksheet. In this example, select cells containing students' last and first names. It's important that you select valid data. Start by clicking the first name below the Last Name column heading (cell B3) and drag down and to the right to include students' first names. Notice that the form is temporarily hidden while you work with the RefEdit control. The range of cells that you select appears in the RefEdit control. Click the minus button in the RefEdit control to return to the form. As you return to the form, the refNames_Change procedure is run. This procedure uses the range address to populate the list box control with the names of students.

Figure 10-21:

The list box on the form is populated via the RefEdit control with the data stored in a worksheet.

The last statement of this procedure calls the lboxStudents_Change procedure to ensure that the form's text and combo boxes are synchronized with the student selection in the list box.

The list box displays the names of active students. The selected student's data is displayed in the text and combo boxes on the left (see Figure 10-21).

Figure 10-21:

The list box on the form is populated via the RefEdit control with the data stored in a worksheet.

6. Click any name in the list box, and check the student's data.

7. Click any name in the list box, and click the Exams tab.

The Exams page displays the name of the selected student (see Figure 10-22). The TabStrip control shows the exam subjects. If the selected student has taken any of the exams, the date and the exam grade are displayed when you click the appropriate subject tab.

Figure 10-22:

The Exams page shows the date and exam grade for the selected student and subject.

You can enter or change a student's grade and exam date by using the provided combo box and calendar control. Visual Basic asks that you confirm the modification of data (review the VBA code for the Calendar1_Click and cboxGrade_Click procedures). After responding Yes in the dialog box, the selected date or grade is written to the corresponding column in a worksheet (see Figure 10-23).

The TabStrip1_Change procedure ensures that when you click the subject tab, Visual Basic displays the exam grade and date from the appropriate spreadsheet cell. The MultiPage1_Change procedure ensures that when you click the Exams page, the lblWho label control displays the last and first name of the student who is currently selected in the list box.

EjChapl t.ilt

■ -Inf

A 1

B

c

D E

! L J

*

H I

J. | K

L

M

1

Display Form

English

French

Math

Physics

JL

SSI!

Last Harm

! First lliimc

Year M.ijoi

Grade

Date

Grade

Date

Grade Date

Grade

Date

3

127-71 -9906

Wy-lka

Ann

2 Mathematics

A

Sß/2002

B_5/14/2002

A

5/15/2002

' 1

690-77-3433

Drod

Robert

4 Linguistics

B

5(6/2002

A

5/7/2002

5

234-12-3434

Cromwei

Yvonne

1 English

6

453-44-6523

Bromsfcy

Adam

3 Chemislry

C

3J6/2002

B

3/6/2002

B 3/6/2002

B

3/20/2002

7

142-92-1231

McDonald WilBam

1 Computer Science

a

429-77-5454

Polanco

Maria

2 Linguistics

9

1

*

H < k .1 \ [nhj Surv^/ XSheet? / /

hi

1 lii

Figure 10-23: Data in columns F-M on the spreadsheet is entered via the Exams tab on the Students and Exams user form.

Figure 10-22:

The Exams page shows the date and exam grade for the selected student and subject.

0 0

Post a comment