ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Toggle Calc button - working, but want it showing "depressed" (https://www.excelbanter.com/excel-programming/337693-toggle-calc-button-working-but-want-showing-depressed.html)

Mike
Toggle Calc button - working, but want it showing "depressed"
 
Thanks for all help. I was able to get the Toggle Calc .xla add-in working
with the following code. However, I can't figure out how to change this macro
to be "depressed" when Calc is ON, and "pushed up" when the Calc is OFF. Any
help? I cannot get msoButtonUp, msoButtonDown, and .State to work at all.
Thanks!

**** IN THE MODULE'S VBA ****
Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation


**** IN THISWORKBOOK'S VBA ****

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0

With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.OnAction = "ToggleApplicationCalculation"
End With
End With

End Sub

Norman Jones
Toggle Calc button - working, but want it showing "depressed"
 
Hi Mike,

The following worked for me:

'===============================
Sub ToggleApplicationCalculation()

With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
.State = msoButtonDown
Else
.State = msoButtonUp
End If
End With

On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
'<<===============================

'===============================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
'<<===============================

===============================
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0

With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.State = msoButtonUp
.OnAction = "ToggleApplicationCalculation"
End With
End With

End Sub
'<<===============================


---
Regards,
Norman



"Mike" wrote in message
...
Thanks for all help. I was able to get the Toggle Calc .xla add-in working
with the following code. However, I can't figure out how to change this
macro
to be "depressed" when Calc is ON, and "pushed up" when the Calc is OFF.
Any
help? I cannot get msoButtonUp, msoButtonDown, and .State to work at all.
Thanks!

**** IN THE MODULE'S VBA ****
Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation


**** IN THISWORKBOOK'S VBA ****

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0

With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.OnAction = "ToggleApplicationCalculation"
End With
End With

End Sub

Mike
Toggle Calc button - working, but want it showing "depressed"
 
This worked great. One last request -- is there a way to get this to work
when Excel is first opened? I tried several things, but not working. I am
wondering if this is because it happens before Excel worksheets are open.

"Norman Jones" wrote:

Hi Mike,

The following worked for me:

'===============================
Sub ToggleApplicationCalculation()

With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
.State = msoButtonDown
Else
.State = msoButtonUp
End If
End With

On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
'<<===============================

'===============================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
'<<===============================

===============================
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0

With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.State = msoButtonUp
.OnAction = "ToggleApplicationCalculation"
End With
End With

End Sub
'<<===============================


---
Regards,
Norman



"Mike" wrote in message
...
Thanks for all help. I was able to get the Toggle Calc .xla add-in working
with the following code. However, I can't figure out how to change this
macro
to be "depressed" when Calc is ON, and "pushed up" when the Calc is OFF.
Any
help? I cannot get msoButtonUp, msoButtonDown, and .State to work at all.
Thanks!

**** IN THE MODULE'S VBA ****
Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation


**** IN THISWORKBOOK'S VBA ****

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0

With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.OnAction = "ToggleApplicationCalculation"
End With
End With

End Sub

Norman Jones
Toggle Calc button - working, but want it showing "depressed"
 
Hi Mike,

You need to correct two errors in your original code.

In both the workbook_Open and Workbook_BeforeClose procedures, changE:

Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete

to:

Application.CommandBars("Standard").Controls( _
"CalculateToggle").Delete


(The control's caption was incorrect)

With these amendments, the toolbar button is added when the workbook opens
and is removed when the workbook is closed.

---
Regards,
Norman



"Mike" wrote in message
...
This worked great. One last request -- is there a way to get this to work
when Excel is first opened? I tried several things, but not working. I am
wondering if this is because it happens before Excel worksheets are open.

"Norman Jones" wrote:

Hi Mike,

The following worked for me:

'===============================
Sub ToggleApplicationCalculation()

With Application.CommandBars.ActionControl
If .State = msoButtonUp Then
.State = msoButtonDown
Else
.State = msoButtonUp
End If
End With

On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
'<<===============================

'===============================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub
'<<===============================

===============================
Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0

With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.State = msoButtonUp
.OnAction = "ToggleApplicationCalculation"
End With
End With

End Sub
'<<===============================


---
Regards,
Norman



"Mike" wrote in message
...
Thanks for all help. I was able to get the Toggle Calc .xla add-in
working
with the following code. However, I can't figure out how to change this
macro
to be "depressed" when Calc is ON, and "pushed up" when the Calc is
OFF.
Any
help? I cannot get msoButtonUp, msoButtonDown, and .State to work at
all.
Thanks!

**** IN THE MODULE'S VBA ****
Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation


**** IN THISWORKBOOK'S VBA ****

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Standard").Controls( _
"ToggleApplicationCalculation").Delete
On Error GoTo 0

With Application.CommandBars("Standard")
With .Controls.Add(Temporary:=True)
.BeginGroup = True
.Style = msoButtonIcon
.FaceId = 283
.Caption = "CalculateToggle"
.OnAction = "ToggleApplicationCalculation"
End With
End With

End Sub


All times are GMT +1. The time now is 04:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com