No automatic cell update with workbook_change unless clicking on another cell


I have a worksheet macro (worksheet_change) that only works properly once I have closed and reopened my workbook, without running any other macros that the workbook has. Otherwise, I have to select any cell after changing the target cell so it updates.

In details, if I open my workbook and work the other macros it has first, then when I move onto this worksheet and change the target cell which contains a list, my table does not update its values according to the selected value until I just click on any other cell within the worksheet.

However, if I close and reopen the workbook, go straight to that worksheet, and change the target cell, then the results in my table update instantly, without me having to click on any other cell.

The problem is, the workbook is supposed to follow a sequence where I have to run all the other macros before I get to this worksheet with the issue. I have tried to put it to sleep for a few milliseconds prior to calling the worksheet_change macro, but that did not help.

I wonder if anyone has any ideas on how to overcome this issue. Any help is very much appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
Sounds like there's an issue with your other macros, but unless we can see the code for them, it's hard to say what.
Kanti PrasadCommented:

Please see if this works

Go to Formulas->Calculation->Calculation Options and select "Automatic".

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nanco_NRCAuthor Commented:

Sorry for the delay and thank you both for your input.
I would love to share the macros (there are at least 30 subs) but, unfortunately, the file is confidential.

Following Prasad's suggestion, I added the command Application.Calculation = xlCalculationAutomatic to the end of the macro behind the sheet with issues and that partially solved the problem: the formulas in my table now update instantly as I select treatments, blocks, gender, etc. But the first column (which contains the categories) still only updates once I click on a random cell within the spreadsheet. This column does not contain formulas, it's being generated by a New Collection procedure within the macro. Maybe that's why xlCalculationAutomatic does not work on it.

It seems to me, though, that the values update internally, they are just not promptly displayed, have I run all the other macros first. If I just open the file and go straight to that sheet, they update instantly. It looks like a memory issue, maybe. I will try to make the processing sleep in different areas of the macro to see if that helps.

Thank you again for your input.
Kanti PrasadCommented:

Check also to see if the Automatic is selected on the sheet itself. Go to the Formular menu bar and look for calculation option it might be somewhere on the right side of the sheet and see if the automatic is clicked
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.