CODEWITHSUNDEEP
vbaexcel
Chris Merola
Chris Merola

Reputation: 23

Application.ScreenUpdating won't change to false

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

Upvotes: 2

Views: 10952

Answers (2)

AndrewK
AndrewK

Reputation: 79

If you print to debug during run the state of it, it will be false, but when you are in debug mode, then it will be always true as your code is in pause mode and this switches back to true automatically until you run it again in "fast" mode

Upvotes: 1

Graham Anderson
Graham Anderson

Reputation: 1239

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

Upvotes: 5

Related Questions