AppActivate not working from PowerPoint VBE but OK from Excel VBE

I have a UserForm in PowerPoint with a button on it that opens the Excel file picker dialog. When the file is selected, PowerPoint VBA then opens the selected Excel file in a hidden instance, loads some data from it into the UserForm and leaves the file open whilst the UserForm remains loaded/visible. When this happens, the PowerPoint icon in the Windows taskbar pulses and a second PowerPoint icon hops up and down, indicating that PowerPoint no longer has focus. This is confirmed by the modal UserForm remaining in a defocussed state.

I added several variations of the AppActivate statement to the PowerPoint VBA code but none work, although no error is raised e.g.

AppActivate Application.Caption
AppActivate "PowerPoint"

Open in new window

However, if I use the same AppActivate command(s) from the Excel VBE when in the defocused state above, the command works as expected.

How can I get the PowerPoint VBE to return focus to PowerPoint in this case?

VBAMicrosoft PowerPointMicrosoft ExcelMicrosoft Office

Last Comment

Jamie Garroch (MVP)

8/22/2022 - Mon

Joe Howard

1/17/2018

Use AppActivate "PowerPoint" in Excel to transfer the focus to Powerpoint.

Jamie Garroch (MVP)

1/17/2018

ASKER

Thanks MacroShadow. Does that mean it's not possible to "reacquire" the focus within the hosting VBE? I tried it the other way round and it appears so. For example, running this in Excel did not return focus to Excel:

Sub test()
  AppActivate "test.pptx"
  AppActivate "test.xlsx"
End Sub

Open in new window

I'm not sure how I can make your proposed solution work since the VBA app I'm working on is hosted within PowerPoint.

Joe Howard

1/17/2018

Is it not possible to add a macro to the Excel file?

Your help has saved me hundreds of hours of internet surfing.

fblack61

Jamie Garroch (MVP)

1/17/2018

ASKER

Unfortunately not. The app allows the user to select any Excel file (which I don't have access to) as an input for the code running in the PowerPoint VBE. I didn't want to use an API but I'm currently investigating if the FindWindow and SetForegroundWindow APIs in this EE question will work.

Joe Howard

1/17/2018

Just a thought here...
Try setting the focus to a control on the UserForm.
i.e. textbox1.setfocus

Jamie Garroch (MVP)

1/17/2018

ASKER

Thanks for sticking with it MacroShadow. I already tried that too, as well as ActivePresentation.Windows(1).Activate

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

Joe Howard

1/17/2018

can you show us the code you're working with?

Jamie Garroch (MVP)

1/17/2018

ASKER

Let me see if I can condense some code to demo the issue. In parallel, I have finished trying the API approach and it doesn't work either. BUT, during that testing, I had a code error which caused the debugger to be invoked, I fixed the error and let the code continue and focus was returned to the UserForm. Then I went back to the AppActivate mechanism and added a Stop statement right before it. When running the code, the form loses focus, stops, and then I let it continue with focus returning to the UserForm as expected. When I remove the Stop statement, focus isn't returned to the UserForm. Is this indicative of some kind of timing issue? I've dried adding a delay and DoEvents prior to AppActivate without success. Let me go condense a code sample...

Jamie Garroch (MVP)

1/17/2018

ASKER

This demonstrates the issue:

Option Explicit

' This is a condensed PPT VBE code snippet to demo the issue.
' Insert a blank UserForm1, add a CommandButton1 and this code to it.
' No other changes are made e.g. UserForm1.ShowModal = True

Private oXL As Object
Private oWB As Object
Private XLfile As String

Private Sub CommandButton1_Click()

  Set oXL = CreateObject("Excel.Application")
  
  With oXL.FileDialog(msoFileDialogFilePicker)
    .Filters.Clear
    .Filters.Add "Excel", "*.xls; *.xlsx; *.xlsm", 1
    .Show
    .AllowMultiSelect = False
    If .SelectedItems.Count > 0 Then XLfile = .SelectedItems(1) Else Exit Sub
  End With
  
  ' Focus has now been now lost from PowerPoint/UserForm1

  Set oWB = oXL.Workbooks.Open(XLfile)
  
  ' If you comment out the next line, focus is not returned to the UserForm
  ' but it works if you leave it in, let it Stop and then Continue.
  Stop
  
  ' Try to return focus to PowerPoint
  AppActivate Application.Caption
  
End Sub

Private Sub UserForm_Terminate()
  ' Clean up
  If Not oWB Is Nothing Then oWB.Close Else Set oWB = Nothing
  Set oXL = Nothing
End Sub