How to Use SetFocus VBA in Excel

The SetFocus method in Excel VBA is useful when you need to program the user’s cursor to focus on a specific control in a user form.

This function is usually used when you want to direct the user’s input to a specific part of a form.

Let’s take a look at a sample use case for the SetFocus method in Excel.

Suppose you have a user form with the required fields. If the user tries to click the Submit button with missing data on the required fields, you want their cursor to focus on the first required field they’ve missed automatically. How can we do this using Excel VBA?

The SetFocus method redirects the user’s cursor to focus on a specific control in the field. In the example above, if our required field is an object called TextBox_username, then adding the command TextBox_username.SetFocus will make this object the next object of focus.

This use case is just one way to use the SetFocus VBA function in Excel. In the next section, we’ll look into an even simpler application for the SetFocus method.

Now that we know when to use the SetFocus VBA function, let’s dive into how users can apply it on their Excel user forms.

 

 

A Real Example of a Form using the SetFocus Method

Let’s take a look at a real example of an Excel user form that uses the SetFocus VBA method.

The user form seen below is designed to prompt the user for their email address and organization details. Included in the form are two command buttons. If the user clicks on the ‘Close’ button, the user form will close. When the user clicks on the ‘Reset’ button, both text fields will clear, and the user is free to fill-up the form again.

userform example

 

If we look at the scripts added to the Reset button, you’ll notice that the SetFocus method was used after clearing out the two textboxes.

use setfocus in Excel to programmatically control user input

 

If we did not add a SetFocus method, the user would have to click on the first textbox to begin input manually. Using the SetFocus function streamlines the form’s user experience.

You can make your own copy of the spreadsheet above using the link attached below. Download the workbook and open it in your Excel Desktop application to access the sample user form.

If you’re ready to try out the SetFocus function in Excel, let’s begin writing it ourselves!

 

 

How to SetFocus VBA in Excel

This section will explain each step needed to use the SetFocus VBA method in Excel. You’ll learn how to add the method to a command button so that the user’s cursor is redirected when the button is clicked on.

In this example, we’ll use a sample user form that asks the user for their email address and organization. We’ll use the SetFocus method to redirect the user’s cursor to the email address field if the user chooses to reset the form.

Follow these steps to start adding the SetFocus function to your user forms:

  1. First, navigate to the Developer tab and click on the Visual Basic icon. This should open up the Visual Basic Editor in another window.
    open Visual Basic editor
  2. In the Visual Basic Editor, you can control what your user forms look like. You can also add custom VBA scripts to any control placed in your form. In this example, we’ll add custom code to our Reset and Close buttons.
    using a userform with text input
  3. Double-click on the ‘Close’ button to open up the code editor. In this example, we’ll add the command ‘Unload Me’ to exit the form when we click this button.
    Close button closes the userform
  4. In the editor for the ‘Reset’ button, we’ll set the values of the two text boxes to empty strings. Next, we’ll use the SetFocus method to place the user’s cursor on TextBox1.
    reset button uses setfocus button to redirect cursor back to textbox 1
  5. To test out your new scripts, you can run the current user form by clicking on the Run icon above.

  6. Excel will then run your user form.  We’ll test out our scripts by placing input into our form and clicking the Reset button.
    add input to userform with setfocus in Excel
  7. When the Reset button is placed, both fields are cleared out, and the user’s blinking cursor is focused on the email address field. The user is now free to type in their email address directly without using the Tab key or clicking on the textbox itself.
    setfocus in Excel in script associated with Reset button

 

 

Frequently Asked Questions (FAQ)

    1. Can I change other properties of a control when it has the focus?
      Excel does not allow you to set the properties of a control when it is in focus. For example, we cannot change the Enabled property of a control if the user is currently focused on that control.
    2. Can I set focus to a control that is not visible or enabled?
      You can only move focus to a visible control. The control must also have their Enabled property set to True.  

 

 

This step-by-step guide should be all you need to start using the SetFocus method in your Excel user forms. Our guide shows how easy it is to direct user input programmatically using VBA. 

The SetFocus VBA method is just one of many functions available in Excel’s Visual Basic Editor. With so many other Excel functions available, you can surely find one that suits your use case. 

Are you interested in learning more about what Excel can do? Subscribe to our newsletter to learn about the latest Excel guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like