Excel VBA - SendKeys MethodThe SendKeys method simulates keystrokes that you would manually input in the active window. Use SendKeys with caution, because it can have unexpected results Author: Debra Dalgleish |
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.
You can use the SendKeys method in your Excel macros VBA code, to simulate keystrokes that you would manually input in the active window.
The SendKeys method has two arguments: SendKeys(Keys, Wait)
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, in the Keys and Key Combinations section, below
On the Comments VBA page, there are macros that use the SendKeys method to open a comment for editing.
To see those macros, press Alt+F11, top open the Visual Basic Editor (VBE).
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 manually pressing the Shift key, and then tapping the F2 key
That manual shortcut would open the worksheet comment in the active cell, so it is ready for you to edit the comment.
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:
If a macro uses the SendKeys method, you can run that macro from the Macro window.
Or, you could add a button to the Quick Access Toolbar, or to the Ribbon, or to a worksheet, to run the macro.
If the SendKeys command isn't working correctly, one of these solutions might help fix the problem
SendKeys Not Working From Shortcut
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
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:
Be sure to include the brace characters (curly brackets) at the start and end of the codes, such as {BACKSPACE}
Key |
Code |
BACKSPACE or BKSP |
{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 key |
{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) |
To see the SendKeys sample code, and the list of keys, download the Excel SendKeys workbook. The zipped file is in xlsm format, and contains macros. Enable macros when you open the file, if you want to test the SendKeys macro.
Last updated: July 22, 2023 4:16 PM