VBA SendKeys

Excel VBA SendKeys

In this article, we will see an outline on Excel VBA SendKeys. Have you ever thought of creating a program that saves your worksheet automatically and you don’t need to hit the Ctrl + S or Excel Save button to do so? It seems to be a fantasy, right? However, this can be done. In VBA, there is a command statement called SendKeys which allows you to send the keypresses as a command to the application in the same way as if you are using the keyboard to do the task. (Ex. Saving the File. You do Ctrl + S button-through keyboard to save a file). It is recommended to use SendKeys as the last option while you are automating any task. The reason behind this is, as we already discussed, SendKeys sends the keystrokes to the application that is currently active. What if you have an application window active at the time of code running under which you don’t want the changes to happen? It will give unexpected results, right? This is the sole reason, we should use SendKeys as the last option while automating the things. It can be used for the small automation though, where no other application environment is about to interfere with the one which you wanted the change to happen.

Syntax SendKeys Method

Following is a syntax SendKeys Method:

SendKeys(String As String, [Wait])

Where,

The first argument ‘String As String’ specifies the key combinations that you wanted to use under the program (Ex. Ctrl + S would be the key combination).

Wait would be an optional parameter which takes Boolean values TRUE and FALSE. If the wait has the value TRUE, it means the system will wait for the keys to be processed and then go to the next line and compile/run the same. If the wait value is FALSE (can keep blank), then the system will not wait for the keys to be processed and will continue to run the entire code.

Certain methods are using which you can combine the keystrokes with Ctrl, Shift or Alt.  Let’s see how we can do that Following is a table that specifies the keystroke combinations for SendKeys with Ctrl, Shift and Alt:

Well, what does that mean? It means, if you want to use a keyboard combination as Ctrl + S under the code, you can use the SendKeys method and do it with SendKeys then a Caret (^) operator for Ctrl and then “s”. It will save the current worksheet in your excel file.

How to Use the SendKeys Method in Excel VBA?

We will learn how to use SendKeys Method in Excel by using the VBA Code.

VBA SendKeys – Example #1

Let’s have some simple examples that will allow us to dive deeper into the working of the VBA SendKeys command.

In this first example, we are about to see very simple automation that automatically saves the current Excel file that is open. You don’t need to hit the Save button or Ctrl + S to save the file. Follow the steps below to achieve the result.

Step 1: Open Module from the Insert menu tab as shown below.

Step 2: Define a new sub-procedure that can hold your macro.

Code:

Sub Example_1()

End Sub

Step 3: Now, to save any file, you have a keyboard key combination as Ctrl + S. to convert this into code, we can use the caret operator (^) and then the “s” keyword as an argument to SendKeys statement.

Code:

Sub Example_1()

Application.SendKeys ("^s")

End Sub

Here, keyword Application specifies the Application to which we are sending the keystrokes (in this case Excel application). “^s” under the parentheses specifies the similar keyboard operation Ctrl + S.

Step 4: Run this code by hitting the Run button or by pressing the F5 key.

After the successful run of this code, your file is saved.

VBA SendKeys – Example #2

Now, we are about to see, how to close a current application by sending softkeys through the macro code using the SendKeys method. Follow the steps below:

Step 1: Write the sub-procedure that can hold your macro under the module.

Code:

Sub Example_2()

End Sub

Step 2: Use Application.SendKeys method to pass the keyboard softkeys to the application. Since the current VBA pane can be closed with the keyboard Alt + Q shortcut, we can use “%q” under macro.

Code:

Sub Example_2()

Application.SendKeys ("%q")

End Sub

Step 3: Hit the Run button or F5 to run this code. As soon as you run this code, the current Visual Basic Editor will close and you will navigate to the Excel file.

VBA SendKeys – Example #3

Now, we are going to write a code that automatically opens the Notepad and writes a text in it automatically. See the screenshot below:

Step 1: Write the sub-procedure that can hold your macro under the module.

Code:

Sub Example_3()

End Sub

The Call Shell allows the system to open the application. The path is where the Notepad.exe is present. vbNormalFocus is an optional argument that specifies the focus on opening and restoring the application to its original size and position.

Step 2: Use Call and Sendkeys in combination so that the system can add a text in Notepad.

Code:

Sub Example_3()

Call Shell("C:\Windows\System32\Notepad.exe", vbNormalFocus)

End Sub

Step 3: Here, we are adding the text “Hello VBA!” using SendKeys to the Notepad. True specifies the waiting time for SendKeys command.

Code:

Sub Example_3()

Call Shell("C:\Windows\System32\Notepad.exe", vbNormalFocus)

Call SendKeys("Hello VBA!", True)

End Sub

Step 4: Run this code by hitting the F5 key or by clicking on the Run button.

This is how we can use the SendKeys method to automate the applications through VBA macro.

Things to Remember

• Be very careful while using SendKeys since it may give unexpected results if you have multiple applications open.
• You can use the SendKeys to automate the small tasks like saving an Excel File or Closing an Excel File etc.
• Use the SendKeys method as a last option while you are trying to automate the things.

Recommended Article

This is a guide to VBA SendKeys. Here we discuss how to use SendKeys Method in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA While Loop
2. VBA Environ
3. VBA Goal Seek
4. VBA Name Worksheet