메모

vba sendkeys method...펌

맘편한넘 2013. 7. 31. 07:37

Excel VBA - SendKeys Method

SendKeys Warning

The SendKeys method simulates keystrokes that you would manually input in the active window. Use with caution, because it can have unexpected results.

WARNING: You should only use the SendKeys Method if no other option is available, because it can cause problems, if the wrong window is active when the code runs.

You can read more about SendKeys on the Microsoft website: SendKeys Method.

Using the SendKeys Method

You can use the SendKeys method in your VBA code, to simulate keystrokes that you would manually input in the active window.

The SendKeys method has two arguments: SendKeys(Keys, Wait)

The Keys argument is required, and is the key or keys that you want to send to the application, as text.
The Wait option is optional.
- Use True if Excel should wait for the keys to be processed before returning control to the macro.
- Use False (or omit this argument) if Excel should continue running the macro without waiting for the keys to be processed.

Keys Argument

For the Keys argument, you can use keys or key combinations, such as:

SendKeys "+{F2}" ...for Shift + F2

SendKeys "%ie~" ...for Alt + I, E, Enter

There is a full list of the codes at the end of this page: Keys and Key Combinations

SendKeys Examples

On the Comments VBA page, there are macros that use the SendKeys method to open a comment for editing. For example, the following macro inserts a comment with no user name, and opens that comment so it is ready for editing.

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    ActiveCell.AddComment text:=""
  End If
  SendKeys "+{F2}"
End Sub

In this example, SendKeys "+{F2}" is like pressing Shift and tapping the F2 key, to edit the comment in the active cell.

In Excel 2003, and earlier versions, where there is a Menu Bar, the SendKeys could simulate a keyboard shortcut to run a menu command.

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    ActiveCell.AddComment text:=""
  End If
  SendKeys "%ie~"
End Sub

In this example, SendKeys "%ie~" is like the manual shortcuts to:

press Alt and typing I (to open the Insert menu),
then type E (to select the Edit command),
and then press the Enter key, to select that command.

Run a Macro That Uses SendKeys

If a macro uses the SendKeys method, you can run that macro from the Macro window.

On the Ribbon's View tab, click Macro
Then click on the macro name, and click Run.

edit comment

Or, you could add a button to the Quick Access Toolbar, or to the Ribbon, or to a worksheet, to run the macro.

add button to QAT

Problem Running SendKeys Macro

If you try to run a macro with a keyboard shortcut, and that macro uses the SendKeys method, the SendKeys method might not work. This problem is acknowledged on the Microsoft website: Error Using SendKeys in VB with Shortcut Key Assigned

In this example, the shortcut Ctrl + Shift + C has been assigned to the CommentAddOrEdit macro.

After creating the shortcut, if you press Ctrl + Shift + C, a blank comment is inserted in the cell, but the comment does not open for editing -- the SendKeys method doesn't work.

The problem occurs because this is a very short macro, and you are still press the Ctrl + Shift keys when the macro runs the SendKeys statement. This sends a key combination of Ctrl + Shift + F2 to Excel, instead of Shift + F2.

To solve the problem, you can add a 1 second (or slightly longer) Wait line in the macro, before the SendKeys.

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    ActiveCell.AddComment Text:=""
  End If
    Application.Wait (Now() + TimeValue("00:00:01"))
    SendKeys "+{F2}"
End Sub

Keys and Key Combinations

For the Keys argument, you can use keys or key combinations. You can type a character, such as "a" or use codes from the table below:

Key	Code
BACKSPACE	{BACKSPACE} or {BS}
BREAK	{BREAK}
CAPS LOCK	{CAPSLOCK}
CLEAR	{CLEAR}
DELETE or DEL	{DELETE} or {DEL}
DOWN ARROW	{DOWN}
END	{END}
ENTER (numeric keypad)	{ENTER}
ENTER	~ (tilde)
ESC	{ESCAPE} or {ESC}
HELP	{HELP}
HOME	{HOME}
INS	{INSERT}
LEFT ARROW	{LEFT}
NUM LOCK	{NUMLOCK}
PAGE DOWN	{PGDN}
PAGE UP	{PGUP}
RETURN	{RETURN}
RIGHT ARROW	{RIGHT}
SCROLL LOCK	{SCROLLLOCK}
TAB	{TAB}
UP ARROW	{UP}
F1 through F15	{F1} through {F15}

To combine keys with Ctrl, Shift and/or Alt, precede the character with the following codes. For example:
SendKeys "+{F2}" ...for Shift + F2

Key	Code
SHIFT	+ (plus sign)
CTRL	^ (caret)
ALT	% (percent sign)