Forum Discussion

Queenie Lai's avatar
Queenie Lai
Copper Contributor
Jun 07, 2018

Closing a userform when changing to another sheet

Hi,

I am trying to close a userform when I switch to other sheets in the same workbook. I just want the userform open when the active sheet called 'Tenants" is selected. 

I use the following codes but it is not working:

Sub Workbook_closeUserform()
If ActiveSheet.Name <> "Tenants" And Userform1.Visible Then
Unload Userform1
End If
End Sub

 

Please help. Thank you.

 

Queenie

Macros & VBA
Need Help
  • Haytham Amairah's avatar
    Haytham Amairah
    Jun 08, 2018

    Hi,

     

    Please copy the below code events into the worksheet code area:

    Private Sub Worksheet_Activate()
        UserForm1.Show vbModal = 0
    End Sub

    Private Sub Worksheet_Deactivate()
        Unload UserForm1
    End Sub

     And don't forget to change the default name of the UserForm in the codes to your existing UserForm name.

     

    Please note the below screenshot for more illustration and find all this in the attached file.

     

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Jun 08, 2018

    Hi,

     

    Please copy the below code events into the worksheet code area:

    Private Sub Worksheet_Activate()
        UserForm1.Show vbModal = 0
    End Sub

    Private Sub Worksheet_Deactivate()
        Unload UserForm1
    End Sub

     And don't forget to change the default name of the UserForm in the codes to your existing UserForm name.

     

    Please note the below screenshot for more illustration and find all this in the attached file.

     

     

Resources