Link to home
Create AccountLog in
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?
Avatar of Joe Howard
Joe Howard
Flag of United States of America image

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

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.
Is it not possible to add a macro to the Excel file?
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.
Just a thought here...
Try setting the focus to a control on the UserForm.
i.e. textbox1.setfocus
Thanks for sticking with it MacroShadow. I already tried that too, as well as ActivePresentation.Windows(1).Activate
can you show us the code you're working with?
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...
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

ASKER CERTIFIED SOLUTION
Avatar of Jamie Garroch (MVP)
Jamie Garroch (MVP)
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Own proven solution accepted as no other provided.