AppActivate problems or is there a better way to take focus froma non modal user form

[Mike Clemens]

I have a workbook that displays a non modal user form when the workbook is
opened. The user form contains a bunch of buttons for running a number of
different macros. After one of the macros runs I want to move focus from the
user form back to the worksheet. I have been using the following statement to
do this:

AppActivate ActiveWorkbook.Windows(1).Caption

This works much of the time but occasionally it fails with an invalid argument
error message. I have no idea what causes the statement to stop working or
begin working again shortly after it has been failing. When the problem is
occurring stopping and restarting Excel does not fix the problem. The problem
occurs when the workbook is the only one open in Excel. I also know that the
value of the caption field is correct from looking at it while in debug.
Sometimes just waiting a few minutes makes the problem go away.

Two questions:

1. Anyone have an idea as to what would cause the appactivate statement to start
failing or working again?

2. Is there a better way to move focus from a non modal user form back to the
worksheet?

Thanks for your help,

Mike Clemens

 

[Peter T]

Try -

AppActivate Application.Caption

There's also an API method to toggle focus between userform and application
that works even with a modal userform.

Regards,
Peter T