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

[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

[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!

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
'<<==============================


---
Regards,
Norman



"Mike" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[Mike]

Thanks much! I got it to work!

"Norman Jones" wrote:

> 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!
>
> 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
> '<<==============================
>
>
> ---
> Regards,
> Norman
>
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
>
>
>

[windsurferLA]

Norman Jones wrote:
> 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!
>
>
> 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
> '<<==============================
>
>
> ---
> Regards,
> Norman
>
>
>
> "Mike" <[email protected]> wrote in message
> news:[email protected]...
>
>>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
>>
>>
>>
>
>
>
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.

[windsurferLA]

windsurferLA wrote:
> Norman Jones wrote:
>
>> 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!
>>
>>
>>
>> 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
>> '<<==============================
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Mike" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>> 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
>>>
>>>
>>>
>>
>>
>>
> 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.
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