Detecting changes in results of formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

spacecityguy - 24 Jan 2006 22:34 GMT

Hi All,

I've written a template that goes through a number of calculations
based on information entered into a worksheet. My code loops through
all the entries in this entry sheet, and as the number of entries
grows, it takes longer to finish the calculation. To improve my
template's performance, I use the Worksheet_Change event to detect
changes have taken place and trigger the calculation when the user
leaves the entry sheet. It works well except when the user enters a
formula into the entry sheet. To illustrate, let's say in cell B15 of
the information sheet, the user enters this formula:

='Sheet1'!A1+'Sheet1'!A2

and Sheet1 is a sheet other that the entry sheet. The problem I run
into is that while the user changes the value in cell A1 or A2 in
Sheet1 and the result in cell B15 of the entry sheet changes along, the
value of cell B15 of the entry sheet is still
"='Sheet1'!A1+'Sheet1'!A2". As a result, the Worksheet_Change event
isn't triggered.

I browsed through the VBA help and couldn't found any guidance on how
to deal with it. I'll appreciate if anyone can enlighten me.

Signature

spacecityguy

Reply to this Message

Resignation of an Excel Guru :( - 24 Jan 2006 23:47 GMT

Since I'm seeking help too, hope this contribution pays my dues:)

Just a thought - if in Worksheet_change code on sheet1 you use the
.Dependents property of the range object of any changed cell, you can trigger
a change event (I think) by reading the formula of the cell(s) that are
dependent on the cell changed and re-entering them
(unchanged!)programmatically... I think that should trigger a change event.

Jules

> Hi All,
>
[quoted text clipped - 19 lines]
> I browsed through the VBA help and couldn't found any guidance on how
> to deal with it. I'll appreciate if anyone can enlighten me.

Reply to this Message

Jim Thomlinson - 25 Jan 2006 00:37 GMT

Give this a try...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

On Error Resume Next
Set rng = Intersect(Range("A1"), Target.Dependents)
On Error GoTo 0
If Not (rng Is Nothing) Then MsgBox "A1 Changed"

End Sub

Signature

HTH...

Jim Thomlinson

> Since I'm seeking help too, hope this contribution pays my dues:)
>
[quoted text clipped - 29 lines]
> > I browsed through the VBA help and couldn't found any guidance on how
> > to deal with it. I'll appreciate if anyone can enlighten me.

Reply to this Message

spacecityguy - 27 Jan 2006 21:03 GMT

Thanks folks for helping... you've taught me about the precedent and
decendent properties I didn't know before. Unfortunately, they will not
solve my problem here. It's probably my fault that I didn't point out
that the formulas in the entry sheet will be entered by the user and
hence I can only find a way to test in the result of the formuals there
have changed (but not if the precedent's value has changed). Thanks
again though.

Signature

spacecityguy

Reply to this Message