Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Toggle Calc button - working, but want it showing "depressed"
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,101
Default 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





Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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







Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,101
Default 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








Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
where is tthe "read only" TOGGLE button ? Kee Excel Worksheet Functions 1 February 18th 10 04:39 PM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
My calc key on Excel changes box to "Text" Box and doesn't calc ? jack Charts and Charting in Excel 0 August 8th 06 07:30 PM
"Toggle total row" button not enabled in Excel 2003 dominic_howden Excel Discussion (Misc queries) 0 April 12th 06 09:21 AM
Working out age from "Day" "Month" "Year" timmyc Excel Worksheet Functions 4 February 5th 06 03:07 PM


All times are GMT +1. The time now is 01:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement