Function execution on click?

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi everyone,

Please could anyone suggest a way to restrict the free running of a function, called by a sub, by making it conditional upon a button being clicked, or a check-box being ticked?

Any suggestions would be extremely helpful.

Thank you.

vcoder
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
What Function??? What sub?? By "running", do you mean "Calculate"?
lenze
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Hi Lenze,

Yes, I guess I really mean calculate rather than run. Is there any way to make evaluation of the function conditional upon a button or checkbox state?

vcoder
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Is it a UDF or native function?? You can use a Button or CheckBox to toggle Calcuation between manual and automatic!!

lenze
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354

ADVERTISEMENT

A Function called within a sub procedure can be controlled, as the procedure itself can be assigned to run on a click or a button. But if a Function is called within worksheet cells, then there appears to be no way to control the Function from evaluating.

The reason why the latter may be useful is if you have hundreds of calls to a function within a sheet, and don't want these to recalculate each time the sheet is opened. It can take a long time and therefore is undesirable.

Is there a way to make a Function called within a sheet conditional on some user-defined state - via a button, checkbox or something similar?

Thanks,

vcoder
 
Last edited:

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Is it a UDF or native function?? You can use a Button or CheckBox to toggle Calcuation between manual and automatic!!

lenze

It's a UDF. Could you suggest a way to implement an auto-to-manual recalc toggle - for a particular UDF only?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Maybe
Code:
Sub ToggleCal()
With Application
  If .Calculation = xlCalculationAutomatic Then
     .Calculation = xlCalculationManual
  Else: .Calculation = xlCalculationAutomatic
  End If
End With
End Sub

lenze
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Thank you Lenze,

I don't want to suppress all sheet calcs, so if I were to place the name of the UDF within this, would this make only the UDF controllable?

Thank you.

vcoder
 

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
Just as a follow-up to this thread, I have now managed to control the evaluation of the function. What I did was to wrap the code in a simple IF statement. The IF evaluated the inner function if a condition was met in a cell on the sheet, and skipped it in all other cases. It worked reliably. This can therefore easily be applied to a button or a checkbox.

Best regards,

vcoder
 

Watch MrExcel Video

Forum statistics

Threads
1,127,550
Messages
5,625,455
Members
416,107
Latest member
AVaes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top