CODEWITHSUNDEEP
excelvba
Chris R
Chris R

Reputation: 65

Optimizing my code and stopping flickering

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

Upvotes: 1

Views: 89

Answers (4)

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

  1. Turn off Screenupdating as @teylyn suggested
  2. 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

  1. Use proper Error handling
  2. Save user settings
  3. Apply your settings
  4. 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

Upvotes: 4

Blake
Blake

Reputation: 230

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.

Upvotes: -2

Nilesh Deshmukh
Nilesh Deshmukh

Reputation: 359

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

Upvotes: 0

teylyn
teylyn

Reputation: 35915

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.

Upvotes: 4

Related Questions