Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle Calculation Status
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle Calculation Status
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 "Paige" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle Calculation Status
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. "Paige" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle Calculation Status
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 -- HTH... Jim Thomlinson "Paige" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle Calculation Status
Wow! Thanks for all the quick responses. I will try them shortly and let
you know how they work. Thanks again.... "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Paige" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle Calculation Status
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. "Paige" wrote: Wow! Thanks for all the quick responses. I will try them shortly and let you know how they work. Thanks again.... "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Paige" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I toggle in and out of read-only status in an Excel file? | Excel Discussion (Misc queries) | |||
Returning calculation status | Excel Worksheet Functions | |||
Status Bar with ETA calculation | Excel Programming | |||
Toggle calculation | Excel Programming | |||
Check calculation status | Excel Programming |