Stopping Deactivation of a Worksheet

[Guest]

When I leave Worksheet A for Worksheet B, the Deactivate Event for Worksheet
A does a calculation to see if a total on Worksheet A has changed. If it
has, I would like to cancel the deactivation of Worksheet A and still have it
my active worksheet. Is there some way to accomplish this in the Worksheet A
Deactivate Event? Or somewhere else?

 

[Doug Glancy]

Bill,

As far as I know, you can't cancel the deactivate event. I'm assuming you
don't just want to reactivate A, and that you have B activate code that you
want to skip. If so, I think you should use a module-level variable, set in
Sheet Activate as to whether the total was changed, like this maybe:

Dim Sheet1_deactivate_cancel As Boolean 'module-level

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Sheet1_deactivate_cancel = False
If Sh.Name = "Sheet1" Then
If Sh.Range("A1") = "no" Then replace with your changed_total check
Sheet1_deactivate_cancel = True
Me.Activate
End If
End If
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet2" And Not Sheet1_deactivate_cancel Then
MsgBox "Sheet2 activated" 'replace with the code you want skipped if A
total changed
End If
End Sub

If you just want to return to sheet A, and there is no sheet B activation
code to skip, then I think you only need this:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Sheet1" And Sh.Range("A1") = "no" Then 'substitute your
changed_total check here
Me.Activate
End If
End Sub

hth,

Doug