Anchor userform to specific worksheet

[drluke]

I have a Userform that is opened by worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("E4")) Is Nothing Then
        Load frmActions
            frmActions.Show
                End If
End Sub

The form has various checkboxes in it, each linked to a specific macro depending on the activity being selected via checkbox. I want my userform to remain open and in the worksheet whilst other sheets are selected, as users may want to return to the form and select another checkbox. I cannot manage to do that. The userform does remain open but it shows on any other sheet that is selected (almost like a floating menu). I have tried this code, but with no success:

Private Sub Worksheet_SheetActivate(ByVal Sh As Object)

    If Sh.Name <> "Actions" Then
        Unload frmActions
                End If

    If Sh.Name = "Actions" Then
         frmActions.Show
               End If
End Sub

Any advice greatly appreciated.

 

[GWteB]

I would like to advise you not to use any of the worksheet event handlers you mentioned directly to place your userform on the screen. When it's done indirectly then there's no problem.
Btw, an event handler with the procedure name

Private Sub Worksheet_SheetActivate(ByVal Sh As Object)

does not exist.

Display your userform preferably by means of code placed in a standard module. Show your userform modeless, so that users can select another worksheet while the userform is still on the screen. The appropriate event handler then may decide whether your userform has to stay visible or not.
In addition, preferably do not use the default instance of the userform but NEW your form, so you're more flexible in making happen what you want. This way you're also able to have future code to interact with your form. Of course you have to write the necessary code for that yourself.
There is much more to note in this regard, but some things are better to discover for yourself.

This goes in a standard module:

Option Explicit

Public MyForm As Object

Public Sub DisplayForm()

    Set MyForm = New UserForm1
    MyForm.Show vbModeless

End Sub

This goes in the ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If StrComp(Sh.Name, "Actions", vbTextCompare) = 0 Then
        If Not MyForm Is Nothing Then
            MyForm.Show vbModeless
        Else
            DisplayForm
        End If
    Else
        If Not MyForm Is Nothing Then
            MyForm.Hide
        End If
    Else
End Sub

 

[drluke]

I would like to advise you not to use any of the worksheet event handlers you mentioned directly to place your userform on the screen. When it's done indirectly then there's no problem.
Btw, an event handler with the procedure name

Private Sub Worksheet_SheetActivate(ByVal Sh As Object)

does not exist.

Display your userform preferably by means of code placed in a standard module. Show your userform modeless, so that users can select another worksheet while the userform is still on the screen. The appropriate event handler then may decide whether your userform has to stay visible or not.
In addition, preferably do not use the default instance of the userform but NEW your form, so you're more flexible in making happen what you want. This way you're also able to have future code to interact with your form. Of course you have to write the necessary code for that yourself.
There is much more to note in this regard, but some things are better to discover for yourself.

This goes in a standard module:

Option Explicit

Public MyForm As Object

Public Sub DisplayForm()

    Set MyForm = New UserForm1
    MyForm.Show vbModeless

End Sub

This goes in the ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If StrComp(Sh.Name, "Actions", vbTextCompare) = 0 Then
        If Not MyForm Is Nothing Then
            MyForm.Show vbModeless
        Else
            DisplayForm
        End If
    Else
        If Not MyForm Is Nothing Then
            MyForm.Hide
        End If
    Else
End Sub

Thank you GWteB! This is amazing.

 

[GWteB]

You are welcome and thanks for the feedback.