Need to create a command button that when first created (when a workbook is
opened), it checks the calculation state and if calc is set to auto, the
button caption will say "AUTO"; if calc is set to manual, the button caption
will say "MANUAL". Then the user should be able to hit the button to toggle
back and forth between auto and manual calculation, with the button caption
changing appropriately each time. I only know the basics of creating a
button....and have not been able to figure this out. Can someone help me
with this please?

Is this what you seek?

Private Sub CommandButton1_Click()
If Application.Calculation = xlCalculationAutomatic Then
CommandButton1.Caption = "AUTO"
ElseIf Application.Calculation = xlCalculationManual Then
CommandButton1.Caption = "MANUAL"
End If
End Sub

Dan

On Oct 26, 1:26 pm, Paige <Pa...@discussions.microsoft.com> wrote:
> Need to create a command button that when first created (when a workbook is
> opened), it checks the calculation state and if calc is set to auto, the
> button caption will say "AUTO"; if calc is set to manual, the button caption
> will say "MANUAL". Then the user should be able to hit the button to toggle
> back and forth between auto and manual calculation, with the button caption
> changing appropriately each time. I only know the basics of creating a
> button....and have not been able to figure this out. Can someone help me
> with this please?

Woops,

I didn't read your whole question.

Try this instead:

Private Sub CommandButton1_Click()
If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
CommandButton1.Caption = "AUTO"
ElseIf Application.Calculation = xlCalculationManual Then
CommandButton1.Caption = "MANUAL"
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Dan

Thanks, Dan, that's definitely half of it. I still need to develop a sub for
Private Sub Workbook_Open() that creates the button, checks the calculation
state when the workbook is opened, and titles the button to whatever the calc
state is, and then tie the toggling of that button to the code below. Can
you advise on that also?

"dan dungan" wrote:

> Woops,
>
> I didn't read your whole question.
>
> Try this instead:
>
> Private Sub CommandButton1_Click()
> If Application.Calculation = xlCalculationAutomatic Then
> Application.Calculation = xlCalculationManual
> CommandButton1.Caption = "AUTO"
> ElseIf Application.Calculation = xlCalculationManual Then
> CommandButton1.Caption = "MANUAL"
> Application.Calculation = xlCalculationAutomatic
> End If
> End Sub
>
> Dan
>
>

These two event procedures should do what you asked for...

Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationAutomatic + _
xlCalculationManual - _
Application.Calculation
CommandButton1.Caption = IIf(Application.Calculation = _
xlCalculationManual, "MANUAL", "AUTO")
End Sub

Private Sub Worksheet_Activate()
CommandButton1.Caption = IIf(Application.Calculation = _
xlCalculationManual, "MANUAL", "AUTO")
End Sub

Rick

> Need to create a command button that when first created (when a workbook
> is
> opened), it checks the calculation state and if calc is set to auto, the
> button caption will say "AUTO"; if calc is set to manual, the button
> caption
> will say "MANUAL". Then the user should be able to hit the button to
> toggle
> back and forth between auto and manual calculation, with the button
> caption
> changing appropriately each time. I only know the basics of creating a
> button....and have not been able to figure this out. Can someone help me
> with this please?

Hi Paige,

Please help me understand why you want to create the button on open.

This seems that would just slow the opening of the workbook.

Dan

On Oct 26, 2:27 pm, Paige <Pa...@discussions.microsoft.com> wrote:
> Thanks, Dan, that's definitely half of it. I still need to develop a sub for
> Private Sub Workbook_Open() that creates the button, checks the calculation
> state when the workbook is opened, and titles the button to whatever the calc
> state is, and then tie the toggling of that button to the code below. Can
> you advise on that also?
>
> "dan dungan" wrote:
> > Woops,
>
> > I didn't read your whole question.
>
> > Try this instead:
>
> > Private Sub CommandButton1_Click()
> > If Application.Calculation = xlCalculationAutomatic Then
> > Application.Calculation = xlCalculationManual
> > CommandButton1.Caption = "AUTO"
> > ElseIf Application.Calculation = xlCalculationManual Then
> > CommandButton1.Caption = "MANUAL"
> > Application.Calculation = xlCalculationAutomatic
> > End If
> > End Sub
>
> > Dan

For a workbook that is complex and has lots of formulas; I want the users to
be able to turn calc on/off more readily, and to be able to turn calc off
PIOR to opening one of these workbooks if they so desire, so was looking for
code to add to Personal.xls that when Excel launched, it would create the
button for them to use. Does that make sense?

"dan dungan" wrote:

> Hi Paige,
>
> Please help me understand why you want to create the button on open.
>
> This seems that would just slow the opening of the workbook.
>
> Dan
>
> On Oct 26, 2:27 pm, Paige <Pa...@discussions.microsoft.com> wrote:
> > Thanks, Dan, that's definitely half of it. I still need to develop a sub for
> > Private Sub Workbook_Open() that creates the button, checks the calculation
> > state when the workbook is opened, and titles the button to whatever the calc
> > state is, and then tie the toggling of that button to the code below. Can
> > you advise on that also?
> >
> > "dan dungan" wrote:
> > > Woops,
> >
> > > I didn't read your whole question.
> >
> > > Try this instead:
> >
> > > Private Sub CommandButton1_Click()
> > > If Application.Calculation = xlCalculationAutomatic Then
> > > Application.Calculation = xlCalculationManual
> > > CommandButton1.Caption = "AUTO"
> > > ElseIf Application.Calculation = xlCalculationManual Then
> > > CommandButton1.Caption = "MANUAL"
> > > Application.Calculation = xlCalculationAutomatic
> > > End If
> > > End Sub
> >
> > > Dan
>
>
>

Hi Rick,

I'm not sure if I should ask these questions here or start a new
thread.

However, I'm curious about the syntax.

I notice your use of + and -.

I've never seen that before.

Please describe what that does.
Is that convention useful only for constants?

Thanks,

Dan


On Oct 26, 2:32 pm, "Rick Rothstein \(MVP - VB\)"
<rickNOSPAMn...@NOSPAMcomcast.net> wrote:
> These two event procedures should do what you asked for...
>
> Private Sub CommandButton1_Click()
> Application.Calculation = xlCalculationAutomatic + _
> xlCalculationManual - _
> Application.Calculation
> CommandButton1.Caption = IIf(Application.Calculation = _
> xlCalculationManual, "MANUAL", "AUTO")
> End Sub
>

Thanks, Rick; they work great - but I want the command button to be on the
Excel toolbar (and not in a worksheet), and I don't know how to create the
toolbar button and link it to these macros. Any advice on that also would be
appreciated!

"Rick Rothstein (MVP - VB)" wrote:

> These two event procedures should do what you asked for...
>
> Private Sub CommandButton1_Click()
> Application.Calculation = xlCalculationAutomatic + _
> xlCalculationManual - _
> Application.Calculation
> CommandButton1.Caption = IIf(Application.Calculation = _
> xlCalculationManual, "MANUAL", "AUTO")
> End Sub
>
> Private Sub Worksheet_Activate()
> CommandButton1.Caption = IIf(Application.Calculation = _
> xlCalculationManual, "MANUAL", "AUTO")
> End Sub
>
> Rick
>
> > Need to create a command button that when first created (when a workbook
> > is
> > opened), it checks the calculation state and if calc is set to auto, the
> > button caption will say "AUTO"; if calc is set to manual, the button
> > caption
> > will say "MANUAL". Then the user should be able to hit the button to
> > toggle
> > back and forth between auto and manual calculation, with the button
> > caption
> > changing appropriately each time. I only know the basics of creating a
> > button....and have not been able to figure this out. Can someone help me
> > with this please?
>
>

Let me show you what I am doing with simpler named variables (it will be
easier to follow). Let's say a variable can only have two possible value.. 5
and 9 (just to pick two numbers at random). To match what is going on in my
code, let's use constants to hold these values... say A=5 and B=9. Now
consider a variable (named V for this example) that you want to toggle back
and forth between the value in A and the value in B. My code line does
this...

V = A + B - V

So, if the current condition is V=A, executing the above line will set V
equal to B (we are subtracting A from A+B leaving B). Execute the line again
and V will become A again (now, we are subtracting B from A+B leaving A),
and so forth. Each time the code line is executed, it toggles V back and
forth between A and B. So, the equivalence between my previously posted code
and the code line above is this....

V = Application.Calculation
A = xlCalculationAutomatic
B = xlCalculationManual

Now, for this to work, V must be initialized with one of the values A or B.
We can do that in code, if necessary, but in the case of the calculation
mode, the "variable" Application.Calculation is already pre-initialized for
us and the constants xlCalculationManual and xlCalculationAutomatic are
predefined by VBA.

Rick


> I'm not sure if I should ask these questions here or start a new
> thread.
>
> However, I'm curious about the syntax.
>
> I notice your use of + and -.
>
> I've never seen that before.
>
> Please describe what that does.
> Is that convention useful only for constants?
>>
>> Private Sub CommandButton1_Click()
>> Application.Calculation = xlCalculationAutomatic + _
>> xlCalculationManual - _
>> Application.Calculation
>> CommandButton1.Caption = IIf(Application.Calculation = _
>> xlCalculationManual, "MANUAL", "AUTO")
>> End Sub