Stop macro when Key is pressed

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,368
Hello all,

I have a macro that selects a random number and writes it to a cell.

Code:
Sub do_it()
Do
'when space bar is pressed then exit sub
Range("A1") = Int((10 - 1 + 1) * Rnd + 1)  'random number between 1 and 10
Loop
End Sub

Anyone know how to get it to stop when the user presses the space bar?

Tia,

Ross
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
The part about stopping the code by pressing the spacebar is working.

Code:
Do
    Calculate
    DoEvents
    GetKeyboardState kbArray
    If kbArray.kbb(32) And 128 Then
        Application.Cursor = xlNormal
        goto continue
    End If
Loop

continue:
msgbox ("Number Selected")
Range("M5") = Range("A1")

End Sub

The problem I have now is that a space has been entered in my active cell and the rest of my code does not seem to be working. I get the message box, but my cursor seems to be stuck in the active cell.

Thanks,

Ross
 
Upvote 0
I tried it like this

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
        MsgBox ("Number Selected")
        Range("M5") = Range("A1")
        Exit Sub
    End If
    Range("A1") = Int((10 - 1 + 1) * Rnd + 1)
Loop
End Sub
 
Upvote 0
I copied the above code into a new workbook.

When I run it I see the random numbers populating in Cell A1.
When I press thew space bar the randomized stops and I get the message box. But I do NOT get the random number from A1 to populate into M5
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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