Excel VBA relection bug

[Starkand]

Hi,

I have the weirdest bug. I have a simple macro that goes like this:

Sub Update()

        Application.Calculation = xlManual
    ActiveWorkbook.RefreshAll
    Range("A20").Select
        Application.Calculation = xlAutomatic

End Sub

When I use this code, all goes well, but I later get a sort of a reflection from one of the other worksheets in the one I am working on. This is very annoying, it typically overshadows what I am working on, especially when I input new data in tables. Here is an image showing the problem:



I would very much appreciate help with this!

/ Erling

 

[jmacleary]

Welcome to the forum. It is probably occurring because the refreshall is carrying on in the background after the macro finishes. Refreshall does not wait for all refreshes to complete, so results coming in may be causing it. If you put this extra line in like this:

Sub Update()

        Application.Calculation = xlManual
    ActiveWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    Range("A20").Select
        Application.Calculation = xlAutomatic

End Sub

then the macro will stop until all background refreshes have completed. If, on the other hand, you want to be able to carry on while th refreshes are still going on, then I think you will always stand a chance of 'ghosting'.

 

[MARK858]

Why are you switching to manual rather than using just .Calculate?

 

[Starkand]

Thank you very much for your help @jmacleary ! I have implemented your code and it seems to fix the problem of ghosting