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?
LVL 14
Jamie GarrochSenior Technical Consultant at BrightCarbonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Use AppActivate "PowerPoint" in Excel to transfer the focus to Powerpoint.
0
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
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.
0
MacroShadowCommented:
Is it not possible to add a macro to the Excel file?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
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.
0
MacroShadowCommented:
Just a thought here...
Try setting the focus to a control on the UserForm.
i.e. textbox1.setfocus
0
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
Thanks for sticking with it MacroShadow. I already tried that too, as well as ActivePresentation.Windows(1).Activate
0
MacroShadowCommented:
can you show us the code you're working with?
0
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
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...
0
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
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

Open in new window

0
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
I figured it out. It's because this line:
With oXL.FileDialog(msoFileDialogFilePicker)

Open in new window

Is opening the file dialog as a child of an invisible Excel instance.
If the line is changed to this:
With Application.FileDialog(msoFileDialogFilePicker)

Open in new window

Then the file dialog is opened as a child of the UserForm within PowerPoint and focus returns to the form automatically once the file dialog is closed. The only reason I had opened it from the Excel object was because I thought you couldn't set Excel file format filters for a PowerPoint file dialog and this was an incorrect assumption.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jamie GarrochSenior Technical Consultant at BrightCarbonAuthor Commented:
Own proven solution accepted as no other provided.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.