Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag for United Kingdom of Great Britain and Northern Ireland asked on

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

Avatar of undefined
Last Comment
Jamie Garroch (MVP)

8/22/2022 - Mon
Joe Howard

Use AppActivate "PowerPoint" in Excel to transfer the focus to Powerpoint.
Jamie Garroch (MVP)

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

Is it not possible to add a macro to the Excel file?
Your help has saved me hundreds of hours of internet surfing.
Jamie Garroch (MVP)

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

Just a thought here...
Try setting the focus to a control on the UserForm.
i.e. textbox1.setfocus
Jamie Garroch (MVP)

Thanks for sticking with it MacroShadow. I already tried that too, as well as ActivePresentation.Windows(1).Activate
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Joe Howard

can you show us the code you're working with?
Jamie Garroch (MVP)

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)

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.Add "Excel", "*.xls; *.xlsx; *.xlsm", 1
    .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.
  ' 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

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Jamie Garroch (MVP)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jamie Garroch (MVP)

Own proven solution accepted as no other provided.