Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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\)" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button That Will Toggle
Thanks Rick.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command button to toggle worksheet event code on / off? | Excel Discussion (Misc queries) | |||
VB's Command Button vs Form's Command Button | Excel Programming | |||
Single command to toggle a Boolean? | Excel Programming | |||
Adding .xla button for Toggle Calculation Button | Excel Programming | |||
Toggle command buttons | Excel Programming |