Show UserForm with Continue/Cancel buttons, allowing user to edit sheet

Morning all,

I have several large codes which run reports that are all run through a user form. Each button calls a different proceedure. One of these proceedures opens up a raw data sheet and fills in some blank cells. If any cells can't be filled in it throws up a message box to alert the user that some data needs to be added manually.

What I'm after is a message box to appear with this alert, and allow the user to either cancel the macro, edit the data necessary and re-run the macro from th beginnng (by clicking the user form button again), or 'suspend' the macro, make the edits, and click a button to continue the macro from where it left off.

After doing a lot of hunting round, I've found I can't do this from a message box as it won't let you edit a worksheet while the message box is visible, and using a user form is the better way. I've created a second userform for this scenario - simple text with two buttons - Continue and Cancel.

I can get the code to display this second userform when it finds any relevant cells, but what I'm struggling with is how to code the Continue button to jump back into the code within the first userform and resume from just after the form was called in the first place.....

Below is the snippet of code I have already tried with the messagebox function. The user form I'm trying to call is called 'NoData', and all the code is contained within the first userform code module.

Please Login or Register  to view this content.

Hi,
Some optins/ideas
1) May be you can popup InputBox to fill values you need and after this is done - go on with existing code
2) You could change existing code: move part of the code which is AFTER your data check to separate sub routine.
Then check your data, if everything is OK - Call SeparateSub, if not - popup your MsgBox about lack of data and Exit Sub.
After all data is corrected and you need to Continue - bind this SeparateSub to button Continue and rest of code will do what you need to finish.

Keebellah's suggestion might work for you, I think.
You CAN display userform and allow user to edit workbook. You need to set "ShowModal" property to False (Under UserForm properties).

Yes but that's not the part I'm struggling with... I can get the userform to display and I can set it to modal so they can edit whatever they need. This I can do/have done. It's the part where they click continue to resume that I'm struggling with...

I can't create a sample due to the size/complexity of the whole file - creating the sample would take as long as it would to split the code out into several parts and duplicate it into the 2nd userform... The code I have above is triggered from the original button on UserForm1 (stored in code sheet for userform1), and manipulates data on a seperate file - 'mybook'. IF it finds any cells in col I that contain 'no data' it displays the message box (to be replaced by the userform). If none of these cells are found it simply completes the remainder of the macro (mainly saving the file as specific file names). If the messagebox/2nd userform is triggered, it does have two buttons in order to trigger the next steps - 'Continue' to resume the original macro, and 'Cancel' to exit the origional macro. All original macros are stored on the 1st userform code sheet/module.

Sorry, I don't feel I'm able to explain this any better Best I can do is upload a flow chart of what I want if that would help.

Test this solution with UserForm.
Main task is to copy values from column A to column C, but this can be done only if there are no empty cells in column A.
Start from UserForm at the beginning, press Start button (CommandButton3) and code will check if there are any empty cells - if empty cell is found then execution of code is interrupted.
After you correct data you can press Resume, but actually this will run the same CommandButton3_Click code, because you never know if all data was corrected- so we need to check it again, right?
In case there are no empty cells then Main action will be processed and code will finish work. In case you will press Cancel button - UserForm will be unloaded and nothing will happen.
All you need to do is to put your "check data" code into UserForm code, this part is not so "heavy" as I can see in your code from post #1

Code for UserForm:

Please Login or Register  to view this content.

Sorrt Keebellah, I have been off work for a while, hence not responding. I have had a play with your file (thank you for your time with this), but I don't think it'll work how I want, and keep the initial userform clear of potentially unnecessary extra buttons. Splitting/duplicating the code out into the 2nd userform may be the best way for this to work, although it's not ideal if I need to update the code in future, I'll have to ensure the changes are replicated for both forms.

Thanks again for your time and help - feel I'll have to ditch the idea for now and may revisit it if/when the entire file gets a revamp.

Sorrt Keebellah, I have been off work for a while, hence not responding. I have had a play with your file (thank you for your time with this), but I don't think it'll work how I want, and keep the initial userform clear of potentially unnecessary extra buttons. Splitting/duplicating the code out into the 2nd userform may be the best way for this to work, although it's not ideal if I need to update the code in future, I'll have to ensure the changes are replicated for both forms.

Thanks again for your time and help - feel I'll have to ditch the idea for now and may revisit it if/when the entire file gets a revamp.