Stop macro when Key is pressed

[rpaulson]

Hello all,

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

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

 

[dave3009]

Try having a look here

http://www.mrexcel.com/forum/showthread.php?t=350685

 

[rpaulson]

Dave,

Thanks, Ill check it out later and report back.

 

[rpaulson]

The part about stopping the code by pressing the spacebar is working.

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

 

[dave3009]

I tried it like this

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

 

[rpaulson]

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