Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- skrol ------------------------------------------------------------------------ skrol's Profile: http://www.excelforum.com/member.php...o&userid=27126 View this thread: http://www.excelforum.com/showthread...hreadid=476586 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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 Kro -- skro ----------------------------------------------------------------------- skrol's Profile: http://www.excelforum.com/member.php...fo&userid=2712 View this thread: http://www.excelforum.com/showthread.php?threadid=47658 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
MouseDown()-MouseUp()
Thanks Peter, I'm getting there. After years of struggling with the old fashioned "Basic" in the 80' with a Z-80 Sinclair thing, I switched to Java-script years ago. I lik graphical things with lots of gonio and math functions. But JavaScript is limited, it's not possible to draw a simple line o circle. So making a normal excel workbook a few weeks ago, I took the courag to have a look at VBA things. I'm just amazed how fast and simple one can use the interaction betwee the spreadsheet and Visual Basic. The sheet can be used to do th 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 :) ----- :cool -- skro ----------------------------------------------------------------------- skrol's Profile: http://www.excelforum.com/member.php...fo&userid=2712 View this thread: http://www.excelforum.com/showthread.php?threadid=47658 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart MouseUp Event Feedback | Charts and Charting in Excel | |||
mouseup | Charts and Charting in Excel | |||
Mousedown / Charting | Excel Programming | |||
get reference to cell from mousedown event | Excel Programming | |||
Converting MouseDown Coordinates to Chart Point Coordinates | Excel Programming |