Calculate Now function until button press

[rikapple]

Hi

For Xmas we are running a competition, basically a number drawn out of a hat. As this will be on teams I wanted to create some pazzaz on screen, I have added all the employee's names and crated a random function which works well every time I select the calculate now button,

What I wanted to do was have the calculate now continuing until a button is pressed. Possibly with .5second gap between the process. To give the effect the names are scrolling through. Then when button is pressed the Calculate now process stops.

Hope this is clear, any help is much apprechiated.

Stephanie

 

[offthelip]

Here is some code which hopefully does what you want, I created two buttons on the sheet one to start and one to stop which call the relevant macros Startrun and Stoprun. This code goes in a standard module. My code just increaments F4, I suggest you just call you randomising macro instead of that code:

Public Stopflag As Boolean
Sub runnum()
Do While Not (Stopflag)
 Range("F4") = Range("f4") + 1
 If Range("f4") > 2000 Then Range("F4") = 0
 ' call your subroutine here instead of the last two lines of code
 DoEvents
Loop

End Sub

Sub Stoprun()
 Stopflag = True
End Sub
Sub Startrun()
Stopflag = False
Call runnum
End Sub

 

[rikapple]

Here is some code which hopefully does what you want, I created two buttons on the sheet one to start and one to stop which call the relevant macros Startrun and Stoprun. This code goes in a standard module. My code just increaments F4, I suggest you just call you randomising macro instead of that code:

Public Stopflag As Boolean
Sub runnum()
Do While Not (Stopflag)
 Range("F4") = Range("f4") + 1
 If Range("f4") > 2000 Then Range("F4") = 0
 ' call your subroutine here instead of the last two lines of code
 DoEvents
Loop

End Sub

Sub Stoprun()
 Stopflag = True
End Sub
Sub Startrun()
Stopflag = False
Call runnum
End Sub

Thank you so much for this, works exactly as needed, saved a lot of head scratching, thank you again!

 

[rikapple]

Hi sorry I have this working, as in it will spin the numbers around as requested, the issue is I cannot get the action to stop, I am not very good with Macros! All I want is for the action to stop once a button is pressed. I have added a demo to explain. Again very much for the actions so far!

 

[offthelip]

Please post the code that you have actually got here , because running the Stoprun macro from a button should stop it, but it does depend on exactly what code you have got in your macro