Using the VBA Label to Create Flash Cards

This tutorial uses the VBA label to create flash cards and uses Excel.

First, go to the VBA Editor/IDE; ALT + F11.

Right click on any line/branch in the VBAProject Explorer at the upper left below the toolbars. Go to Insert and click on UserForm.

SIDEBAR----- you can also resize the form by clicking on any blank space on it and using the white squares along the border. SIDEBAR-----

Right click on the VBA label and choose Copy.

Click on a blank space on the form and choose Paste. A new VBA label is created in the center of the form. drag it to under the first label.

Repeat the previous step and move the third label under the second.

In the toolbox, click on the rectangular icon which is the Command Button and then click on the form to create one, or drag to draw it. Repeat to create a second command button.

AddCommandBtn

Now, create two checkboxes, we're going to sophisticate our flash cards.

Click on the left Checkbox. Change its Name to Num and its Caption to No..

Click on the right Checkbox. Change its Name to Cap and its Caption to Capitols.

SIDEBAR----- After you see that the code works, you can reposition the controls and change the font size if you like. To change the font, its size, etc. click on the current font in the Font property and then on the ellipsis. SIDEBAR-----

Right click on the form in the Project window and then on View Code...

Option Explicit
'  Option Base 1 causes the array indexes to
'  start at 1 instead of 0
Option Base 1
'  Since we want most of our variables to
'  retain their values after exiting a Sub procedure,
'  we declare them before the first subroutine.
'  Variables declared in a Sub are reset to an Empty status
'  when the Sub ends.
Dim MyValue As Integer
Dim iStart  As String, iEnd As String, iCn As Integer
Dim cnt As Integer
Dim iUsed() As Integer
Dim ChangeCaption As Integer
Sub NextFlashCard()
    Dim CntArr As Integer
    Do
    MyValue = Int((iEnd * Rnd) + 1)
        For CntArr = iStart To iEnd
            If MyValue = iUsed(CntArr) Then Exit For
            If iUsed(CntArr) = 0 Then Exit Do
        Next
    Loop
    iUsed(cnt) = MyValue
    If Num Then
        Label1.Caption = MyValue
        Label2.Caption = ""
    Else
        Label1.Caption = ""
        Label2.Caption = Worksheets("Sheet1").Range("A" & MyValue).Value
    End If
    Label3.Caption = ""
    Label4.Caption = iCn
'    Repaint
    cnt = cnt + 1
    iCn = iCn + 1
End Sub
Private Sub Answer_Click()
    If ChangeCaption = 0 Then
        ChangeCaption = 1
        If Num Then
            If Cap Then
                Label2.Caption = Worksheets("Sheet1").Range("A" & MyValue).Value
                Label3.Caption = Worksheets("Sheet1").Range("B" & MyValue).Value
            Else
                Label2.Caption = Worksheets("Sheet1").Range("A" & MyValue).Value
            End If
        Else
            Label3.Caption = Worksheets("Sheet1").Range("B" & MyValue).Value
        End If
'        Repaint
        Answer.Caption = "Question"
    Else
        If cnt = 51 Then
            MsgBox "End"
            End
        End If
        ChangeCaption = 0
        NextFlashCard
        Answer.Caption = "Answer"
    End If
End Sub

Private Sub StopRun_Click()
    End
End Sub
Private Sub UserForm_Initialize()
iStart = 1
iEnd = 50
ReDim iUsed(50)
For cnt = iStart To iEnd
    iUsed(cnt) = 0
Next
cnt = 1
iCn = 1
Randomize
ChangeCaption = 1
    Label1.Caption = ""
    Label2.Caption = ""
    Label3.Caption = ""
    Label4.Caption = ""
    Answer.Caption = "Question"
End Sub

In Excel, click in cell A1 (on Sheet1). Go to the Data menu, click Import External Data then Import Data. Choose the file abc (or the something else), make sure "Delimited" is marked near the upper left of the dialog box. Press Finish and then Enter. You should have the 50 states in column A from rows 1 to 50 and the capitals next to them in column B.
SIDEBAR-----

All this code runs from within the form. We need a Sub to open the form.

Right click on any line in the VBAProject window, then on Insert, and choose Module. Paste the following in the code window of the module...

Sub OpenForm()
    UserForm1.Show 0
End Sub

If you want to see how the code works in slow motion - step in mode - you can set a breakpoint by putting the cursor in the line...

Private Sub Answer_Click()

and pressing F9 to toggle the breakpoint on. Run the OpenForm subroutine as before. When you click on the Question/Answer button the code will stop on that line and from there you can press F8 to step through the code one line at a time to see how it works.

You could also start stepping through the code (F8) from the OpenForm subroutine and when you get caught in the loop that sets all the values in the iUsed array, set a breakpoint after the loop and press F5 once to complete the loop and then continue with F8.

If, along with memorizing the 50 state capitals, you have memorized the states in alphabetical order, you can check the No. checkbox. You would then be prompted with a number and have to know the state corresponding to it, or, if you also checked the Capitals checkbox, know the state and the capital.

Return from VBA Label to "Free VBA Tutorials"

Return from VBA Label to our "Home page"