Closing a userform when changing to another sheet

[Queenie Lai]

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

[#2 · Accepted solution]

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.