Anchor userform to specific worksheet

drluke

Active Member
Joined
Apr 17, 2014
Messages
311
Office Version
  1. 365
Platform
  1. Windows
I have a Userform that is opened by worksheet change event:
VBA Code:
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:
VBA Code:
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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:
VBA Code:
Option Explicit

Public MyForm As Object

Public Sub DisplayForm()

    Set MyForm = New UserForm1
    MyForm.Show vbModeless

End Sub


This goes in the ThisWorkbook module:
VBA Code:
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
 
Solution

drluke

Active Member
Joined
Apr 17, 2014
Messages
311
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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:
VBA Code:
Option Explicit

Public MyForm As Object

Public Sub DisplayForm()

    Set MyForm = New UserForm1
    MyForm.Show vbModeless

End Sub


This goes in the ThisWorkbook module:
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,564
Messages
5,770,886
Members
425,649
Latest member
cbTexas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top