MouseDown()-MouseUp()

[skrol]

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

[Peter T]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Vic Eldridge]

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
>
>

[skrol]

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

[Peter T]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[skrol]

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

[Peter T]

> 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

[skrol]

Hi Peter,
You are right.
Your solution is doing the same thing. Thats what I wanted.
Interesting is:

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.

Stoffer Krol

[Peter T]

Hi Stoffer,

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" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Peter,
> You are right.
> Your solution is doing the same thing. Thats what I wanted.
> Interesting is:
>
> 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.
>
> 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
>

[skrol]

Thanks Peter,
I'm getting there.
After years of struggling with the old fashioned "Basic" in the 80's with a Z-80 Sinclair thing, I switched to Java-script years ago. I like graphical things with lots of gonio and math functions.
But JavaScript is limited, it's not possible to draw a simple line or circle.
So making a normal excel workbook a few weeks ago, I took the courage to have a look at VBA things.
I'm just amazed how fast and simple one can use the interaction between the spreadsheet and Visual Basic. The sheet can be used to do the calculations and than using VBA to call cells and write to cells, create shapes and make things move etc.
Thats fast working. Something I looked for, for years.

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

-----