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

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

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

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

"Mike Clemens" wrote in message
...
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