• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1886
  • Last Modified:

MS Excel not auto-calculating cells

I have a MS Excel spreadsheet that calls a custom function.     Several consecutive vertical cells call the function:  =myFoo(A3)

My problem is that Excell does not auto recalculate these values when I activate the spreadsheet.  In order to get the values updated I have to select them all and type <ctrl>-d.  

How do I make the spreadsheet auto-recalculate the values when I activate the worksheet?
1 Solution
Make sure automatic calculation is turned on:

tools->options               calculation tab  --> check automatic or automatic (except tables)

Or, in the sheet activate event in VB, you can add

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub


that should read

Private Sub Worksheet_Activate()
End Sub

tmonteitAuthor Commented:
ugeb, its not working I've listed all the symptoms.

1.  neither of those worksheet subroutines recalculates the worksheet and changes the cell values.

2.  tools->options               calculation tab  --> check automatic - is enabled

3.  F9 Does not recalculate

4.  Still the only way I can get cell values to change is to hilight the cells and press ctrl-d
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Are other cells recalculating?  Is it only your user defined function or does nothing work?

If only your function, try setting a breakpoint at the beginning of the function to see if it is being called.  You can also add
debug.print   xxx  
where xxx is anything you want, including the value of the argument

Jeroen RosinkSoftware testing consultantCommented:
perhaps placing the line in your UDF might help:
Application.Volatile = True

Rory ArchibaldCommented:
What does your UDF do? It ought, from your description, to recalculate if the input cells (A4, A5 etc) change, but not otherwise. If you need it to recalculate even if those don't change then you should either look at rewriting the UDF or use Application.Volatile as Jeroen suggested.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now