Suspending a macro

[OldTim]

Has anyone discovered a way to suspend a running macro to allow input to a cell on the active worksheet? Upon entry control would be returned to the macro for manipulation of the entered value.

While not a macro per se the worksheet_change event works for me. This has been more than a simple math trainer for my grandson but a journey of discovery for me. Thank you all for your help. The basic code that I can build upon follows...

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Answer = Range("G22").Value
    Range("G22").Select
    If Answer <> "" Then
        If Answer = MyTotal Then
            Range("G18").Value = "GREAT JOB"
        Else
            Range("G18").Value = "SORRY, TRY AGAIN"
            Application.EnableEvents = True
            Exit Sub
         End If
     End If
     MyTotal = 0
     For Each c In Worksheets("Sheet1").Range("G20:G21")
        MyValue = Int((15 * Rnd) + 1)   ' Generate random values between 1 and 15
        c.Value = MyValue
        MyTotal = MyTotal + MyValue
    Next c
    Worksheets("Sheet1").Range("G22").ClearContents
     Application.EnableEvents = True
End Sub

Oh! In my zeal to get an answer I mistakenly cross posted to another forum without proper notice. (rtfms). I bear the cross of my 5 demerit points with suitable shame. Thanks again all.

[shg]

Welcome to the forum.

See the InputBox function.

[OldTim]

Welcome to the forum.

See the InputBox function.

Thanks shg. I am looking for a different approach if at all possible in VB. I don't want an InputBox cluttering up my spreadsheet. Any other ideas?

[shg]

You can use a MsgBox to prompt the user to make an entry.

[OldTim]

You can use a MsgBox to prompt the user to make an entry.

more clutter on my spreadsheet As far as I can tell MsgBox uses canned responses and does not allow entry of the value that I would otherwise enter on the spreadsheet. I was hoping to find a subtler solution. I really appreciate you taking the time to respond to my post. Thanks

[shg]

As is becoming apparent, I don't know what you're trying to do.

You can select a cell, put a prompt in it, exit the sub, and wait for the worksheet change event to respond.

In your next post, perhaps you can explain what you want instead of saying that my guesses aren't it.

[TryToExcel]

OldTim,

This seems like a simple mathematical addition. Can you not have a "If" function in an adjacent cell (or any cell that can be seen when entering your answer) to display the response?

e.g. If ( If A1 Or A2 = "", "", If ( A1 + A2 = A3, "Correct", Sorry, No") )

Danny

[TryToExcel]

I boobooed on my "If" function. Here's the correct function.

e.g. If ( A1 Or A2 = "", "", If ( A1 + A2 = A3, "Correct", Sorry, No") )

[shg]

That's invalid syntax for what you're trying to do.

[TryToExcel]

Thanks shg,

What was I thinking?

Proper syntax is "=IF(OR(A1="",A2=""),"",IF(A1+A2=A3,"Correct","Sorry, No"))"

Another Homer Simpson moment.

Danny