Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping Deactivation of a Worksheet
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? -- Bill @ UAMS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping Deactivation of a Worksheet
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 "BillCPA" <Bill @ UAMS wrote in message ... 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? -- Bill @ UAMS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stopping Auto calculation in Part of a Worksheet | Excel Worksheet Functions | |||
Starting/Stopping Worksheet Event Code | Excel Discussion (Misc queries) | |||
Ms-Query stopping | Excel Programming | |||
Worksheet Deactivation Event | Excel Programming | |||
Prevent deactivation of sheet | Excel Programming |