MsgBox in a macro and it prevents acton in another workbook until response received

I have a 'for loop' in a macro and when a certain error condition occurs I display a msgbox with ok or cancel buttons. I'd like my users to be able to go to another workbook to correct the issue and then press 'OK' to continue the process, but when I try that I cannot do anything in the other workbook.

Is it possible to do that or is that a limitation of Excel. This would be a 'nice to have' but not a show stopper.

Thanks for any insight you can give me.

That might have some merit, but I'd need to have some way to know when they finished making the correction in order to return to the macro's 'for loop'?

Opening the workbooks in separate instances of excel will allow you to edit one while the code is running in the other.

You might need to make some changes to your code for that to work though.

Thanks for your reply. I've never opened excel in separate instances, so looked on Google to find out how to do that. I did the following:


Google text start:

If you already have one instance of Excel open and the Excel icon is on your Windows taskbar, you can just press and hold the SHIFT key and then click on the taskbar icon and it'll open another instance.

End Google Text.

The Icon on the task bar turned an Orange Color briefly when I opened the second workbook. When I ran my macro and the msgbox displayed I went to the second workbook to try to make corrections and it was just clocking until eventually the following message appeared at top of ribbon "Reply to thread msgbox and it prevents action in another workbook until response received"

Do you know of another way to open in separate instances in excel 2016?

Thanks for your reply. I've never opened excel in separate instances, so looked on Google to find out how to do that. I did the following:


Google text start:

If you already have one instance of Excel open and the Excel icon is on your Windows taskbar, you can just press and hold the SHIFT key and then click on the taskbar icon and it'll open another instance.

End Google Text.

The Icon on the task bar turned an Orange Color briefly when I opened the second workbook. When I ran my macro and the msgbox displayed I went to the second workbook to try to make corrections and it was just clocking until eventually the following message appeared at top of ribbon "Reply to thread msgbox and it prevents action in another workbook until response received"

Do you know of another way to open in separate instances in excel 2016? I'm not sure if it was actually opened in a separate instance.

Thank you for the suggestion. I haven't heard of this option and will look on Google to see if I can figure out how to use it. Do you have any example you could show me?

I appreciate your help!!

Originally Posted by Norie

Instead of a messagebox you could use a modeless userform.

Not something I've used, but just looked it up, and if I'm reading the info correctly then this method gets my vote.

When I suggested a separate instance, I forgot that MS have made it far less simple to open one in more recent versions of excel

I agree and will pursue using the modeless userform.

Thanks for your input

Thank you and I will pursue setting up a userform. I'ver never used one and don't know at this point how to put information in the form and receive input from the user.

In my msgbox I'm putting info telling user what row of the second workbook contains the content which needs correction. Is that something that is do-able in a userform and how to display the userform when needed in the macro. Any help for this would be greatly appreciated.

Do you need to pursue it though? If it is necessary to interrupt the loop for an amendment to be made then it looks to be the preferable method to try, but is it really necessary?

As Dave has already pointed out..

Originally Posted by davesexcel

Maybe it would be better though to use the code to see if all the information is there before you start the loop

What causes the error in the procedure that requires user intervention?

Jason, One workbook contains formulas to pull stats from a tournament draw sheet where match results are entered. The first sheet is then massaged by the macro to let the operator know when scores for a match are incomplete. I would like to be able to let them go and modify the other work sheet before going thru the whole sheet just for convenience. I can work around it but I want it to be as simple as possible to use.

This workbook is for running a Table Tennis giant round robin tournament and the results sheet will be sent to USATT (U S A Table Tennis).

Thanks for your input

I have attached two worksheets which I was able to get down to a manageable size to upload. I had to eliminate some worksheets in both books.

The Tournament Register forum example.xlsm I copied the rows needed and pasted them as special values only in other rows and then eliminated all the rows with formulas which allowed me to delete other worksheets in this book to decrease size.

I did similar reductions in Tourney_E1D_forum_example.xlsm

The Macro name is "GENERATE_USATT_RESULTS" is in Tournament Register forum example.xlsm

The code in the macro does what I need it to do except for the feature I want which is to allow updating of Tourney_E1D... So if I can get more info on how to setup modeless userform with same features as my msgbox and invoke it from the macro and get results from users responses that would be greatly appreciated.

The register has scores for each match in the E1D (Drawsheet) both use 6 rows for scores. The formulas which I had to replace format scores and winner/loser positions in the file according to the format dictated by USA Table Tennis (USATT).

Thanks for your help!

I don't think that my idea of an inputbox will work too well with your workbook.

I'll leave the explanation of the userform method to those more experienced in the use of them.

Ok, This would be a nice to have but I can keep it as is until I find out more about userforms. Thanks Again for your input.

Actually I could shorten the for loop and start at row 127 but the code does match after row 126. I had previously hidden unneeded rows starting in row 6 but I made a change and am deleting unneeded rows in a different worksheet which I didn't include in the sample in order to make the example a smaller file.

If you look at row 127 you'll see where the coding matches the sheet.

Thanks for helping!