UserForm.show issue

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
168
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)....

VBA Code:
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)

Code:
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

Code:
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
L

Legacy 456155

Guest
o_O
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.
 
L

Legacy 456155

Guest
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.
VBA Code:
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
SequenceQuestionOption1Option2Option3Option4Option5Correct Answer
1This is question 1Question 1 - Option 1Question 1 - Option 2Question 1 - Option 3Question 1 - Option 4Question 1 - Option 52
2This is question 2Question 2 - Option 1Question 2 - Option 2Question 2 - Option 3Question 2 - Option 4Question 2 - Option 55
3This is question 3Question 3 - Option 1Question 3 - Option 2Question 3 - Option 3Question 3 - Option 4Question 3 - Option 51
4This is question 4Question 4 - Option 1Question 4 - Option 2Question 4 - Option 3Question 4 - Option 4Question 4 - Option 53
5This is question 5Question 5 - Option 1Question 5 - Option 2Question 5 - Option 3Question 5 - Option 4Question 5 - Option 51
 

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
168
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,295
Messages
5,571,394
Members
412,386
Latest member
Yasaman
Top