Suspend/resume running of a macro
 

Hi all
Any help would be greatly appreciated.

Is there a simple way of allowing the user to pause (suspend) a macro and
then allow it to resume, say, on <Enter key?

Platform: Excel 2000

Background: I have written an event timer (countdown in minutes) for each
round in a bridge competition and occasionally need to manually pause it for
a minute or so if there is a hold up in play. (The timer is a guide only -
does not need to be 100% accurate).
I can use Ctrl-Break and then resume, but this is rather clumsy and
unprofessional.

I did a Google search and it seems that this simple request is NOT possible!
However maybe the experts have now found a way!

If not possible please just say so and I will continue using Ctrl-Break.
Yuk!

Thanks,
Peter Bircher

Suspend/resume running of a macro
 

You can use a custom error handler for Ctrl-Break (or Esc)

Application.EnableCancelKey

Sub noesc()
'set it up so if user hits esc, you send it to error handler
Application.EnableCancelKey = xlErrorHandler
On Error GoTo 1
While True
ActiveCell.Offset(1).Select 'press esc here
Wend
Exit Sub
1:
MsgBox "You pressed esc"
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Peter" wrote in message
...
Hi all
Any help would be greatly appreciated.

Is there a simple way of allowing the user to pause (suspend) a macro and
then allow it to resume, say, on <Enter key?

Platform: Excel 2000

Background: I have written an event timer (countdown in minutes) for each
round in a bridge competition and occasionally need to manually pause it
for
a minute or so if there is a hold up in play. (The timer is a guide only -
does not need to be 100% accurate).
I can use Ctrl-Break and then resume, but this is rather clumsy and
unprofessional.

I did a Google search and it seems that this simple request is NOT
possible!
However maybe the experts have now found a way!

If not possible please just say so and I will continue using Ctrl-Break.
Yuk!

Thanks,
Peter Bircher

Suspend/resume running of a macro
 

Thanks Rob
That would be a tad more elegant than the Ctrl-Break into my code.
Peter

"Rob van Gelder" wrote in message
...
You can use a custom error handler for Ctrl-Break (or Esc)

Application.EnableCancelKey

Sub noesc()
'set it up so if user hits esc, you send it to error handler
Application.EnableCancelKey = xlErrorHandler
On Error GoTo 1
While True
ActiveCell.Offset(1).Select 'press esc here
Wend
Exit Sub
1:
MsgBox "You pressed esc"
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Peter" wrote in message
...
Hi all
Any help would be greatly appreciated.

Is there a simple way of allowing the user to pause (suspend) a macro
and
then allow it to resume, say, on <Enter key?

Platform: Excel 2000

Background: I have written an event timer (countdown in minutes) for
each
round in a bridge competition and occasionally need to manually pause it
for
a minute or so if there is a hold up in play. (The timer is a guide
only -
does not need to be 100% accurate).
I can use Ctrl-Break and then resume, but this is rather clumsy and
unprofessional.

I did a Google search and it seems that this simple request is NOT
possible!
However maybe the experts have now found a way!

If not possible please just say so and I will continue using Ctrl-Break.
Yuk!

Thanks,
Peter Bircher

Suspend/resume running of a macro
 

Why not just have your countdown timer check a value in a cell and stop
counting (continue to run) when it finds a certain condition.

The best solution would depend on how you have your timer running
(continuous loop or application.ontime), but I find it hard to believe that
this has been a major challenge for the "experts".

--
Regards,
Tom Ogilvy


"Peter" wrote in message
...
Thanks Rob
That would be a tad more elegant than the Ctrl-Break into my code.
Peter

"Rob van Gelder" wrote in message
...
You can use a custom error handler for Ctrl-Break (or Esc)

Application.EnableCancelKey

Sub noesc()
'set it up so if user hits esc, you send it to error handler
Application.EnableCancelKey = xlErrorHandler
On Error GoTo 1
While True
ActiveCell.Offset(1).Select 'press esc here
Wend
Exit Sub
1:
MsgBox "You pressed esc"
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Peter" wrote in message
...
Hi all
Any help would be greatly appreciated.

Is there a simple way of allowing the user to pause (suspend) a macro
and
then allow it to resume, say, on <Enter key?

Platform: Excel 2000

Background: I have written an event timer (countdown in minutes) for
each
round in a bridge competition and occasionally need to manually pause
it
for
a minute or so if there is a hold up in play. (The timer is a guide
only -
does not need to be 100% accurate).
I can use Ctrl-Break and then resume, but this is rather clumsy and
unprofessional.

I did a Google search and it seems that this simple request is NOT
possible!
However maybe the experts have now found a way!

If not possible please just say so and I will continue using
Ctrl-Break.
Yuk!

Thanks,
Peter Bircher