I have a userform named FunctionButtons with showmodal set to true that is
displayed automatically by the workbook_open event procedure with the following
code:

VBA.UserForms.Add(FunctionButtons.Name).Show

The form is displayed on the first and only worksheet in the workbook when the
workbook is opened.

One of the macros (accessed by clicking one of the button on the FunctionButtons
form) adds a new worksheet and activates the new worksheet.

I don't want the FunctionButtons form displayed on the second worksheet so I
added the following code to the first worksheet.

Private Sub Worksheet_Activate()
FunctionButtons.Show
End Sub

Private Sub Worksheet_Deactivate()
FunctionButtons.Hide
End Sub

I know these routines are executed at the right times from placing breakpoints
in them.

Sometimes with the second worksheet is added and displayed the FunctionButtons
form is still displayed. When this happens going back to the first worksheet
causes a second copy of the form to be displayed. Getting the second copy of
the form only happens once regardless of the number of times you switch back and
forth between the first and second worksheets. If it happens it is always the
first time switching back to the first worksheet.

Other times everything appears to work correctly - no form on the second
worksheet and no second copy of the form on the first worksheet.

One other thing I found - when the hide/show stuff works the following line of
code does not work.

AppActivate ActiveWorkbook.Windows(1).Caption

When the hide/show stuff does not work the same lime of code works fine.

The error generated when the line of code doesn't work is "Invalid procedure
call or argument".

Any ideas as to what is going on? I've spent most of the day trying to figure
this out without an success.

Thanks for your help.

I changed the code in the workbook_open event procedure from

VBA.UserForms.Add(FunctionButtons.Name).Show

to

FunctionButtons.Show

and the problem is gone.

So what's the difference between the 2 ways of showing a form?

Mike Clemens wrote:
I have a userform named FunctionButtons with showmodal set to true that
is displayed automatically by the workbook_open event procedure with the
following code:

VBA.UserForms.Add(FunctionButtons.Name).Show

The form is displayed on the first and only worksheet in the workbook
when the workbook is opened.

One of the macros (accessed by clicking one of the button on the
FunctionButtons form) adds a new worksheet and activates the new
worksheet.

I don't want the FunctionButtons form displayed on the second worksheet
so I added the following code to the first worksheet.

Private Sub Worksheet_Activate()
FunctionButtons.Show
End Sub

Private Sub Worksheet_Deactivate()
FunctionButtons.Hide
End Sub

I know these routines are executed at the right times from placing
breakpoints in them.

Sometimes with the second worksheet is added and displayed the
FunctionButtons form is still displayed. When this happens going back
to the first worksheet causes a second copy of the form to be
displayed. Getting the second copy of the form only happens once
regardless of the number of times you switch back and forth between the
first and second worksheets. If it happens it is always the first time
switching back to the first worksheet.

Other times everything appears to work correctly - no form on the second
worksheet and no second copy of the form on the first worksheet.

One other thing I found - when the hide/show stuff works the following
line of code does not work.

AppActivate ActiveWorkbook.Windows(1).Caption

When the hide/show stuff does not work the same lime of code works fine.

The error generated when the line of code doesn't work is "Invalid
procedure call or argument".

Any ideas as to what is going on? I've spent most of the day trying to
figure this out without an success.

Thanks for your help.