Prevent navigation off worksheet

[tiredofit]

My workbook contains two worksheets, Sheet1 and Sheet2.

Sheet2 contains a Worksheet_Activate event.

Sheet1 contains a Worksheet_Deactivate event.

If the active sheet is Sheet1 and the user attempts to navigate to Sheet2, the Worksheet_Deactivate events kicks off.

If a certain condition is not met, I want the user to remain on Sheet1.

What I have found is even if I disable events, like:

Application.EnableEvents = False

Me.Activate

Application.EnableEvents = True

the moment events are enabled, the Worksheet_Activate event on Sheet2 is kicked off.

How can I prevent that?

I suppose I could set up a global variable, such that if the condition is not met, this global variable is set to false and then in Sheet2, add this:

Private Sub Worksheet_Activate()

    If GlobalVar = True then

        ' some code

    End If

End Sub

Thanks

 

[GWteB]

You are thinking in the right direction. Try the code below and see if this is what you mean.
The condition is any value in cell A1 of worksheet Sheet1. If this condition is met, worksheet Sheet2 cannot be permanently activated. If cell A1 is cleared or deleted, worksheet Sheet2 can be permanently activated again.

This goes in the ThisWorkbook module:

Option Explicit

Private Type TThisWbLocals
    Condition   As Boolean
    Msg         As String
    ShtPrevious As Worksheet
    ShtBlocked  As Worksheet
End Type
Private this As TThisWbLocals

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If this.Condition Then
        If Not this.ShtBlocked Is Nothing Then
            If Sh.Name = this.ShtBlocked.Name Then
                If Not this.ShtPrevious Is Nothing Then
                    Application.EnableEvents = False
                    this.ShtPrevious.Activate
                    Application.EnableEvents = True
                End If
            End If
        End If
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    GetCondition
    Set this.ShtPrevious = Sh
End Sub

Private Sub GetCondition()
    this.Condition = CBool(Me.Sheets("Sheet1").Range("A1").Value <> vbNullString)
End Sub

Public Sub EnableSheets()
    Set this.ShtBlocked = Nothing
End Sub

Public Sub DisableSheet(ByVal argSht As Worksheet)
    Set this.ShtBlocked = argSht
End Sub

This goes in a standard module:

Public Sub Example()
    ThisWorkbook.DisableSheet ThisWorkbook.Sheets("Sheet2")
End Sub

Public Sub Unblock()
    ThisWorkbook.EnableSheets
End Sub

 

[tiredofit]

You are thinking in the right direction. Try the code below and see if this is what you mean.
The condition is any value in cell A1 of worksheet Sheet1. If this condition is met, worksheet Sheet2 cannot be permanently activated. If cell A1 is cleared or deleted, worksheet Sheet2 can be permanently activated again.

This goes in the ThisWorkbook module:

Option Explicit

Private Type TThisWbLocals
    Condition   As Boolean
    Msg         As String
    ShtPrevious As Worksheet
    ShtBlocked  As Worksheet
End Type
Private this As TThisWbLocals

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If this.Condition Then
        If Not this.ShtBlocked Is Nothing Then
            If Sh.Name = this.ShtBlocked.Name Then
                If Not this.ShtPrevious Is Nothing Then
                    Application.EnableEvents = False
                    this.ShtPrevious.Activate
                    Application.EnableEvents = True
                End If
            End If
        End If
    End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    GetCondition
    Set this.ShtPrevious = Sh
End Sub

Private Sub GetCondition()
    this.Condition = CBool(Me.Sheets("Sheet1").Range("A1").Value <> vbNullString)
End Sub

Public Sub EnableSheets()
    Set this.ShtBlocked = Nothing
End Sub

Public Sub DisableSheet(ByVal argSht As Worksheet)
    Set this.ShtBlocked = argSht
End Sub

This goes in a standard module:

Public Sub Example()
    ThisWorkbook.DisableSheet ThisWorkbook.Sheets("Sheet2")
End Sub

Public Sub Unblock()
    ThisWorkbook.EnableSheets
End Sub

Thanks for your detailed version.

Mine is more simple, as follows:

' Standard Module

Public MyFlag As ClsSettings


' Thisworkbook

Private Sub Workbook_Open()

    Set MyFlag = New ClsSettings

    MyFlag.Flag = False
    
End Sub


' ClsSettings

Option Explicit

    Private pFlag As Boolean

Public Property Get Flag() As Boolean

    Flag= pFlag
    
End Property

Public Property Let Flag(ByVal F As Boolean)

    pFlag= F
    
End Property


' Sheet2

Private Sub Worksheet_Activate()
    
    If Not MyFlag.Flag Then
    
        ' do something
    
    End If
    
End Sub


' Sheet1

Private Sub Worksheet_Deactivate()
    
    MyFlag.Flag = False
    
    If Me.Cells(1, 1).Value = "FAIL" Then

        With Application

            .EnableEvents = False

             Me.Activate

            .EnableEvents = True

        End With

        MyFlag.Flag = False

    End If

End Sub

 

[GWteB]

You're welcome. There are many ways to get the same result. Glad it's sorted