why doesn't SendKeys work consistently?

[crimsonkng]

In my macro, I need to use the SendKeys command for various reasons. But
it's wierd: sometimes SendKeys works like I programmed them, sometimes just
some of the SendKeys commands work, sometimes Excel "calls" the SendKeys in a
different order than from what I've programmed, and sometimes SendKeys
doesn't work at all. All of the above can happen when
1. I run the macro using a keyboard command or
2. if I run the macro by merely clicking "Run" on the macro list form or
3. if I do an F8 within Visual Basic. (Actually, it seems like SendKeys
never works when I do the F8 thing.)

For example, here's a simple Macro that just won't run consistently/correctly:

SendKeys "%(=)" 'inputs an AutoSum (Alt-equals)
SendKeys "{ENTER}"
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
SendKeys "{END}"
SendKeys "{UP}"
SendKeys "{UP}"

I can't get a handle on it. Obviously, I'm doing something wrong. Any
hints/suggestions?

Dan

 

[Helmut Weber]

Hi,

sendkeys is notoriously unreliable anway.

It sends keystrokes to the active window.

And when you are in the VBA editor,
the keystrokes are sent to the editor window,
as this is the active window.

Helmut Weber

 

[K Dales]

You can try an AppActivate statement just before the SendKeys, e.g:
AppActivate "Microsoft Excel"
SendKeys "{END}"
etc...

But this is still not reliable in a multitasking environment since other
apps may steal focus even after the AppActivate and then the SendKeys goes to
the wrong app (the one with focus at the time the statement is called). I
have big headaches with my email notifications coming up and stealing focus,
so I have learned to avoid SendKeys.

Please note (if you were not aware) that your code could all be done with no
SendKeys:
ActiveCell. "%(=)" 'inputs an AutoSum (Alt-equals
With ActiveCell
Set SumRange = Range(.Cells(0,1),.Cells(0,1).End(xlUp))
.Formula = "=SUM(" & SumRange.Address & ")"
.Offset(-1, 1).Copy
.Offset(1, 0).Range("A1").PasteSpecial xlPasteAll
End With
SumRange.Cells(0,1).Select ' equivalent to your {END}{UP}{UP} due to def'n
of SumRange

 

[tony h]

send keys is dificult to get right but judicious use of ensuring th
window to which you want to send is ACTIVE and activating it if not.

The thing is that SENDKEYS sends to the active window so if you ar
doing an F8 I presume you are in the VBA window and so it will be th
VBA window that recieves your typing.

It won't be a problem with the code not doing things the sam