Sub CommandButton2_MouseDown()
For i = 1 To 20
Sheets("Plan1").Range("a1").Value = Sheets("Plan1").Range("a1").Value +
1
For j = 1 To 2000
Application.Calculate
Next j
Next i
End Sub
The "j-loop" (For j = 1 To 1000) is only there to slow down the
execution.
Thanks all.
Have a nice sunday.
Stoffer Krol
--
skrol
------------------------------------------------------------------------
skrol's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27126
View this thread: http://www.excelforum.com/showthread.php?threadid=476586
Private Declare Function GetInputState Lib "user32" () As Long
'in your loop
If GetAsyncKeyState(&H1) Then
' left button is down
Else
' exit code
End If
Regards,
Peter T
"skrol" <skrol.1wzmac_1...@excelforum-nospam.com> wrote in
message news:skrol.1wzmac_1...@excelforum-nospam.com...
You'll need a module level variable so that MouseDown & MouseUp event
handlers can share the same variable. You'll also need the DoEvents function
somewhere inside MouseDown's loop to allow the operating system to process
the MouseUp event. The following example should get you going.
Regards,
Vic Eldridge
Dim MouseIsDown As Boolean
Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
MouseIsDown = True
Do While MouseIsDown
Range("A1") = Range("A1") + 1
DoEvents
Loop
End Sub
Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
MouseIsDown = False
End Sub
But I wanted things going by operating a CommandButton on the sheet.
I'm wrestling with it for almost 1 day, but I can't get it.
:confused: :confused: :confused: :confused:
Maybe someone knows the (simple I hope) solution.
Afraid I gave you completely the wrong API, not very helpful!
Both Vic's and my (corrected) suggestions should work same way with
Worksheet CommandButtoms.
Put two on a sheet, named CommandButton1 & CommandButton2, and paste
following into the sheet module
Private Declare Function GetAsyncKeyState Lib "User32" _
(ByVal vKey As Long) As Long
Dim MouseIsDown As Boolean
Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
MouseIsDown = True
Do While MouseIsDown
Range("A1") = Range("A1") + 1
DoEvents
Loop
End Sub
Private Sub CommandButton1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
MouseIsDown = False
End Sub
Private Sub CommandButton2_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
DoEvents ' to depress the button
Do While GetAsyncKeyState(&H1)
Range("A2") = Range("A2") + 1
Loop
End Sub
Take your pick!
Regards,
Peter T
"skrol" <skrol.1x466g_1...@excelforum-nospam.com> wrote in
message news:skrol.1x466g_1...@excelforum-nospam.com...
I prefer the second option, stopping the loop on releasing the button.
But your solution is also verey usefull.
Thanks again.
Glad you've got it working.
> I prefer the second option, stopping the loop on releasing the button.
> But your solution is also verey usefull.
I'm a bit confused though -
Both methods should stop the loop when the button is released, are you
saying one of the methods didn't?
My solution was the second of the two examples I posted, if you mean the API
method.
Regards,
Peter T
Your solution (button 2) counts more than 3 times faster as button 1
(Vic) does.
But both are very usefull to help me understand what things do and how
things happen.
Calling an API is always going to be faster than DoEvents. However that
doesn't necessarily preclude using it. The test loop is artificial, in a
real life scenario you wouldn't use DoEvents in every increment. Instead
call it somewhere between every say 0.2 & 0.01 sec's in one of the outer
loops.
As the test did not have an outer loop you could use a counter, try
replacing the first of the three routines with this:
Private Sub CommandButton1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim n As Long
MouseIsDown = True
Do While MouseIsDown
Range("A1") = Range("A1") + 1
n = n + 1
If n = 100 Then
DoEvents
n = 0
End If
Loop
End Sub
With the counter in DoEvents I didn't notice much difference between the two
methods. But I would also use a counter with the API method or place in an
appropriate outer loop.
Choice of method would depend on other things, eg you might well want to
enable other events to be processed during the loop. Then again that might
be the opposite of what you want to occur.
Regards,
Peter T
"skrol" <skrol.1x6y6c_1...@excelforum-nospam.com> wrote in
message news:skrol.1x6y6c_1...@excelforum-nospam.com...
And as if you were reading my mind: -"eg you might well want to
enable other events to be processed during the loop."-
That will be my next (giant) step this weekend.
Have a nice weekend.
Stoffer Krol
:) ----- :cool: