Pause a macro after each line, run next after hitting spacebar?

[manxman]

I want the following macro to stop after each line and wait for me to hit the
spacebar before entering the next line. What code do I need to add?

Sub Optimize()
'
' Optimize Macro
' Run through series of percentages
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Application.Run "PERSONAL.XLS!MoveCursorNot"
ActiveCell.FormulaR1C1 = "0.0001"
ActiveCell.FormulaR1C1 = "0.0002"
ActiveCell.FormulaR1C1 = "0.0003"
ActiveCell.FormulaR1C1 = "0.0004"
ActiveCell.FormulaR1C1 = "0.0005"
End Sub

[Chip Pearson]

There is no way to pause a macro and wait for a keypress. The
only thing you can do is display a MsgBox after each line of code
and let the user click OK.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"manxman" <manxman@discussions.microsoft.com> wrote in message
news:8C9AF189-26A7-43C7-92C3-E439A3F5B641@microsoft.com...
>I want the following macro to stop after each line and wait for
>me to hit the
> spacebar before entering the next line. What code do I need to
> add?
>
> Sub Optimize()
> '
> ' Optimize Macro
> ' Run through series of percentages
> '
> ' Keyboard Shortcut: Ctrl+Shift+O
> '
> Application.Run "PERSONAL.XLS!MoveCursorNot"
> ActiveCell.FormulaR1C1 = "0.0001"
> ActiveCell.FormulaR1C1 = "0.0002"
> ActiveCell.FormulaR1C1 = "0.0003"
> ActiveCell.FormulaR1C1 = "0.0004"
> ActiveCell.FormulaR1C1 = "0.0005"
> End Sub

[Andrew Taylor]

Of course, hitting the space bar clicks the OK button,
so this does do what the OP wants

Chip Pearson wrote:
> There is no way to pause a macro and wait for a keypress. The
> only thing you can do is display a MsgBox after each line of code
> and let the user click OK.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "manxman" <manxman@discussions.microsoft.com> wrote in message
> news:8C9AF189-26A7-43C7-92C3-E439A3F5B641@microsoft.com...
> >I want the following macro to stop after each line and wait for
> >me to hit the
> > spacebar before entering the next line. What code do I need to
> > add?
> >
> > Sub Optimize()
> > '
> > ' Optimize Macro
> > ' Run through series of percentages
> > '
> > ' Keyboard Shortcut: Ctrl+Shift+O
> > '
> > Application.Run "PERSONAL.XLS!MoveCursorNot"
> > ActiveCell.FormulaR1C1 = "0.0001"
> > ActiveCell.FormulaR1C1 = "0.0002"
> > ActiveCell.FormulaR1C1 = "0.0003"
> > ActiveCell.FormulaR1C1 = "0.0004"
> > ActiveCell.FormulaR1C1 = "0.0005"
> > End Sub

[manxman]

That sounds like it would be fine. How do you suggest I proceed to make this
as simple as possible? Thanks very much for your help.

"Chip Pearson" wrote:

> There is no way to pause a macro and wait for a keypress. The
> only thing you can do is display a MsgBox after each line of code
> and let the user click OK.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "manxman" <manxman@discussions.microsoft.com> wrote in message
> news:8C9AF189-26A7-43C7-92C3-E439A3F5B641@microsoft.com...
> >I want the following macro to stop after each line and wait for
> >me to hit the
> > spacebar before entering the next line. What code do I need to
> > add?
> >
> > Sub Optimize()
> > '
> > ' Optimize Macro
> > ' Run through series of percentages
> > '
> > ' Keyboard Shortcut: Ctrl+Shift+O
> > '
> > Application.Run "PERSONAL.XLS!MoveCursorNot"
> > ActiveCell.FormulaR1C1 = "0.0001"
> > ActiveCell.FormulaR1C1 = "0.0002"
> > ActiveCell.FormulaR1C1 = "0.0003"
> > ActiveCell.FormulaR1C1 = "0.0004"
> > ActiveCell.FormulaR1C1 = "0.0005"
> > End Sub
>
>
>