Set focus back to application window after showing custom form

Question

When a user form is displayed (its Show method), it not only appears on the screen, but also takes focus (destination, for example, keystrokes).

Say a custom form is a custom toolbar. His Show triggered in Workbook_Open() , but the form itself is used relatively rarely, so we want the focus to return to the main application window immediately after it appears.

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

So how is this done?

I believe that the solution for my example appears after

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

Answer 1

I use this one:

AppActivate Application.caption

this resets focus from the custom form to the excel sheet.

Answer 2

For me

 AppActivate ThisWorkbook.Application 

right after the Show statement is working fine.

In other cases

 AppActivate "Microsoft Excel" 

may also be ok.

Answer 3

It is a bit complicated, but it is something that can be done.

In the "Private Sub UserForm_Initialize ()" routine, add this as the last line:

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

In any of the common code modules (add one if you don't have them), declare an API function:

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

In any of the general code modules (maybe, in the case of the API declaration), add this routine:

 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 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 

Answer 4

Another form:

 AppActivate ThisWorkbook.Name 

Answer 5

I use AppActivate ActiveWindow.Caption because AppActivate Application.Caption may focus the wrong window if multiple windows are open for the same book.

Answer 6

As a note to this excellent discussion, in some cases you can avoid the focus problem by skipping the call before .Show , so focus never moves in the first place. For example, in Word, if you are updating a modeless form or dialog box, just update the required area and omit the .Show call, for example:

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

Answer 7

I am creating an object for an application, for example. Outlook, then change WindowSate to Maximized (OlMaximized), then when I want to remove focus, I minimize (olMinimized)

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

Or you change the state inside the application, you can also change its location and size, etc., to get additional information: https://msdn.microsoft.com/en-us/library/office/ff838577.aspx

 Application.WindowState = xlMaximized 

Answer 8

 Private Sub UserForm_Activate() RefRangeIn.SetFocus End Sub 

it works for me in excel 2013 vba

Answer 9

Add a dummy form and add codes as shown 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 

Answer 10

I created a floating menu with a user form and use this code so that my cursor leaves the user form and jumps back to my worksheet. It works at the end of each command button code, as well as with a user form initialization code.

AppActivate ThisWorkbook.Application

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