Solved: How to pause a macro

[marshybid]

Hi All,

Can anyone please tell me how I can pause a macro, allowing me to access the spreadsheet and make some manual changes, then re-continue the rest of the macro.

Thanks,

Marshybid

[Bob Phillips]

What sort of changes?

[marshybid]

Hi xld,

One of the columns already calculated as the first part of the macro will require some values to be manually changed based on data provided seperately befor the rest of the macr is run.

Unfortunately these new values change weekly and can not be simply written in as part of the whole macro.

I need the macro to pause at a certain point, allow me to make the manual changes in the spreadsheet then continue (I assume I will have to click something to cause it to continue)

Or.... Is it easier to split out the macro into 2??? One to do the first part and stop, then run the rest as second macro??

Thanks,

Marshybid

[Bob Phillips]

I would go fo thr latter.

You could throw up an Application.Inputbox to get ranges and another to get values, maybe in a lop until the user quits, and apply those values, but the other is simpler.

[Ago]

i used this way in one of my workbooks.
the workbook is supposed to be shown on a projector and have stops to explain whats going on.


Function pause()

Range("A1").Value = "."
Range("A1").Select
While Range("A1").Value = "."
DoEvents
Wend


End Function


all you need to do is to type 'call pause' where you need one in the macro and paste this code in your module.
all you need to do to start the macro again is to select A1 and press delete

[marshybid]

I will stick with splitting the macro into the two component parts for now. However for future, is there no way of creating a pause point in a mcro then assigning a key stroke to commence from the pause point??

Thanks xld, did you get a chance to look at the query re: tidying up the code and speeding it up??

Marshybid

[marshybid]

Thanks Ago, I replied to xld before I saw your suggestion.

I will try that.

Marshybid

[Ago]

this could work aswell. it sometimes works for me and sometimes not.
the only time my code would get to these lines if something goes wrong thats why i dont really know if it works or not.



pause = 123
Debug.Assert (pause < 100)

[marshybid]

Ago, the pause function works great. I just changed the cell reference to an appropriate empty cell.

Had to remember to place the application.screenupdating = false elsewhere in my macro, otherwise the spreadsheet doesn't show.... DOH!!!

Great help.

Marshybid