Toggle Calculation Status

[Guest]

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

 

[Guest]

The focus is probably not on the active page.
After opening the file you need to perform a worksheets(abc).activate

Excel has lots of problems with the focus not being where you wnat it to be.

 

[Guest]

Instead of altering a cell in the spreadsheet why not change the button
caption. That gets you around the whole sheet protection thing. Something
like this...

Private Sub ToggleButton1_Click()
If Application.Calculation = xlCalculationAutomatic Then
ToggleButton1.Caption = "Manual"
Application.Calculation = xlCalculationManual
Else
ToggleButton1.Caption = "Automatic"
Application.Calculation = xlCalculationAutomatic
End If
End Sub

You will want to ensure that the caption and calc mode are correct on open
something like this... (Place in ThisWorkbook)...

Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
Sheet1.ToggleButton1.Caption = "Automatic"
End Sub

 

[Guest]

Wow! Thanks for all the quick responses. I will try them shortly and let
you know how they work. Thanks again....

 

[Guest]

Tried them out; ended up going with Jim's idea so that the verbiage would
show on the button versus separately. Thanks again for the great feedback;
will keep it all for future reference/use.