how to get onkey macro to fire while another macro is running

[Brian Murphy]

Hello to all you Excel Wizards,

I have a macro that does some quick and dirty animation with an embedded chart (I just use a simple loop, and the Escape key to halt). I'm pretty happy with how the animation works but I'd like to enable some keypress commands to control the animation, and excel does not respond to keystrokes while my animation macro is running. This behavior actually makes some sense since you can't click on cells or do anything else while the animation macro is running. I use Application.EnableCancelKey to respond to a press of the Escape key. But Application.OnKey does not work for other keys.

Is there any way to allow my animation macro to respond to presses of other keys besides Escape?

I might point out that I have successfully worked out using mouse movements to control the animation stream. If the mouse isn't moving, the animation plays normally. If the mouse is moved to the right(or left) the animation plays forward (or backward) as long as the mouse keeps moving. Otherwise it starts to play normally again. I'm using the API call GetCursorPos to do this. This is pretty cool. At least to me.

Anyway, responding to some other keystrokes would be nice. I hope it can be done without diving into classes and events, which I suppose is possible since the animation is being done with a single excel chart.

Thanks,

Brian Murphy
Austin, Texas

 

[Tom Ogilvy]

I think you would need to subclass Excel and or hook into the keyboard
buffer. See this thread by Stratos

http://groups.google.com/[email protected]

for some ideas.



--
Regards,
Tom Ogilvy

Hello to all you Excel Wizards,

I have a macro that does some quick and dirty animation with an embedded
chart (I just use a simple loop, and the Escape key to halt). I'm pretty
happy with how the animation works but I'd like to enable some keypress
commands to control the animation, and excel does not respond to keystrokes
while my animation macro is running. This behavior actually makes some
sense since you can't click on cells or do anything else while the animation
macro is running. I use Application.EnableCancelKey to respond to a press
of the Escape key. But Application.OnKey does not work for other keys.

Is there any way to allow my animation macro to respond to presses of other
keys besides Escape?

I might point out that I have successfully worked out using mouse movements
to control the animation stream. If the mouse isn't moving, the animation
plays normally. If the mouse is moved to the right(or left) the animation
plays forward (or backward) as long as the mouse keeps moving. Otherwise it
starts to play normally again. I'm using the API call GetCursorPos to do
this. This is pretty cool. At least to me.

Anyway, responding to some other keystrokes would be nice. I hope it can be
done without diving into classes and events, which I suppose is possible
since the animation is being done with a single excel chart.

Thanks,

Brian Murphy
Austin, Texas

 

[Brian Murphy]

Thanks for the reply, Tom. It looks like classing the chart so I can use
events would be the easiest way to do this. I've done some of this before
with my chartool.xla utility, so I should be able to do it again.

Cheers,

Brian

 

[Tom Ogilvy]

I don't see any chart events that would be useful in this context. I think
you are in deeper waters than that. Givent that, however, my first thought
was to sprinkle your code with doevents statements, and perhaps that would
let your onkey macros run. I doubt it, but that would be the easiest if it
works. It probably wouldn't be real consistent if it does work, but people
like to repetitively hit the same key until they get a response.

 

[Brian Murphy]

Hello Tom,

I had given DoEvents a try, but it had no affect.

As a test, I threw together the following code, and this didn't
respond either. At least not until the macro aaa() was finished
running.

In my Chartool code, I initialize a class with events on a chart, and
I've successfully got it responding to a host of key presses. So if I
get amibitious enough, I should be able to use that approach for my
animation.

Brian


Sub aaa()
Application.OnKey "{right}", "sub2"
[a1].Select
Do
DoEvents
Selection.Offset(1, 0).Select
DoEvents
For j = 1 To 10000
DoEvents
Next j
DoEvents
i = i + 1
DoEvents
Loop Until i >= 10

End Sub

Sub sub2()
MsgBox "right"
DoEvents
End Sub

 

[Vic Eldridge]

Howdy Brian,

GetAsyncKeyState might suit your purposes.
Have a play with the following example. You'll see that it responds
to the right arrow key while the loop is running.

Regards,
Vic Eldridge


Public Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As
Long) As Integer
Public Const VK_RIGHT = &H27

Sub Looper()
Range("A1") = 0
Do Until Range("A1") = 10000
Range("A1") = Range("A1") + 1
Range("B1") = GetAsyncKeyState(VK_RIGHT)
Loop
End Sub

 

[Brian Murphy]

Hello Vic,

That's a great tip.

I ran your sample and it looks like it can do the trick. I'll be
visiting the microsoft site to read up on the GetAsyncKeyState
function.

After thinking about it some more, I don't think my idea of using a
custom class on a chart can work. It can't run an animation loop any
differently than a regular module. Which I think means the event
procedures won't fire while the animation loop is running.

What is really needed is Application.OnTime that can distinguish
hundredths of a second, or at least tenths of seconds, which I don't
think it can. This would allow the animation "loop" to not have to
run continuously.

Anyway, I think your idea is just what I'm looking for.

More testing to do tonight.

Cheers,

Brian
Austin, Texas

 

[Bob Phillips]

Have you tried the windows timer using callbacks to get that level of time?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Brian Murphy]

Hello Bob,

I have not tried what you suggested. I think once a long time ago I tried to do something referred to as a "callback", and I failed at it. I don't recall what I was trying to do.

I have implemented the GetAsyncKeyState stuff as Vic suggested, and in my testing so far it is working beautifully. I ended up with a single function I call once each time through my animation loop. I'm just checking 4 specific keys. After this function is called my animation loop performs some special actions if one of my key press codes is True. I found I needed the Sleep statement to keep the function from ringing True more than once for a single press of a key.

Function CheckForKeyPress()
keyLeft = (GetAsyncKeyState(VK_LEFT) <> 0)
keyRight = (GetAsyncKeyState(VK_RIGHT) <> 0)
keyX = (GetAsyncKeyState(VK_X) <> 0)
keyY = (GetAsyncKeyState(VK_Y) <> 0)
If keyLeft Or keyRight Or keyX Or keyY Then Sleep 50
End Function

I'm elated at how well the animation is working for me. It's a huge improvement over how I first did this some years back.

Cheers,

Brian