Set focus back to the application window after showing userform

Question

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

score 23 · Accepted Answer · answered Jan 21 '15 at 17:04

23

i use this one :

AppActivate Application.caption

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

answered Jan 21 '15 at 17:04

Patrick Lepelletier

1,596
2
17
24

4

This seems to be a more universal version of what I also tried. Maybe `ThisWorkbook.Application.Caption` would be the most solid solution.. – Greenberet Jan 22 '15 at 08:48
This is exactly what I needed. Very helpful! – Josh Jobin Mar 13 '17 at 13:54

score 5 · Answer 2 · answered Jan 20 '15 at 10:04

5

For me

AppActivate ThisWorkbook.Application

right after the Show statement seems to work fine.

In other cases

AppActivate "Microsoft Excel"

may also be ok.

answered Jan 20 '15 at 10:04

Greenberet

490
1
5
18

IDK why but now these stopped working... So further answers are explicitly welcome! – Greenberet Jan 20 '15 at 12:57
this still works for me in Excel 2016 with a 64-bit environment. In the userform code I have `Me.Show VBA.vbModeless` followed immediately by `AppActivate Excel.ThisWorkbook.Application.Caption`. If this isn't working for you my first thought is either you're not using a modeless form or something in the code returns focus to the userform after this `AppActivate` line. To test I would suggest trying it in a new workbook on a new blank form. – ChrisB Mar 09 '20 at 20:27

score 3 · Answer 3 · answered Dec 02 '19 at 17:38

Both AppActivate Application.Caption and (the better) AppActivate ActiveWindow.Caption mentioned in the other answers do their job in focusing back on the application window itself ... but they do NOT focus on the actual cell/range where one typically wants the focus to be. For that, use:

ActiveCell.Activate

which has the benefit of not requiring an additional click on the cell area of the sheet where you want to return focus - an additional click that can potentially change the previous selection.

score 2 · Answer 4 · answered Jan 20 '15 at 15:49

This is a bit tricky, but this is what can do.

In the subroutine “Private Sub UserForm_Initialize()”, add this as the last line:

Private Sub UserForm_Initialize()
    . . . . . . . . . . 
    Application.OnTime Now(), "MoveFocusToWorksheet"
End Sub

In any of the general code modules (add one if you have none), declare an API function:

Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long

In any of the general code modules (could be the one with the API declaration, of course), add this subroutine:

Public Sub MoveFocusToWorksheet()
    Dim Dummy As Long

    ThisWorkbook.Worksheets("Sheet1").Activate
    ' "Sheet1" here is the tab name of the sheet you want to move focus to. _
        Or simply use then: With shtABC.Activate _
        where "shtABC" being the worksheet's CodeName, _
        same as ThisWorkbook.Worksheets("Sheet1").CodeName, _
        same as the sheets module name showing in the Project Explorer panel.
    Dummy = SetForegroundWindow(Application.hwnd)
End Sub

While truly appreciating your efforts I think it is AppActivate what was really designed to do what I needed in Q. The VBA help says: _"The **AppActivate** statement changes the focus to the named application or window but does not affect whether it is maximized or minimized."_ — Greenberet, Jan 22 '15 at 08:58
@Greenberet The AppActivate is a very elegant solution. Caution: the help also says: _"If there is no exact match, any application whose title string begins with **title** is activated."_ This is a source of potential danger. As long as you are sure it is **never** an issue in you case, the AppActivate is definitely it. — Gene Skuratovsky, Jan 22 '15 at 12:25

score 1 · Answer 5 · answered May 16 '17 at 10:12

I create an object for the application e.g. Outlook, then change the WindowSate to Maximised (OlMaximized), then when I want to remove focus I minimise (olMinimized)

Set OutlookObj = GetObject(, "Outlook.Application")
OutlookObj.ActiveExplorer.WindowState = olMinimized
OutlookObj.ActiveExplorer.WindowState = olMaximized

Or you change the state from inside the application, you can also change its location and size etc for more info see: https://msdn.microsoft.com/en-us/library/office/ff838577.aspx

Application.WindowState = xlMaximized

Riccardo La Marca · Answer 6 · 2018-03-06T12:53:22.783

1

An other form is:

AppActivate ThisWorkbook.Name

edited Mar 06 '18 at 12:53

answered Mar 06 '18 at 12:44

Riccardo La Marca

775
5
15

score 1 · Answer 7 · answered Aug 06 '19 at 19:12

1

I use AppActivate ActiveWindow.Caption because AppActivate Application.Caption can focus the wrong window if multiple windows are opened for the same workbook.

answered Aug 06 '19 at 19:12

CETAB

21
2

score 0 · Answer 8 · answered Jan 23 '17 at 21:08

As a footnote to this excellent discussion, in some circumstances you may avoid the focus problem by skipping the call to .Show, so the focus never moves in the first place. For example with Word, if you are updating a modeless form or dialog box, just update the required area and omit the call to .Show, e.g.:

Sub ShowProblems(ByVal ProbLoc)
    EditBox2.TextBox.Text = "Here is the problem location: " & ProbLoc
    ' not needed: EditBox2.Show vbModeless
End Sub

score 0 · Answer 9 · answered Jul 17 '17 at 07:45

0

Private Sub UserForm_Activate()
    RefRangeIn.SetFocus
End Sub

it work for me, in excel 2013 VBA

answered Jul 17 '17 at 07:45

Carlosmuerto

1

score 0 · Answer 10 · edited Jul 28 '18 at 17:45

0

Add a dummy form and add codes as below:

Private Sub SomeButton_Click()

    frm_Dummy.Show vbModeless
    Unload frm_Dummy

End Sub

OR

Sub SomeSub()

    frm_Some.Show vbModeless
    frm_Dummy.Show vbModeless
    Unload frm_Dummy

End Sub

edited Jul 28 '18 at 17:45

Cœur

37,241
25
195
267

answered Aug 20 '17 at 17:45

Fen

31
3

score 0 · Answer 11 · answered Nov 05 '18 at 04:55

I created a floating menu with a user form and use this code to make my cursor leave the user form and jump/focus back to my worksheet. It works at the end of each command button code and with the initiation code of the user form as well.

AppActivate ThisWorkbook.Application

Just place the above line of code before the "End Sub" line of any command button code and the initial show userform code.

Set focus back to the application window after showing userform

11 Answers11

Linked