Trigger VBA code when Worksheet_Activate event occurs - doesn't work if multiple views are active

user_11392765 0
Viewed 39

I want to suspend calculations when a certain worksheet is activated and then reset calculation to whatever it was originally set to when the sheet is deactivated. It works fine if I only have one view open. But if I have two (or more) Windows open in the same workbook (View/New Window), the Worksheet_Activate and Worksheet_Deactivate events do not trigger when you move between views, so calculations remain set to manual when the user moves to the alternate view. I did notice the Workbook_WindowActivate and Workbook_WindowDeactivate events, but can't quite make that work either. The code below is in the worksheet module. I need a "Worksheet_GotFocus" event, or for the Worksheet_Activate event to trigger when the View is activated. Or perhaps I can hang the code off the Selection_Change event so that as soon as the user clicks within the sheet in question, iot triggers the code, but not sure how to handle the deactivation in that case. Any ideas anyone?

Option Explicit
Dim vCalc As Variant

Private Sub Worksheet_Activate()
    'save auto calc setting - suspend auto calcs
    vCalc = Application.Calculation
    If Application.Calculation <> xlCalculationManual Then _ 
        Application.Calculation = xlCalculationManual

End Sub

Private Sub Worksheet_Deactivate()
    'force calculation of UDF's (leave related)
    If Range("rfRLeaveChanged") = "X" Then
        'blah blah blah
    End If
    'reset auto calc (if applicable)
    If Application.Calculation <> vCalc Then _
        Application.Calculation = vCalc

End Sub
vba excel
1 Answers

OK,I figured it out. I have to trigger the code in both the sheet and the ThisWorkbook modules. In ThisWorkbook I trigger it on the Workbook_WindowActivate event. If Windows.Count > 1 and the active sheet is the sheet in question, I change calculations to manual, or for any other sheet I reset calculations to the users previous setting.

user_113927650
Related