MS Excel not auto-calculating cells

Posted on 2007-08-06
Last Modified: 2013-12-26
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?
Question by:tmonteit
    LVL 11

    Expert Comment

    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


    LVL 11

    Expert Comment

    that should read

    Private Sub Worksheet_Activate()
    End Sub


    Author Comment

    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
    LVL 11

    Expert Comment

    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

    LVL 33

    Accepted Solution

    perhaps placing the line in your UDF might help:
    Application.Volatile = True

    LVL 85

    Expert Comment

    by:Rory Archibald
    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.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now