Stopping a macro with a keystroke

K

KW

Board Regular
Joined
Jan 25, 2005
Messages
167
I'm sure there is a simple way of achieving this, but I'm struggling with it.

I have a simple macro as follows that starts a lottery draw:

Sub StartLotteryDraw()

Do
Calculate
Loop

End Sub

I want to be able to press the space bar to stop the macro which in effect is constantly re-calculating.

Any help would be appreciated.

Regards

KW
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi

Yes, but I would like to be able to press a single key (not necessarily the space bar, but preferable). All Ctrl + Break does is give the interupt message box.

hanks

KW
 
Upvote 0
HI,

Why do you want to stop the macro...aside from the obvious fact that it will run forever. Is it just arbitrary (I'm tired of this macro) or is there some other event that makes it "enough" (I've got all the lottery draws I need now).

It's not a simple solution I guess - you need to send an interrupt to the program and as far as I know Ctrl + Break is the only simple way to do this. If you protect the project with a password I think Ctrl + Break would not allow you to see the code which is a little better anyway - its not exactly a pretty message but you click OK and you've achieved your goal.

--Alex.
 
Upvote 0
Try

Code: 
Type KeyboardBytes
    kbb(0 To 255) As Byte
End Type

Declare Function GetKeyboardState Lib "User32.DLL" (kbArray As KeyboardBytes) As Long

Sub StartLotteryDraw()
Dim kbArray As KeyboardBytes
Application.Cursor = xlWait
Do
    Calculate
    DoEvents
    GetKeyboardState kbArray
    If kbArray.kbb(32) And 128 Then
        Application.Cursor = xlNormal
        Exit Sub
    End If
Loop
End Sub
 
Upvote 0
Nice! I figured there may be an API call for this but how did you find it?...
 
Upvote 0
Ahh...yes, I've seen you recommend this before...it must be quite a treasure trove!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Toggle Button to Start and Stop Macros
2
Replies
12
Views
1K
offthelip
O
K
  • Question
Pause a macro when the user starts entering a number in the active cell & resume it when they hit the Enter key
Replies
3
Views
947
Jaafar Tribak
Jaafar Tribak
D
  • Solved
Macro that won't run Trying to use DoWhile Loop and Offset to compare cells
Replies
8
Views
211
Micron
M
E
  • Solved
Excel VBA Macro for Cell Formatting
Replies
6
Views
317
kevin9999
K
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
125
Akuini
Akuini

Forum statistics

Threads
1,214,983
Messages
6,122,583
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top