What's with the flicker with Application.Screenupdating=False?

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Hey guys!
I have two macros on my active worksheet. The first macro hides columns that don't have a visible cell with a value like "*Total". When this macro is launched, I get a ton of screen flicker. I thought Application.ScreenUpdating= False would suppress it, but it's not.
Code:
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Cells.EntireColumn.Hidden = False
Dim lastRow As Long
Dim MyCount As Long
lastRow = Range("E" & Rows.Count).End(xlUp).Row
Dim c As Range
MyCount = 0
For Each c In Range("B5:B" & lastRow).SpecialCells(xlCellTypeVisible)
    If c Like "*Total" Then
    MyCount = MyCount + 1
    End If
Next c
If MyCount = 0 Then Range("B5:B" & lastRow).EntireColumn.Hidden = True


MyCount = 0
For Each c In Range("C5:C" & lastRow).SpecialCells(xlCellTypeVisible)
    If c Like "*Total" Then
    MyCount = MyCount + 1
    End If
Next c
If MyCount = 0 Then Range("C5:C" & lastRow).EntireColumn.Hidden = True

MyCount = 0
For Each c In Range("D5:D" & lastRow).SpecialCells(xlCellTypeVisible)
    If c Like "*Total" Then
    MyCount = MyCount + 1
    End If
Next c
If MyCount = 0 Then Range("D5:D" & lastRow).EntireColumn.Hidden = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Is that macro calling my other macro and causing the screen flicker?
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
If Target.Address <> "$B$3" Then GoTo Month
If Range("B3").Value = "Year" Then GoTo Month
Dim TYear As Long
Dim LYear As Long
Dim NYear As Long

TYear = Year(DateSerial(Year(Date), Month(Date), 1))
LYear = (Year(DateSerial(Year(Date), Month(Date), 1)) - 1)
NYear = (Year(DateSerial(Year(Date), Month(Date), 1)) + 1)
Range("B3").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
If Range("B3") <> "Year" Then

    Select Case Range("B3").Value
        Case Is = "This Year"
            Range("B3").Value = TYear
        Case Is = "Last Year"
            Range("B3").Value = LYear
        Case Is = "Next Year"
            Range("B3").Value = NYear

    End Select
End If

If Range("B3") <> "Year" And Range("C4") = "December" Then
    Range("C3").Formula = "=IF(ISERROR(B3-1),""Year"",(B3-1))"
Else
    Range("C3") = Range("B3")
    
End If

Month:
If Target.Address <> "$B$4" Then Exit Sub
If Range("B4").Value = "Month" Then Exit Sub

If Range("B3") <> "Year" And Range("C4") = "December" Then
    Range("C3").Formula = "=IF(ISERROR(B3-1),""Year"",(B3-1))"
Else
    Range("C3") = Range("B3")
End If
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I've run into that problem where a macro calling another, even with Application.ScreenUpdating = false causes issues.

Try commenting out the Application.ScreenUpdating = True line from the secondary macro (the one that gets called from the first one)
 

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Well, it was worth a shot. I commented out app.scr in the second macro, still flickering.

I commented out the entire second macro, still flickering. So the issue isn't calling the second macro. The first macro just insists on flickering
 

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Could the macro be calling itself?

When I put the code in a module and run manually, no flicker.

When the code is in the worksheet and is automatically called (want it to run when user selects an outline level), lots of flicker and it looks like columns are being hidden and unhidden repeatedly.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,468
Messages
5,528,977
Members
409,849
Latest member
J7House1984
Top