Set focus back to the application window after showing userform

vba excel focus userform
Greenberet picture Greenberet · Jan 20, 2015 · Viewed 46.7k times · Source

When showing a userform (running its Show method) it not only shows up on the screen but also takes the focus (the destination of e.g. keystrokes).

Say, the userform is a custom made toolbar. Its Show fires in Workbook_Open() but the form itself is used relatively rarely so we want the focus to go back to the main application window right after its appearance.

Unfortunately, it seems SetFocus method is not valid for application objects.

So how is this done?

I suppose the solution for my example comes after

Private Sub Workbook_Open()
    [...]
    UserForm1.Show

Answer

Patrick Lepelletier picture Patrick Lepelletier · Jan 21, 2015

i use this one :

AppActivate Application.caption

this resets the focus from a userform to your Excel Sheet.

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More