Toggle Calculation Status

[Paige]

The purpose of the following code is to allow the user to toggle calculation
back and forth between auto and manual. When they click on the button, Cell
K20 says either automatic or manual, so they know what they changed it to.
Problem is that each time they open the file, when they click on the button
for the first time, Cell K20 doesn't update; they have to click on the button
again in order for K20 to update to the correct status. I can't seem to
figure out what I'm doing wrong. If someone has an idea, please enlighten
me! Thanks....Paige

Private Sub ToggleButton1_Click()
If ToggleButton1 = True Then
Application.Calculation = xlCalculationAutomatic
ActiveSheet.Unprotect Password:="xxxx"
Range("K20").Value = "AUTOMATIC"
Range("E9").Select
ActiveSheet.Protect Password:="xxxx", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Exit Sub
Else
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect Password:="xxxx"
Range("K20").Value = "MANUAL"
Range("E9").Select
ActiveSheet.Protect Password:="xxxx", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Exit Sub
End If
End Sub

 

[Bernie Deitrick]

Paige,

Change your first conditional:

Private Sub ToggleButton1_Click()
If Range("K20").Value = "MANUAL" Then
Application.Calculation = xlCalculationAutomatic
ActiveSheet.Unprotect Password:="xxxx"
Range("K20").Value = "AUTOMATIC"
Range("E9").Select
ActiveSheet.Protect Password:="xxxx", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Exit Sub
Else
Application.Calculation = xlCalculationManual
ActiveSheet.Unprotect Password:="xxxx"
Range("K20").Value = "MANUAL"
Range("E9").Select
ActiveSheet.Protect Password:="xxxx", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Exit Sub
End If
End Sub

HTH,
Bernie
MS Excel MVP