VBA Prevent User from leaving Worksheet (Worksheet_Deactivate)

[ExcelKnut]

I would like to prevent the user from leaving Sheet1 if certain conditions are not met.

I'm using the Worksheet_Deactivate event in the Sheet1 module. However, when the user attempts to leave Sheet1 by selecting Sheet2, the Sheet2 Worksheet_Activate event runs even though I have Application.EnableEvents set to False on the Sheet1 Worksheet_Deactivate event.

I hope that all makes sense. I've been trying to solve this for hours. Any help is GREATLY appreciated!!!

Jeff

 

[Yongle]

Keep things simple
A hidden sheet cannot be selected
To keep user on the sheet HIDE all other sheets
Make the sheets visible only when the conditions are met

an example for you (place code in SHEET module)
- here all other sheets are hidden unless both A1 contains a value and A2 contains today's date
- you could also make Worsheet

Private Sub Worksheet_Change(ByVal Target As Range)
    HideSheets
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    HideSheets
End Sub

Private Sub HideSheets()
    Dim ws As Worksheet
    If Range("A1") = "" Or Range("A2") <> Date Then
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> Me.Name Then ws.Visible = xlSheetVeryHidden
        Next
    Else
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next
    End If
End Sub