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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,653
Messages
5,838,612
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top