Toggle Calc Button - how to make it work OK at startup

M

Mike

Thanks for all the other help! I am trying to make a button that will be
depress/up if the Calculation is Off/On. I am able to make it work, but only
AFTER the button is pushed. I would like to add one more part where it does
changes the button to pressed/up at STARTUP, but I cannot get it to work. Any
ideas? This is greatly appreciated!

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


Sub ToggleApplicationCalculation()
On Error GoTo ErrorHandler
If Application.Calculation = xlManual Then
Application.Calculation = xlAutomatic
Application.CommandBars.ActionControl.State = msoButtonUp
MsgBox "Calculation toggled to Automatic."
Else
Application.Calculation = xlManual
Application.CalculateBeforeSave = True
Application.CommandBars.ActionControl.State = msoButtonDown
MsgBox "Calculation toggled to Manual."
End If
ErrorHandler:
End Sub 'ToggleApplicationCalculation
 
N

Norman Jones

Hi Mike,

Sorry, I missed this post earlier!
I would like to add one more part where it does
changes the button to pressed/up at STARTUP, but I cannot get it to work.
Any
ideas? This is greatly appreciated!
Click to expand...

In the Workbook_Open code, chamge

.State = msoButtonUp
to
.State = msoButtonDown

Incidentally, the code you show with this post has been superceded earlier
in the thread.

To avoid confusion the code should read:

'==============================>>
'\\ In the ThisWorkbook module
'----------------------------------

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

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

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

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

And:

'==============================>>
'\\ In a standard module
------------------------
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
'<<==============================
 
M

Mike

Thanks much! I got it to work!

Norman Jones said:
Hi Mike,

Sorry, I missed this post earlier!


In the Workbook_Open code, chamge

.State = msoButtonUp
to
.State = msoButtonDown

Incidentally, the code you show with this post has been superceded earlier
in the thread.

To avoid confusion the code should read:

'==============================>>
'\\ In the ThisWorkbook module
'----------------------------------

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

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

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

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

And:

'==============================>>
'\\ In a standard module
------------------------
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
'<<==============================
Click to expand...
 
W

windsurferLA

Norman said:
Hi Mike,

Sorry, I missed this post earlier!




In the Workbook_Open code, chamge

.State = msoButtonUp
to
.State = msoButtonDown

Incidentally, the code you show with this post has been superceded earlier
in the thread.

To avoid confusion the code should read:

'==============================>>
'\\ In the ThisWorkbook module
'----------------------------------

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

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

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

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

And:

'==============================>>
'\\ In a standard module
------------------------
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
'<<==============================
Click to expand...
This is a new question believed to be related to the subject of this post.

Can one do the following in Excel97? Can one create a command button
that toggles between two states. In one state it is either green or
appears "up", and in the other state it is either "red" or appears down.
Alternatively the text label on the button could change for "turn on" to
"turn off." The same button needs to be linked to two macros, one of
which it is activated when the associated function is UP, green or OFF
and the other when the associated function is DOWN, Red or ON.

When one creates a command button using Excel97, where is the
information stored that tells the program which macro is associated with
that command button? Where is the label text stored. I could not find
it among the macro modules.

thanks in advance to the group that seems to quickly come up with
answers to even my most esoteric questions.
 
W

windsurferLA

windsurferLA said:
This is a new question believed to be related to the subject of this post.

Can one do the following in Excel97? Can one create a command button
that toggles between two states. In one state it is either green or
appears "up", and in the other state it is either "red" or appears down.
Alternatively the text label on the button could change for "turn on" to
"turn off." The same button needs to be linked to two macros, one of
which it is activated when the associated function is UP, green or OFF
and the other when the associated function is DOWN, Red or ON.

When one creates a command button using Excel97, where is the
information stored that tells the program which macro is associated with
that command button? Where is the label text stored. I could not find
it among the macro modules.

thanks in advance to the group that seems to quickly come up with
answers to even my most esoteric questions.
Click to expand...
ALERT .. I think I've found an answer to my own question. It was just a
matter of knowing what to call things. I now see that there is an option
for a "toggle button" and that one can change the State, Caption and
other features with VBA code such as ToggleButton1.Value = False
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Toggle Calc button - working, but want it showing "depressed" 3
Adding .xla button for Toggle Calculation Button 5
Can't get Control.State to work 15
How to maintain size of array: UniqRow 4
CommandCombo BOX want to populate all named ranges in it 1
Reset Custom Button on toolbar 1
Add "paste values" to right-click shortcut menu-how do? Excel 2010 1
VBE tricky question 2

Top