Stop specific cell from recalculating?
 

I've created a new product as an Automation Addin. It's function is
similar to smart tags as it allows specific functions to push or pull
data between Excel and remote servers via Web Services. When a
customer ID changes in a cell, the cell next to it may recalculate,
the formula will invoke the service and pull in a customer name or
other data.

The problem is that if the user inserts/deletes columns or rows the
entire sheet is recalculated, and every cell with those formulas will
trigger a web service call. I have a couple complex formulas that
stop this from happening, but I won't expect users to put these
formulas in every worksheet that uses the functions.

So I want to allow the user to stop the firing of the specific cells
with the formulas until they want them to calculate. If they change a
customer ID they'll probably want the event to fire, but not if the
entire worksheet does a shift.

Ideally we could use an Excel function that says:
=IF($Z$1,OurFunction(foo),NoOperation)
The user could set Z1 to true as they wish to allow functions to
execute or not. Unfortunately Excel returns a True or False value if
the result of IF() is not specified.

I'm posting to this forum because I'm hoping there is a formula to do
this. If not then I'll code the AddIn to check for some conditions in
the worksheet, and if they're not met, just don't invoke the remote
call. Even then however, I still need to deal with making sure that
the current cell value doesn't change - or passing the current value
out and then returning the same value if we're doing a No-Op.

I can solve the problems but I want the solution to be elegant. Any
ideas before I start hacking away?

Thanks!!!!

Stop specific cell from recalculating?
 

Rather than stopping certain cells from recalculating, here's a couple little
macros that will only recalculate specific cells when Recalc is set to
Manual...........perhaps that will do as you wish.

Sub ConditionalRecalcOLD()
Dim myrange As Range
Range("myrange").Calculate
End Sub

Sub ConditionalRecalc()
With Selection
..Calculate
End With
End Sub

Vaya con Dios,
Chuck, CABGx3




"Tony Gravagno" wrote:

I've created a new product as an Automation Addin. It's function is
similar to smart tags as it allows specific functions to push or pull
data between Excel and remote servers via Web Services. When a
customer ID changes in a cell, the cell next to it may recalculate,
the formula will invoke the service and pull in a customer name or
other data.

The problem is that if the user inserts/deletes columns or rows the
entire sheet is recalculated, and every cell with those formulas will
trigger a web service call. I have a couple complex formulas that
stop this from happening, but I won't expect users to put these
formulas in every worksheet that uses the functions.

So I want to allow the user to stop the firing of the specific cells
with the formulas until they want them to calculate. If they change a
customer ID they'll probably want the event to fire, but not if the
entire worksheet does a shift.

Ideally we could use an Excel function that says:
=IF($Z$1,OurFunction(foo),NoOperation)
The user could set Z1 to true as they wish to allow functions to
execute or not. Unfortunately Excel returns a True or False value if
the result of IF() is not specified.

I'm posting to this forum because I'm hoping there is a formula to do
this. If not then I'll code the AddIn to check for some conditions in
the worksheet, and if they're not met, just don't invoke the remote
call. Even then however, I still need to deal with making sure that
the current cell value doesn't change - or passing the current value
out and then returning the same value if we're doing a No-Op.

I can solve the problems but I want the solution to be elegant. Any
ideas before I start hacking away?

Thanks!!!!

Stop specific cell from recalculating?
 

Sorry Tony..........for some reason I didn't get all of your post the first
time, therefore my response was somewhat inappropriate. Just ignore, and I'l
go take the rest of my medications.

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

Rather than stopping certain cells from recalculating, here's a couple little
macros that will only recalculate specific cells when Recalc is set to
Manual...........perhaps that will do as you wish.

Sub ConditionalRecalcOLD()
Dim myrange As Range
Range("myrange").Calculate
End Sub

Sub ConditionalRecalc()
With Selection
.Calculate
End With
End Sub

Vaya con Dios,
Chuck, CABGx3




"Tony Gravagno" wrote:

I've created a new product as an Automation Addin. It's function is
similar to smart tags as it allows specific functions to push or pull
data between Excel and remote servers via Web Services. When a
customer ID changes in a cell, the cell next to it may recalculate,
the formula will invoke the service and pull in a customer name or
other data.

The problem is that if the user inserts/deletes columns or rows the
entire sheet is recalculated, and every cell with those formulas will
trigger a web service call. I have a couple complex formulas that
stop this from happening, but I won't expect users to put these
formulas in every worksheet that uses the functions.

So I want to allow the user to stop the firing of the specific cells
with the formulas until they want them to calculate. If they change a
customer ID they'll probably want the event to fire, but not if the
entire worksheet does a shift.

Ideally we could use an Excel function that says:
=IF($Z$1,OurFunction(foo),NoOperation)
The user could set Z1 to true as they wish to allow functions to
execute or not. Unfortunately Excel returns a True or False value if
the result of IF() is not specified.

I'm posting to this forum because I'm hoping there is a formula to do
this. If not then I'll code the AddIn to check for some conditions in
the worksheet, and if they're not met, just don't invoke the remote
call. Even then however, I still need to deal with making sure that
the current cell value doesn't change - or passing the current value
out and then returning the same value if we're doing a No-Op.

I can solve the problems but I want the solution to be elegant. Any
ideas before I start hacking away?

Thanks!!!!

Stop specific cell from recalculating?
 

Better living through chemistry! <g

--
Biff
Microsoft Excel MVP


"CLR" wrote in message
...
Sorry Tony..........for some reason I didn't get all of your post the
first
time, therefore my response was somewhat inappropriate. Just ignore, and
I'l
go take the rest of my medications.

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

Rather than stopping certain cells from recalculating, here's a couple
little
macros that will only recalculate specific cells when Recalc is set to
Manual...........perhaps that will do as you wish.

Sub ConditionalRecalcOLD()
Dim myrange As Range
Range("myrange").Calculate
End Sub

Sub ConditionalRecalc()
With Selection
.Calculate
End With
End Sub

Vaya con Dios,
Chuck, CABGx3




"Tony Gravagno" wrote:

I've created a new product as an Automation Addin. It's function is
similar to smart tags as it allows specific functions to push or pull
data between Excel and remote servers via Web Services. When a
customer ID changes in a cell, the cell next to it may recalculate,
the formula will invoke the service and pull in a customer name or
other data.

The problem is that if the user inserts/deletes columns or rows the
entire sheet is recalculated, and every cell with those formulas will
trigger a web service call. I have a couple complex formulas that
stop this from happening, but I won't expect users to put these
formulas in every worksheet that uses the functions.

So I want to allow the user to stop the firing of the specific cells
with the formulas until they want them to calculate. If they change a
customer ID they'll probably want the event to fire, but not if the
entire worksheet does a shift.

Ideally we could use an Excel function that says:
=IF($Z$1,OurFunction(foo),NoOperation)
The user could set Z1 to true as they wish to allow functions to
execute or not. Unfortunately Excel returns a True or False value if
the result of IF() is not specified.

I'm posting to this forum because I'm hoping there is a formula to do
this. If not then I'll code the AddIn to check for some conditions in
the worksheet, and if they're not met, just don't invoke the remote
call. Even then however, I still need to deal with making sure that
the current cell value doesn't change - or passing the current value
out and then returning the same value if we're doing a No-Op.

I can solve the problems but I want the solution to be elegant. Any
ideas before I start hacking away?

Thanks!!!!