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...

Please Login or Register  to view this content.

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.

[snb]

I don't want an InputBox cluttering up my spreadsheet.

The only thing an inputbox can't ever do is cluttering up your spreadsheet. A user (or your VBA-code) can.

Please Login or Register  to view this content.

If you want to control both the user's input and your spreadsheet you'd better use a Userform.

[OldTim]

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.

Sorry for any frustration/or controversy I might be causing. It is not intentional. I am writing a math trainer for my grandson and am in the earliest stages.

The plan is to have two numbers generated randomly and displayed one above the other in a column. The student would then enter their answer in the cell below the question. Evaluation of the answer will generate a response ie. "Yes" or "No".

Simple enough I know, however, I would prefer that the student not have to re-launch the macro after each entry or have to manually select the answer cell to respond to subsequent questions.

I would like to evaluate the answer from within the macro, respond to it, generate new numbers and await a response. Hence my search for a method to suspend a macro, await input into a cell then resume the macro.

My code so far is embarrasingly simple but I will post it if you think it will help. I would really like to learn more about the worksheet change event however.

Thanks again

[shg]

You don't need a macro for that.

If A1 contains 3, A2 contains 4, and A3 is supposed to be the sum, then in A4,

=if(a3="", "", if(a3=a1+a2, "Correct!", "Sorry , no!"))

[OldTim]

Thanks once again. I already have this solution implemented. If, however, I do learn how to suspend a macro, relinquish focus to the spreadsheet then resume after data input I will post it for all to see. I'am thinking that what I seek is not doable in VB.

[shg]

As I said before, the macro can exit and wait for user input, as detected by the Worksheet Change event. See, for example, http://www.mvps.org/dmcritchie/excel/event.htm.

Please post a link to other forums where you have posted the same question, and I strongly suggest you post a link in those other forums as well.

[Cbrehm]

Thanks once again. I already have this solution implemented. If, however, I do learn how to suspend a macro, relinquish focus to the spreadsheet then resume after data input I will post it for all to see. I'am thinking that what I seek is not doable in VB.

Explore this code attached. Not finished but should give you ideas. Pay attention to the code on sheet1.

[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.

[OldTim]

Explore this code attached. Not finished but should give you ideas. Pay attention to the code on sheet1.

Thanks. This has possibilities.

[OldTim]

As I said before, the macro can exit and wait for user input, as detected by the Worksheet Change event. See, for example, http://www.mvps.org/dmcritchie/excel/event.htm.

Please post a link to other forums where you have posted the same question, and I strongly suggest you post a link in those other forums as well.

I have only posted to one other forum. How do I post the links you urge?

[shg]

Copy the URL for that thread from the browser address bar and post it here; copy the URL for this thread and post it there.

[OldTim]

Copy the URL for that thread from the browser address bar and post it here; copy the URL for this thread and post it there.

I have terminated my post at excel help. I like it better here. Regards

[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