I have a Userform attached to two worksheets in a workbook. The
userform cannot be closed on those two sheets (it can be minimized)
and the userform hides properly when a different worksheet is
activated; however, if the workbook is open and one of the two
worksheets is active and the user creates a new workbook or opens a
different workbook the userform stays front and center on the screen
instead of hiding. What code do I need to write so that the userform
is only on the screen when the appropriate worksheet is in the
forefront of the screen?

Try this:

Private Sub Worksheet_Activate()
UserForm1.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
UserForm1.Hide
End Sub

This code goes in the sheet module for the sheet you need to display
the userform on. Replace "UserForm1" with the name of the actual
userform.

This code goes in the ThisWorkbook module for the workbook in
question.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
UserForm1.Hide
End Sub

Again, replace "UserForm1" with the name of the actual userform.

HTH,
JP

On Apr 16, 5:03*pm, wrote:
I have a Userform attached to two worksheets in a workbook. *The
userform cannot be closed on those two sheets (it can be minimized)
and the userform hides properly when a different worksheet is
activated; however, if the workbook is open and one of the two
worksheets is active and the user creates a new workbook or opens a
different workbook the userform stays front and center on the screen
instead of hiding. *What code do I need to write so that the userform
is only on the screen when the appropriate worksheet is in the
forefront of the screen?

On Apr 16, 5:18*pm, JP wrote:
Try this:

Private Sub Worksheet_Activate()
UserForm1.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
UserForm1.Hide
End Sub

This code goes in the sheet module for the sheet you need to display
the userform on. Replace "UserForm1" with the name of the actual
userform.

This code goes in the ThisWorkbook module for the workbook in
question.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
UserForm1.Hide
End Sub

Again, replace "UserForm1" with the name of the actual userform.

HTH,
JP

On Apr 16, 5:03*pm, wrote:



I have a Userform attached to two worksheets in a workbook. *The
userform cannot be closed on those two sheets (it can be minimized)
and the userform hides properly when a different worksheet is
activated; however, if the workbook is open and one of the two
worksheets is active and the user creates a new workbook or opens a
different workbook the userform stays front and center on the screen
instead of hiding. *What code do I need to write so that the userform
is only on the screen when the appropriate worksheet is in the
forefront of the screen?- Hide quoted text -

- Show quoted text -

I already have the code listed above in my workbook/worksheet and it
works perfectly when I click thru other sheets in that workbook. My
problem occurs when I create a completely new workbook, the form stays
in the forefront of the screen "floating" on top of the brand new
workbook instead of hiding behind it with the workbook/sheet it
belongs to.

I appreciate any and all help. Thanks!

How about this slight mod:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

If TypeName(Sh) = "Worksheet" Then
If Sh.Name < "My Worksheet" Then
UserForm1.Hide
End If
End If

End Sub

Just substitute "My Worksheet" for the name of the worksheet you want
to display the userform on.


--JP

On Apr 17, 10:49*am, wrote:
On Apr 16, 5:18*pm, JP wrote:





Try this:

Private Sub Worksheet_Activate()
UserForm1.Show vbModeless
End Sub

Private Sub Worksheet_Deactivate()
UserForm1.Hide
End Sub

This code goes in the sheet module for the sheet you need to display
the userform on. Replace "UserForm1" with the name of the actual
userform.

This code goes in the ThisWorkbook module for the workbook in
question.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
UserForm1.Hide
End Sub

Again, replace "UserForm1" with the name of the actual userform.

HTH,
JP

On Apr 16, 5:03*pm, wrote:

I have a Userform attached to two worksheets in a workbook. *The
userform cannot be closed on those two sheets (it can be minimized)
and the userform hides properly when a different worksheet is
activated; however, if the workbook is open and one of the two
worksheets is active and the user creates a new workbook or opens a
different workbook the userform stays front and center on the screen
instead of hiding. *What code do I need to write so that the userform
is only on the screen when the appropriate worksheet is in the
forefront of the screen?- Hide quoted text -

- Show quoted text -

I already have the code listed above in my workbook/worksheet and it
works perfectly when I click thru other sheets in that workbook. *My
problem occurs when I create a completely new workbook, the form stays
in the forefront of the screen "floating" on top of the brand new
workbook instead of hiding behind it with the workbook/sheet it
belongs to.

I appreciate any and all help. *Thanks!- Hide quoted text -

- Show quoted text -