Command Button That Will Toggle

[Paige]

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?

 

[dan dungan]

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

 

[dan dungan]

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

 

[Paige]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[dan dungan]

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

 

[Paige]

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]

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

 

[Paige]

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\)]

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

 

[dan dungan]

Thanks Rick.