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.
Is that macro calling my other macro and causing the screen flicker?
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 SubIs 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
