UserForm.show issue

[wtom0412]

Good Morning All,

I have a very small anoying issue that I just can't figure out.

The purpose of the application is a Training Module which asks the User a Question with five multiple choice answers. There can be only one correct answer. There will be 10 modules with up to 25 questions per module.

Just to start, I have three UserForms (Question_1, Question_2, and Question_3). I have an Excel Workbook that contains three TABS (1, 2, and 3) each of which contain the Question and the five answers (an OptionButton next to each answer allows the Author to designate one answer as the correct one).

The UserForms pull in the relevant text from the WorkBook (Question_1 from TAB 1, Question_2 from TAB 2 etc). Once the user has answered the question correctly (via clicking an OptionButton on the UserForm next to the correct answer), a CommandButton appears (using Me.CommandButton1.Visible = True) labeled "Next" which calls the next UserForm.

The issue I have is I get an error "Run-time error '424': Object Captured" when I call the next UserForm.

Because all of the Userforms are the same, I have duplicated the UserForms, and copied and pasted the VBA to each form (changing relevant data between the three).

Following is the code on the first UserForm (Question_1) that pulls the initial Data from the WorkBook (TAB 1)....

Private Sub UserForm_Initialize()

Me.CommandButton1.Visible = False

Label4.Caption = Sheets("1").Range("D2").Value
Label5.Caption = Sheets("1").Range("D4").Value
Label6.Caption = Sheets("1").Range("D6").Value
Label7.Caption = Sheets("1").Range("D8").Value
Label8.Caption = Sheets("1").Range("D10").Value
Label9.Caption = Sheets("1").Range("D12").Value

TextBox7.Text = Sheets("1").Range("M3").Value
TextBox9.Text = "0"
TextBox9.Text = "0"

End Sub

The next code is repeated five times (one for each OptionButton - I have only showed it once but made appropriate changes between each)

Private Sub OptionButton1_Click()
TextBox8.Text = "1"

If TextBox8.Value = TextBox7.Value Then

Label10.Caption = "Correct, click Next to go to the next question!!"
Me.CommandButton1.Visible = True
TextBox10.Value = "1"
TextBox11.Value = "1"

Else

Label10.Caption = "Sorry, that is not correct, please try again"
Me.CommandButton1.Visible = False
TextBox10.Value = "0"
TextBox11.Value = "0"

End If

End Sub

And then fianlly I have the following code which calls the next UserForm

Private Sub CommandButton1_Click()
Question_2.Show
End Sub

That's my immediate issue / question that I am seeking your help on.

Further to this, (I'm not sure if I should post the following as a new question or not - I don't want to break the Board rules)?

As mentioned above, there are potentially up to 25 questions in each training module and up to 10 modules. I am aware that things could get very "messy" if I have to create a UserForm for each Question in each Module (up to 250 UserForms!!). Is there a way that I can just use one Form (same layout for each question) to pull the next Question and Answer data in? I am thinking a Case statement would be the preffered option rather that multiple If Then Else statements, but I just can't get my head around the Case Statement.

Any help or advice on either issue would be very greatly appreciated.

Cheers, TT

 

[Legacy 456155]

At first glance, it would seem that you only need one userform. Drop a copy of your workbook in this folder if you wish or share an example in some other way.

 

[wtom0412]

Hi Dataluver,

I just dropped the file.

Cheers, TT

 

[Legacy 456155]

This should be enough to give you some ideas. There are other ways to go about this, but this is pretty simple.

Example file "to wtom0412-5536114.xlsm" in this folder.

All code goes in the userform.

Option Explicit

Private ModuleNumber As Integer
Private NumberOfQuestions As Integer
Private ListData As Range
Private CurrentQuestion As Integer

Private Sub UserForm_Initialize()
    Dim NameOfListObject As String
    
    ModuleNumber = GetModule()
    NameOfListObject = "Module" & Format(ModuleNumber, "000")
    Set ListData = Range(NameOfListObject).ListObject.DataBodyRange
    NumberOfQuestions = ListData.Rows.Count
    CurrentQuestion = 1
    LoadQuestion
End Sub

Private Function GetModule() As Integer
    'should select module using a combobox perhaps or another form
    'will just return 1 for example
    GetModule = 1
End Function

Private Sub LoadQuestion()
'    Sequence    Question    Option1 Option2 Option3 Option4 Option5 Correct Answer
    lblSequence = "Question " & CurrentQuestion
    lblQuestion = ListData.Cells(CurrentQuestion, 2)
    lbl1 = ListData.Cells(CurrentQuestion, 3)
    lbl2 = ListData.Cells(CurrentQuestion, 4)
    lbl3 = ListData.Cells(CurrentQuestion, 5)
    lbl4 = ListData.Cells(CurrentQuestion, 6)
    lbl5 = ListData.Cells(CurrentQuestion, 7)
    lblResult = "Select an option"
    cmdNext.Visible = False
End Sub

Private Sub CheckAnswer(OptionSelected As Integer)
    If OptionSelected = CorrectAnswer(OptionSelected) Then
        lblResult = "Correct, click Next to go to the next question!!"
        Controls("opt" & OptionSelected) = False
        If CurrentQuestion = NumberOfQuestions Then
            MsgBox "Contratulations.  Your final answer was correct. You're finished!"
        Else
            cmdNext.Visible = True
        End If
    Else
        lblResult = "Sorry, that is not correct, please try again"
    End If
End Sub

Private Function CorrectAnswer(OptionSelected As Integer) As Integer
    CorrectAnswer = ListData.Cells(CurrentQuestion, 8)
End Function

Private Sub cmdNext_Click()
    CurrentQuestion = CurrentQuestion + 1
    LoadQuestion
End Sub

Private Sub opt1_Click()
    CheckAnswer 1
End Sub
Private Sub opt2_Click()
    CheckAnswer 2
End Sub
Private Sub opt3_Click()
    CheckAnswer 3
End Sub
Private Sub opt4_Click()
    CheckAnswer 4
End Sub
Private Sub opt5_Click()
    CheckAnswer 5
End Sub

Table named Module001

Sequence	Question	Option1	Option2	Option3	Option4	Option5	Correct Answer
1	This is question 1	Question 1 - Option 1	Question 1 - Option 2	Question 1 - Option 3	Question 1 - Option 4	Question 1 - Option 5	2
2	This is question 2	Question 2 - Option 1	Question 2 - Option 2	Question 2 - Option 3	Question 2 - Option 4	Question 2 - Option 5	5
3	This is question 3	Question 3 - Option 1	Question 3 - Option 2	Question 3 - Option 3	Question 3 - Option 4	Question 3 - Option 5	1
4	This is question 4	Question 4 - Option 1	Question 4 - Option 2	Question 4 - Option 3	Question 4 - Option 4	Question 4 - Option 5	3
5	This is question 5	Question 5 - Option 1	Question 5 - Option 2	Question 5 - Option 3	Question 5 - Option 4	Question 5 - Option 5	1

 

[wtom0412]

Wow, thank you so much dataluver, this is amazing.

I really appreciate the time you took to help me with this, it works a treat,

Cheers, TT

 

[Legacy 456155]

Good deal. Drop a post if you need more help.