Optimizing my code and stopping flickering

Question

Hi guys so this part of my code is causing a little bit of flickering so i figure i must be doing something wrong, at first i thought just the act of hiding and unhiding columnns was causing the flicker and its just unavoidable but some sheet changes dont require column change and it still flickers.

I should note there is no flickering when i remove this portion of the code so it absolutely it is this code that is the problem.

Also i'm very new to coding so if someone wouldnt mind optimizing my code and kindly explaining what you did and why i would really appreciate it.

Thanks :)

Private Sub Worksheet_Change(ByVal Target As Range)
   If Sheets("Calendar").Range("B5") = "Hours" Then
        Application.ScreenUpdating = False
        Call UnlockSheet
        On Error GoTo BlankStaff
        Sheets("Calendar").Range("AU:AW").EntireColumn.Hidden = False
    With Worksheets("Calendar").Range("AV2:AV8").Borders(xlEdgeRight)
        .LineStyle = xlLineStyleNone
    End With
    With Worksheets("Calendar").Range("AV3:AV8").Borders(xlEdgeRight)
        .LineStyle = xlDashDotDot
    End With
        Call LockSheet
        Application.ScreenUpdating = True
    Else
        If Sheets("Calendar").Range("B5") = "Days" And Sheets("Calendar").Range("AV4").Value = "No" And Sheets("Calendar").Range("AV5").Value = "No" And Sheets("Calendar").Range("AV6").Value = "No" And Sheets("Calendar").Range("AV7").Value = "No" And Sheets("Calendar").Range("AV8").Value = "No" Then
            Application.ScreenUpdating = False
            Call UnlockSheet
            On Error GoTo BlankStaff
            Sheets("Calendar").Range("AU:AW").EntireColumn.Hidden = True
        With Worksheets("Calendar").Range("AV2:AV8").Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = 1
        End With
            Call LockSheet
            Application.ScreenUpdating = True
        Else
            Application.ScreenUpdating = False
            Call UnlockSheet
            Sheets("Calendar").Range("AU:AV").EntireColumn.Hidden = False
            Sheets("Calendar").Range("AW:AW").EntireColumn.Hidden = True
        With Worksheets("Calendar").Range("AV2:AV8").Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = 1
        End With
            Call LockSheet
            Application.ScreenUpdating = True
        End If
    End If
BlankStaff:
    Exit Sub
End Sub

I've not verified it, but as a guess, move your ScrrenUpdating=False to before the If statement, and ScreenUpdating=True to after the End If. Then it won't need to evaluate anything with a live screen. — B Troy, Sep 13 '19 at 03:33

score 4 · Answer 1 · answered Sep 13 '19 at 03:34

4

With each Application.ScreenUpdating = True, the screen will update and flicker. Put Application.ScreenUpdating = False at the beginning of your code and Application.ScreenUpdating = True in the line above Exit Sub

You also want to make sure that the subs you call don't reset the screenupdating.

You also may want to work on the indentation. It's confusing. The With blocks should be indented inside the IF blocks.

answered Sep 13 '19 at 03:34

teylyn

34,374
4
53
73

Thankyou, are there set rules for how to indent vba properly, i tried searching for this and couldnt find anything – Chris R Sep 13 '19 at 07:23

score 4 · Accepted Answer · answered Sep 13 '19 at 06:17

There are 2 main things that you can do to avoid the flicker.

Turn off Screenupdating as @teylyn suggested
Avoid the use of .Select/Activate etc... . You may want to see How to avoid using Select in Excel VBA

Having said that, whenever you are working with events, you need to respect a user's personal application settings. You may want the calculation to be manual but me as a user may like it in automatic mode. So the best way to go about it is

Use proper Error handling
Save user settings
Apply your settings
At the end of the code, reset user's settings.

See this example

Option Explicit

Sub Sample()
    Dim calcState As Long
    Dim screenUpdt As Boolean
    Dim eventsState As Boolean

    On Error GoTo Whoa

    '~~> Store user settings so that we can reset that later
    With Application
        calcState = .Calculation
        screenUpdt = .ScreenUpdating
        eventsState = .EnableEvents
    End With

    TurnOnMySetting

    '
    '~~> You Rest of the code goes here
    '

LetsContinue:       
    With Application
        .Calculation = calcState
        .ScreenUpdating = screenUpdt
        .EnableEvents = eventsState
    End With
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Sub TurnOnMySetting()
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
End Sub

score 0 · Answer 3 · answered Sep 13 '19 at 05:09

I would recommend to add below 2 checks for all macros, specially when you have lot of calculations/updates

turn off automatic calculations (Application.Calculation) = set to Manual (xlCalculationManual) turn off screen updating (Application.ScreenUpdating)

call those 2 in separate function so that you can enable / disable them.. call the function when you enter the procedure and then before you exit

score -2 · Answer 4 · answered Sep 13 '19 at 05:37

-2

Just add Application.ScreenUpdateing = False as the first line in the sub routine. There is no need to set it back to True at the end, it will automatically get turned back on. I do not recommend setting calculations to false because if there is an error they will not get turned back on unless you have proper error handling.

answered Sep 13 '19 at 05:37

Blake

230
1
8

1

This is so not true... If you don't set it back to True you won't see anything when clicking anywhere... – Damian Sep 13 '19 at 06:02
It appears that it will turn screen updating back on after a macro finishes. I ran a sub that just turns it off screen updating, then run another one to to test if screen updating is true to Debug.Print("True") and it prints True to the immediate window. I did some reading and it appears that it used to be an issue after excel 2003, but in office 365 it seems to be ok. – Blake Sep 17 '19 at 00:39

Optimizing my code and stopping flickering

4 Answers4