Cells Not Updated Automatically

  • Hi


    I have a worksheet in my Excel wookbook that references particular cells from another sheet. I use a simple macro to alter the values in some of these cells, but for some utterly bizzare reason, the cells referencing them are not updated. The only way to update the cells is to click on the cell and press Enter!


    I've never had this problem before.


    Does anyone know why this is, or perhaps know of a line of VBA code that updates every cell on a particular worksheet.


    Many thanks
    Johno

  • Re: Cells Not Updated Automatically


    Hi


    I've tried compressing the file and removing excess code/sheets, but cannot upload it. However, I don't think it is the code.


    If I close Excel and reopen it, the values in the cells referencing other sheets have been updated. Why is this?


    Many thanks
    Johno

  • Re: Cells Not Updated Automatically


    Hi


    I've noticed that when the sheet with the cells that reference other cells is created by my macro, the references don't update properly. But if I save the workbook and reopen it, it works fine. VERY strange.


    Has anyone the faintest idea why this is?


    Thanks
    Johno

  • Re: Cells Not Updated Automatically


    Can you post up a sample of the code that you are using ? I seem to remember some old bug in Office 2000 in terms of sheets being added and resulting in broken calculation trees - it's a long time since I used it though... reopening would I think force a calc tree rebuild so that may be the cause of your issue.


    I'll have a dig around.

  • Re: Cells Not Updated Automatically


    Hi Will


    Thanks for the reply.


    My code really is huge and the workbook is impossible to compress at a size to send on here (unless you have an email address I can send it to).


    Is there a line of code I can use that will rebuild the calc tree?


    Johno

  • Re: Cells Not Updated Automatically


    Sorry, can't accept files using email at work.


    Are you using Excel 2000 ? If so, it's not an easy task!


    The guru on Excel's calculation engine is a chap called Charles Williams - top bloke too ;) On the following page he explains how, using VBA, you can force recalculation with full dependency rebuild in Excel versions from 97 thru 2007


    Calculation in Excel using VBA

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!