Microsoft Excel - Trying to stop screen updates while macro is running.

I created the following macro for which I can not seem to stop the screen from updating when it  Calls RunAllaccounting. The RunAllaccounting activates macros used on another sheet, so the screen flickers going back and forth from from sheet to sheet.

Second, do you know a quick code that would show a "Please Wait" box while it runs, and a "Macro Finished" box when complete?


Sub Optimization1()

    Application.ScreenUpdating = False

'Set opening values to anchor initial calculations

    Worksheets("Scenarios").Range("PostFlipCashAllo1").Value = 0.0495

    Do

'Goal Seek Post Flip Cash Allocation
    Worksheets("Scenarios").Activate
    Range("a15").Select
    Application.CutCopyMode = False
    Range("PTIRR").GoalSeek Goal:=Range("PTIRRtarget1"), ChangingCell:=Range("PostFlipCashAllo1")
    
'Sets investor contribution amounts to NPV of cash flows at desired return

    Worksheets("Scenarios").Activate
    Range("TargetSEcontr").Select
    Selection.Copy
    Range("SEcontr1").PasteSpecial xlPasteValues
                        
'Goal Seek Post Flip Cash Allocation
    Worksheets("Scenarios").Activate
    Range("a15").Select
    Application.CutCopyMode = False
    Range("PTIRR").GoalSeek Goal:=Range("PTIRRtarget1"), ChangingCell:=Range("PostFlipCashAllo1")
    If Range("PostFlipCashAllo1").Value < 0.0495 Then Range("PostFlipCashAllo1") = 0.0495

    Call RunAllaccounting

    Loop While (Range("PTIRR").Value < Range("PTIRRtarget1") Or Range("SEcontrDifferential").Value <> 0)

    Application.ScreenUpdating = True

End Sub

Hello Joe,

The only thing that I can think of is that one of the other macros turns the ScreenUpdating back on so it is a bit hard to be specific without the whole application being exposed.  As the function call is inside a do loop on the second and subsequent runs through the various worksheet activates etc would be updated until the screenupdating is turned off again elsewhere but to be turned on again before it comes back inside the loop and so the process repeats.  So one fix might be to put the updating off code inside the loop.  BUT...

There are a couple of things about your code and it might be down to the other things that it does but I wonder why you have the repetition of the worksheet activate and the range select before the goal seek operations.  It doesn't seem to be related to that function as it all works on named ranges and I suspect you could delete all three lines with another small change.  Where you set the value between the two goal seek functions I think it could be simplified to something like:
Range("SEcontr1").Value = Range("TargetSEcontr").Value
and this would eliminate the need to clear the clipboard which is the third line mentioned above.

Not sure if this helps so let us know how you go.

Regards

Harry

PS.  Will have athink about the message thing later.

H

Hello Joe,

Have a look at these for an idea how to implement a progress meter.  I haven't tried them and you would have to create your own measure of 'completeness'.

http://www.excel-easy.com/vba/examples/progress-indicator.html

http://strugglingtoexcel.com/2014/03/27/progress-bar-excel-vba/

Hope you have fixed your 'flashing' problem.

Regards

Harry