Cause Cells to update.

[sid]

I have a spreadsheet that spans several hundred cells. On a second SS
I created a totals table. I populate the cells on this table by
calling a function that I wrote in VBA.

The VBA Function loops through a column and creates the appropriate
value and returns it. But when I go back to the main SS and change a
cell the totals SS does not update (call the function again). I
thought that I should be able to hit F9 (recalc), but that does not
help either. The only way that I have found to call the function
again is to go to the cell and exit it with "Enter"
i.e. Cell A2 contains ' = MyFunction("Param1") '

Any help is appreciated.

Sid.

 

[KC Rippstein hotmail com>]

Use a worksheet change event.
Right-click your main spreadsheet tab and click "View Code."
In the code window for that worksheet is a title bar with 2 dropdown menus.
In the left one select "Worksheet" and in the right one select "Change". You
should see this:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Enter your other function name inside the new sub procedure. So:
Private Sub Worksheet_Change(ByVal Target As Range)
MyFunction
End Sub

The change event is called whenever a change is made to that spreadsheet, so
that's the easiest way to keep your other page updated at all times.

 

[sid]

Use a worksheet change event.
Right-click your main spreadsheet tab and click "View Code."
In the code window for that worksheet is a title bar with 2 dropdown menus..  
In the left one select "Worksheet" and in the right one select "Change".  You
should see this:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Enter your other function name inside the new sub procedure.  So:
Private Sub Worksheet_Change(ByVal Target As Range)
    MyFunction
End Sub

The change event is called whenever a change is made to that spreadsheet, so
that's the easiest way to keep your other page updated at all times.
--
Please remember to indicate when the post is answered so others can benefit
from it later.








- Show quoted text -

Can't I use that event to cause the second sheet to update all its
cells. I have a lot of functions on the second sheet and I don't want
to have to name all of them again. (and keep them synchronized)

Thanks

 

[KC Rippstein hotmail com>]

I see what you're saying...you are calling a ton of user defined functions on
sheet2 that are not refreshing when data on sheet1 is changed.

For each of those functions you created, you have to add this to the begging
of each one:
Application.Volatile

Then they will refresh just like any normal formula.

 

[sid]

I see what you're saying...you are calling a ton of user defined functionson
sheet2 that are not refreshing when data on sheet1 is changed.

For each of those functions you created, you have to add this to the begging
of each one:
Application.Volatile

Then they will refresh just like any normal formula.

--
Please remember to indicate when the post is answered so others can benefit
from it later.







- Show quoted text -

I added that statement, but it had not effect.

Not sure how a statement added to the function cause it to update with
a change in the object that is calling it ?
I need to add a property to the first sheet to notify the second sheet
that changes have been made.

Thanks

 

[KC Rippstein hotmail com>]

Application.Volatile should have fixed the problem. I suggest you post this
question in the Excel Programming newsgroup to get a better answer.
Sorry I cannot help you further.

 

[sid]

Application.Volatile should have fixed the problem.  I suggest you post this
question in the Excel Programming newsgroup to get a better answer.
Sorry I cannot help you further.
--
Please remember to indicate when the post is answered so others can benefit
from it later.








- Show quoted text -

It did, but not until I hit F9
Don't understand that, but now it updates everytime !

Thanks