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 GarrochPowerPoint Consultant & DeveloperAsked:
Who is Participating?
 
Jamie GarrochPowerPoint Consultant & DeveloperAuthor 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
 
MacroShadowCommented:
Use AppActivate "PowerPoint" in Excel to transfer the focus to Powerpoint.
0
 
Jamie GarrochPowerPoint Consultant & DeveloperAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MacroShadowCommented:
Is it not possible to add a macro to the Excel file?
0
 
Jamie GarrochPowerPoint Consultant & DeveloperAuthor 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 GarrochPowerPoint Consultant & DeveloperAuthor 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 GarrochPowerPoint Consultant & DeveloperAuthor 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 GarrochPowerPoint Consultant & DeveloperAuthor 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 GarrochPowerPoint Consultant & DeveloperAuthor Commented:
Own proven solution accepted as no other provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.