How to read keyboard buffer while macro is endless looping. Please see sample below.

[SARA]

I have a macro doing endless calculation in a loop,
only a key is press will stop the marco and print the result.
How can I do in ** ?

sub sample()
x=1
loop:
x=x+1


**[[ if a key is pressed, goto key_press ]]**


goto loop
key_press:
cells(1,1)=x
end sub

 

[Frank Kabel]

Hi
try the following (execution stops with the 'ESC' key)

Sub sample()
Dim x
'set it up so if user hits esc, you send it to error handler
Application.EnableCancelKey = xlErrorHandler
On Error GoTo 1
While True
x = x + 1
Wend
Exit Sub
1:
MsgBox "You have pressed esc. Value is: " & x
End Sub

 

[Stephen Bullen]

Hi Sara,

I have a macro doing endless calculation in a loop,
only a key is press will stop the marco and print the result.
How can I do in ** ?

See the checkkey example on the Excel page of my web site.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[SARA]

Hi
try the following (execution stops with the 'ESC' key)

Sub sample()
Dim x
'set it up so if user hits esc, you send it to error handler
Application.EnableCancelKey = xlErrorHandler
On Error GoTo 1
While True
x = x + 1
Wend
Exit Sub
1:
MsgBox "You have pressed esc. Value is: " & x
End Sub

It work! thank you very much!

SARA

 

[SARA]

Hi Sara,


See the checkkey example on the Excel page of my web site.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

you have done a great job,
that is what I need,
thank you very much!!
SARA