Focus back to Excel once email has been created with VBA

Question

I have a VBA macro which does the following:

1. Creates a report, saves it to main directory. Closes the report
2. Creates various emails, attaches the report to each (doesn't send the email)
3. Msgbox shows up showing that process is finished

The thing is that, once the whole process is finished, I want the user to know it is done, but this code doesn't return the focus to excel (which I hoped would be the problem)

Windows(the_current_window).Activate
Worksheets(currentQuoteSheet).Select

The last email created is what stays on screen. The msgbox doesn't come up, only if I click on excel icon on the menu bar it does. So users are there just waiting to finish (when it has, in fact :( )

Any ideas how to make sure the msgbox shows up after last email is created without users having to click on excel?

Solution 1

Try

sub doSomething()
   'do something
   AppActivate "Microsoft Excel"
End Sub

Solution 2

I am on MS Office 365 and for some reason I get an Application Error when trying AppActivate "Microsoft Excel" and AppActivate "Excel", however:

AppActivate Application.Caption

Worked for me.

Solution 3

Although this works to a degree, it doesn't activate the MSGbox to allow one to press y/n on the keyboard (you can with the mouse).

This allows keyboard interaction if you are doing the task repetitively and don't want to grab your mouse. Tested Office 2013 (64bit) (remove PtrSafe if you are 32bit)

Private Declare PtrSafe Function MessageBox _
        Lib "User32" Alias "MessageBoxA" _
           (ByVal hWnd As Long, _
            ByVal lpText As String, _
            ByVal lpCaption As String, _
            ByVal wType As Long) _
        As Long
Sub Test
        'Call showwindow(ActiveWorkbook.Name)
        AppActivate "Excel"
        'I need this to retain the focus
        retval = MessageBox(&O0, "Do you want to process", "My Question", vbYesNo + vbSystemModal)
End Sub

Comments

[user1135218]

Hi, this didn't work unfortunately, you need to click on Excel before the message comes up.

[Dejan Dozet]

That is the way!