Application.ScreenUpdating won't change to false

Question

I'm pretty new to VBA, but I've found my way around by reading through these forums. I've never had a problem with Application.ScreenUpdating not working and after researching online, I can't find an answer to my problem.

I've created a Daily Checklist application in excel that gives a month view in the Daily Checklist. The tasks line up in rows and you simply select yes, no or N/a, for that task for the day on the calendar. You can save the checklist and it copies the information to a data sheet in a separate tab. Everything works perfectly except when saving the information the Application.Screenupdating isn't working and the user ends up seeing the flickering and jumping back and forth between the data sheet and the calendar view.

Any ideas or guidance on why Application.ScreenUpdating is not changing to false? I've tried moving it around in different areas, but nothing seems to work.

Here's the Save checklist sub:

Sub Save_Checklist()

Dim Checklist_Date
Dim Completed As Long
Dim Left_to_Complete As Long
Dim Database_Date As Range
Dim Database_Row As Long
Dim bScrUpdate

bScrUpdate = Application.ScreenUpdating
If bScrUpdate = True Then Application.ScreenUpdating = False

Worksheets("Database").Unprotect Password:="youngC"

If MsgBox("This button will save this month's checklist data into the database. All previous information will be overwritten. Would you like to continue?", vbYesNoCancel, "Reset the Calender") = vbYes Then

Checklist_Date = Worksheets("Daily Checklist").Range("E4").Value
Add_to = Worksheets("Daily Checklist").Range("AL1").Value
Range("E55").Select
ActiveCell.Resize(2, Add_to).Select
Selection.Copy

    On Error Resume Next
    With Worksheets("Database").Activate
        Range("A1:A366").Select
        Selection.Find(What:=Checklist_Date, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Select
    On Error GoTo 0
        If Not Database_Date Is Nothing Then Application.Goto Database_Date, True
    End With

Selection.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True


Worksheets("Database").Protect Password:="youngC"

Else
    MsgBox ("Please be sure all information is correct before saving it.")
End If

If Not Application.ScreenUpdating = bScrUpdate _
    Then Application.ScreenUpdating = bScrUpdate

End Sub

This is the code I have running upon the workbook opening (I have grouping in the "Add Tasks to Month's Calendars" sheet):

Private Sub Workbook_Open()
    Worksheets("Main Menu").Activate
    With Worksheets("Add Tasks to Month's Calendars")
    .EnableOutlining = True
    .Protect Password:="youngC", _
    Contents:=True, UserInterfaceOnly:=True
    End With
End Sub

Any help at all would be greatly appreciated.

Thanks, Chris

Your title says it doesn't change to FALSE but your question says it doesn't work. Which is it? — Doug Glancy
Also, I don't think it does any harm, but setting a Boolean value to False if it's True is the same as setting it to False, so you could shorten those two lines of code. — Doug Glancy
I apologize for the misunderstanding in the question. The macro runs perfectly fine, so the code works. The Application.Screenupdating = False is not working and therefore the screen changes when the user clicks the button assigned to this macro. — Chris Merola
just get rid of all the pointless activates and selects and gotos 1) slowing it down, 2) causing the flickering. ScreenUpdating doesn't stop all screen changes: do you see the data being pasted etc? You should just see it changing sheets and workbooks etc. — Cor_Blimey
@user2603948 unfortunately the macro recorder will rarely show 'good' ways to do things, but it is useful for finding out certain properties or when dealing with things like pivot tables / data connections. Using Selection just returns the object that is selected be it a range, a chart, a shape etc. Instead of going e.g. ActiveCell.Copy:Worksheets("Sheet2").Activate:Worksheets("Sheet2").Range("A1").Select:Selection.PasteSpecial... assign the range to a variable: Set myRange1 = Worksheets("Sheet1").Cells(1,1):Set myRange2 = Worksheets("Sheet2").Cells(1,1):myRange2.Value = myRange1.Value — Cor_Blimey

Graham Anderson Graham Anderson · Accepted Answer · 2013-08-25T08:54:37

Personally I would just set the screenupdating property to false without checking it, once the VBA has finished running the screen will update as normal.

Also I think that the problem is that the second peice of code that runs when you open the worksheet does not turn screenupdating to false so you will see all the movements you described. Excel doesn't remember that the screenupdating was set to false by a previous macro and even if it did the code you have turns it back on anyway. You should be able to solve this by setting screenupdating to false on your first line, the modification is shown below.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Worksheets("Main Menu").Activate
    With Worksheets("Add Tasks to Month's Calendars")
    .EnableOutlining = True
    .Protect Password:="youngC", _
    Contents:=True, UserInterfaceOnly:=True
    End With
End Sub

Application.ScreenUpdating won't change to false

2 Answers