[mariaa33]

I am trying to find the best way to pause a macro, allow the user to edit the spreadsheet in various cells and then resume the macro. I saw on one site about modeless msgbox but could not find information on how to use it. Any suggestions would be greatly appreciated.

[slurpee55]

One of the simpler ways is to add this pause routine to the macro
http://www.mrexcel.com/archive/VBA/869.html

[mariaa33]

That would not work because it would take X minutes to make the changes. The number of minutes would vary and I would not want to put in too long of a wait or too short of a wait.

[slurpee55]

Then I would suggest you use an Input Box.
Here is a good description on how to use them
http://www.ozgrid.com/VBA/inputbox.htm

[mariaa33]

Already tried that too but it does not allow the user to go to the actual spreadsheet and enter the data as there will be different cells that need to be entered each time it is used. It needs to just pause the macro and allow the user to enter data whereever it is need and when finished the user click "ok" to continue with the macro.

[slurpee55]

What does your macro do? I am trying to understand why the user can't make the changes in advance, or have formulas recalculate the values.
If you could post a copy of your spreadsheet (Use Go Advanced - there is an option that you will see then for adding files) then perhaps we could help more.

[mariaa33]

The spreadsheet is very large and after other formatting, it sorts the data so that the rows with the empty cells are at the top. The cells that are empty require the user to look up data in another application and then enter it into the spreadsheet. It could be anywhere from 1 to 30 different cells that manual entry is needed. After they complete this manual entry I want to continue with the macro.

[slurpee55]

Why not just split it into two macros - one that stops after sorting and another that does the rest?
You could assign two buttons that would run one macro each....

[mariaa33]

That is the way I had it to start with but I was trying to keep the user from having to run several macros. Ok, that may be my only choice. I was looking into using a userform with an ok button. thanks

[OBP]

It sounds like this should actually be in Access, an Access Query would show empty Cells and allow data entry.

[slurpee55]

I asked one of the best coders on here about this and he said that you could do it in BASIC but not in VBA. (I would guess you could program in something else also, but....)
An idea I thought of - if there is a cell that they always must enter data into, and it is the last one that they enter data into (or click into) - you could even assign a blank cell to this function; have them type in "ok" or something - but when that cell loses focus, you could auto start the second macro.

[Rollin_Again]

Create a modeless form with nothing but an OK button on it. When the user runs the macro the code will execute up to the point where the user input is required and then the form will be displayed. Since the form is modeless you can move it and manually enter data into the workbook. When the user clicks the OK button the form should be hidden and the remainder of your macro code should be executed in the button_click event. To make the form modeless just open the form in the VB editor and set the ShowModal property to False. Let me know if you want me to post an example.


Regards,
Rollin

[mariaa33]

How would I code that? I have never worked with modeless forms. I have tried to figure it out with some examples on the net but no success.

[mariaa33]

Unfortunately, my company has blocked that website. They even block google groups.

[mariaa33]

Wait, I know somewhere I can go. Be there in a min