M
Mike Clemens
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.
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.