S
Steve Drenker
PowerBook G4, 2 GB RAM, Excel 2004.
I want to set auto calculation to ON when I activate a worksheet and then
restore it to its original state when deactivating the worksheet. Very
simple, right?
Dim OrigCalc As Integer ' Global to save/restore state
Private Sub Worksheet_Activate()
OrigCalc = Application.Calculation
Application.Calculation = xlAutomatic
End Sub
Private Sub Worksheet_deactivate()
Application.Calculation = OrigCalc
End Sub
This worked fine several times. Then, all at once, the compiler refused to
recognize the "End Sub" in the deactivate routine even though it is right
there in plain sight ("Compiler Error: Expected End Sub").
1. I restarted Excel, but no change.
2. I rebooted the computer, but no change.
3. Erased and retyped the last "End Sub," no change.
4. Erased the entire last sub and used the VBA drop-down menus to insert a
brand new "Sub Worksheet_deactivate()" sub. Now the compiler cannot find the
"End Sub" for the first sub Worksheet_Activate().
Anybody else seen this flaky compiler behavior? Are there any solutions?
This is in a big model and I can't rebuild it from scratch if the file is
corrupt.
Steve
I want to set auto calculation to ON when I activate a worksheet and then
restore it to its original state when deactivating the worksheet. Very
simple, right?
Dim OrigCalc As Integer ' Global to save/restore state
Private Sub Worksheet_Activate()
OrigCalc = Application.Calculation
Application.Calculation = xlAutomatic
End Sub
Private Sub Worksheet_deactivate()
Application.Calculation = OrigCalc
End Sub
This worked fine several times. Then, all at once, the compiler refused to
recognize the "End Sub" in the deactivate routine even though it is right
there in plain sight ("Compiler Error: Expected End Sub").
1. I restarted Excel, but no change.
2. I rebooted the computer, but no change.
3. Erased and retyped the last "End Sub," no change.
4. Erased the entire last sub and used the VBA drop-down menus to insert a
brand new "Sub Worksheet_deactivate()" sub. Now the compiler cannot find the
"End Sub" for the first sub Worksheet_Activate().
Anybody else seen this flaky compiler behavior? Are there any solutions?
This is in a big model and I can't rebuild it from scratch if the file is
corrupt.
Steve