0

I wrote a program to take Excel data in an Excel spreadsheet and put it in a PDF form. The program opens the PDF form, fills in the data, and then saves and closes the PDF. It then refocuses to the Excel Workbook. It works fine on my laptop, but when I handed it off to a friend a run-time error of Invalid procedure call or argument showed up on...

AppActivate ThisWorkbook.Name

I'm wondering why this worked for me, but not my friend. I'm also wondering if there is another way to bring the focus back to the Excel Workbook. Thanks!

Edit: I want it to refocus to Excel to show a Msgbox being popped up indicating that the tool has done it's job.

ThisWorkbook.Activate
MsgBox "Job Completed!"
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Bob
  • 1,344
  • 3
  • 29
  • 63
  • 2
    Can You show some more code? Have You tried to put ThisWorkbook.Activate? – Teamothy Sep 10 '19 at 15:53
  • I do not have adobe pro. I had a version of the tool that used pro, but had to make another version since my friend doesn't have it. – Bob Sep 10 '19 at 15:57
  • 2
    [One Possible reason](https://stackoverflow.com/questions/46932069/appactivate-works-in-excel-2007-but-not-in-2010) – Siddharth Rout Sep 10 '19 at 16:03
  • @Teamothy yes, it runs, but the Msgbox doesn't pop up. See Edit – Bob Sep 10 '19 at 16:11
  • Nothing I seem to do will make it so the MsgBox pops up automatically. The user has to click on the Excel screen for the box to pop up – Bob Sep 10 '19 at 16:54
  • So, have you tried `AppActivate Title:=ThisWorkbook.Application.Caption` (as suggested in the link Siddharth Rout pointed to) ? – DecimalTurn Sep 10 '19 at 17:33
  • @Decimalturn yes and it throws an error – Bob Sep 10 '19 at 17:54
  • `Invalid procedure call or argument` ? Or another one? – DecimalTurn Sep 10 '19 at 17:57
  • Try ading `Doevents` before the msgbox? If this doesn't work then we will use APIs to find the window and bring it to foreground – Siddharth Rout Sep 11 '19 at 02:34
  • `ThisWorkbook.Activate` and `AppActivate ThisWorkbook.Name` should have worked. I would like to see the complete code if that is possible. Let me know by using the "@" sign and then my name so that I can get notified... – Siddharth Rout Sep 11 '19 at 02:40

1 Answers1

0

I know this is not exactly a solution to your problem, but if none of the ideas from the comments helped (activate and whatnot), consider using mshta.exe instead of msgbox:

Title = "title"
MessageText = "msg"
PauseTimeSeconds = 0

CreateObject("WScript.Shell").Run "mshta.exe vbscript:close(CreateObject(""WScript.Shell"")." & "Popup(""" & MessageText & """," & PauseTimeSeconds & ",""" & Title & """))"

It won't re-focus on excel but should display your msg on top of other windows, which is what you want I believe. Please note the code will continue to execute whether user clicks ok or not. PauseTimeSeconds will close the window after certain amount of seconds, or leave it open if set to 0.

Daniel
  • 814
  • 6
  • 12