XL2002 - Sheet event BEFORE deactivating.

[Trevor Williams]

Hi All

Many moons ago I posted a message regarding triggering an event before the
sheet deactivates... I had 2 responses, one of which was from Peter T as
follows:

Private Sub Worksheet_Deactivate()
If Len(Range("A1")) = 0 Then ' empty cell A1
On Error GoTo errH:
' if necessary disable events
Application.EnableEvents = False
Me.Activate
Application.Goto Range("A1"), True
MsgBox "Complete cell A1"
End If
errH:
Application.EnableEvents = True
End Sub

This works well except all the sheets in my workbook have a
Worksheet_Activate event. The above code does reactivate the sheet if not
filled in correctly, but the code for the target sheet still runs, which then
gives errors.

My question is, can the worksheet_activate code for the target sheet be
switched off if the above code discovers a FALSE value on the original sheet?

I'm not using buttons to navigate the workbook so I can't capture it that way.

I hope thats clear(?)

Thanks in advance

Trevor Williams

 

[Peter T]

Hello again,

If you have the same code in all sheet modules might as put it in the
Thisworkbook module. Have a go with the following (comment or remove sheet
module code)

' ThisWorkbook module
Private mbExit As Boolean

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If mbExit Then
mbExit = False
Exit Sub
End If
' normal activate code
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

mbExit = False

If Len(Sh.Range("A1")) = 0 Then ' empty cell A1
On Error GoTo errH:
' if necessary disable events
Application.EnableEvents = False
Sh.Activate
Application.Goto Sh.Range("A1"), True
mbExit = True
MsgBox "Complete cell A1"
End If
errH:
Application.EnableEvents = True
End Sub

Not sure if I followed the objective corectly so test thoroughly, incl
similar code in 2+ workbooks and switching between them.

Regards,
Peter T

 

[Trevor Williams]

Hey Peter, good to hear from you again.

I'll implement, test, and report back today... finger crossed

Thanks

Trevor