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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
Is it a UDF or native function?? You can use a Button or CheckBox to toggle Calcuation between manual and automatic!!

lenze
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Maybe
Code:
Sub ToggleCal()
With Application
  If .Calculation = xlCalculationAutomatic Then
     .Calculation = xlCalculationManual
  Else: .Calculation = xlCalculationAutomatic
  End If
End With
End Sub

lenze
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top