How to pause macro while user enters a value in a cell and then unpause macro-CodePudding

I want to write an educational macro that works like this:

The macro writes a question in A1 like "Please write the value of 5 plus 6 in cell B1".

The user then writes 11 in cell B1, and clicks a button.

The macro responds with "Correct" in cell C1 and writes the next question in A2.

In this way, the spreadsheet shows all the past questions and answers.

Is it possible to do this - to have the macro pause while the user fills in some text and then a button unpause it so it continues from where it left off?

Many thanks!

CodePudding user response：

If I understand you correctly and if you don't mind using a UserForm, then maybe you can try to have something like this :

Prepare the question and the answer in Sheet2 something like the image above.
You may want to have all the font as white, protect the sheet and hide it.

Then the Sheet1 as where the test is, which something like below animation:

Create a UserForm
Make one button on it and one TextBox on it.

The sub:

Dim oFill As Range: Dim cnt As Long:
Dim rgQdata As Range: Dim rgQ As Range

Private Sub UserForm_Initialize()
CommandButton1.Caption = "NEXT"
Set rgQdata = Sheets("Sheet2").Range("A2")
Set rgQ = ActiveSheet.Range("A2")
rgQ.Value = rgQdata.Value
End Sub

Private Sub TextBox1_Change()
rgQ.Offset(0, 1).Value = TextBox1.Value
End Sub

Private Sub CommandButton1_Click()

    If rgQ.Offset(0, 1) = rgQdata.Offset(0, 1) Then
        rgQ.Offset(0, 2).Value = "correct"
    Else
        rgQ.Offset(0, 2).Value = "incorrect"
    End If

TextBox1.SetFocus
Set rgQdata = rgQdata.Offset(1, 0)

    If rgQdata.Value <> "" Then
        Set rgQ = rgQ.Offset(1, 0)
        rgQ.Value = rgQdata.Value
        TextBox1.Value = ""
    Else
        Unload Me
    End If

End Sub

The Initialize will make a range for the first question in the cell of the data (Sheet2) as rgQdata variable and a range for the first question asked as rgQvariable. Then it put the rgQ value from the rgQdata value.

When the user type the answer in the TextBox1,
it will fill rgQ.offset(0,1) with what he type.

The user need to click the NEXT button to see his result.

When the user click the NEXT button, the sub check if the rgQ.Offset(0, 1) value is the same with the rgQdata.Offset(0, 1) value. If same then put "correct" if not same then put "incorrect" in rgQ.offset(0,2) cell.

Then it put the next question by offsetting one cell down of both rgQdata and rgQ. It also check if there is no value in rgQdata then it unload the userform.