Application.ScreenUpdating = False not working properly

    I call a function right at the start of my code that looks like this:


    Code
    Sub OptimizeCode_Begin()
Application.ScreenUpdating = False
EventState = Application.EnableEvents
Application.EnableEvents = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False
End Sub


    At the end of my code I call:


    Code
    Sub OptimizeCode_End()
ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True
End Sub


    In one of the subs that runs in between, I have the following code:


    Code
    If .Worksheets(intCount).Name = "Invoice" Then
                Worksheets("Invoice").Delete
                .Worksheets.Add
            With ActiveSheet
                .Name = "Invoice"
            End With
                booFound = True
                Exit For
            End If


    Yet for some reason, at the line:


    Code
    Worksheets("Invoice").Delete


    I still get the warning about deleting a sheet.


    If I change that block above to:



    It works fine. But my question is why do I need the "Application.DisplayAlerts = False" immediately before the .Delete statement? Surely alerts should already be set to False due to the initial sub setting it that way?


    Also posted here:


    http://www.mrexcel.com/forum/e…properly.html#post4548740

    Re: Application.ScreenUpdating = False not working properly


    You would not need to disable Alert Diplay immediately before .Delete if "Application.DisplayAlerts = False" was included in your initial sub settings, but it isn't.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Application.ScreenUpdating = False not working properly


    Not trying to sound condescending, but do you know what all of the code in your OptimizeCode_ procedures are doing?


    I've seen these being misused used a lot and posted all over boards as a "one size fits all" solution. These procedures aren't optimizing anything, they're just turning application settings off. If you're doing that, you should ensure you have appropriate error handlers in your code to switch them back to their default settings in the event that the procedure exits early. We don't live in times where memory is that precious so if you need to use these kinds of procedures to make your code run noticeably faster then you have a poor design.


    (rant over *breathe*)


    More specific to your question though...


    It looks like you delete your sheet inside some kind of loop, except you're not using the loop variable to identify the sheet to be deleted. I don't think that's your problem here, but based on the code you've supplied there's nothing obviously wrong. Can you attach a workbook instead that shows the code not working?


    [sw]*[/sw]

Participate now!

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

Register Yourself Login