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

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" wrote in message
...
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

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" wrote in message
...
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 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" wrote in message
...

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 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" wrote in message
...

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