MouseDown()-MouseUp()

In one of my sheets I have button, starting a macro wih "MouseDown()".
This macro starts a simple loop.
No problem.
But what I want is that when I release the mouse button, the macto stops before ending the loop. Now it goes till the end.
Now it's something like this:

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

Maybe check the state of the mouse periodically during your loop and exit

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_1129453506.5726@excelforum-nospam.com> wrote in
message news:skrol.1wzmac_1129453506.5726@excelforum-nospam.com...
>
> In one of my sheets I have button, starting a macro wih "MouseDown()".
> This macro starts a simple loop.
> No problem.
> But what I want is that when I release the mouse button, the macto
> stops before ending the loop. Now it goes till the end.
> Now it's something like this:
>
> 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...o&userid=27126
> View this thread: http://www.excelforum.com/showthread...hreadid=476586
>

Hi Stoffer,

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





"skrol" wrote:

>
> In one of my sheets I have button, starting a macro wih "MouseDown()".
> This macro starts a simple loop.
> No problem.
> But what I want is that when I release the mouse button, the macto
> stops before ending the loop. Now it goes till the end.
> Now it's something like this:
>
> 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...o&userid=27126
> View this thread: http://www.excelforum.com/showthread...hreadid=476586
>
>

Thanks Peter and Vic.
Specialy Vic's solution runs perfect in any userform.

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.



Maybe someone knows the (simple I hope) solution.

Stoffer Krol

Hi Stoffer Krol,

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_1129665927.8513@excelforum-nospam.com> wrote in
message news:skrol.1x466g_1129665927.8513@excelforum-nospam.com...
>
> Thanks Peter and Vic.
> Specialy Vic's solution runs perfect in any userform.
>
> 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.
>
>
>
> Maybe someone knows the (simple I hope) solution.
>
> Stoffer Krol
>
>
> --
> skrol
> ------------------------------------------------------------------------
> skrol's Profile:
http://www.excelforum.com/member.php...o&userid=27126
> View this thread: http://www.excelforum.com/showthread...hreadid=476586
>

Thanks...
I did it wrong, but now it's working perfect......

I prefer the second option, stopping the loop on releasing the button.
But your solution is also verey usefull.

Thanks again.

Stoffer Krol

> I did it wrong, but now it's working perfect......

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