Screen Flicker

**BuzzT** · 07-06-2011, 12:36 PM

I am using a fairly small amount of code. The problem is the second section of code (which essentially just clears a range of cells if specific cell is cleared) is causing everything to run slow and flicker. By commenting out the section in question, everything is great. I find it strange that such a small condition could cause this. I have tried various VBA optimizations, but no luck.

Any ideas, or am I stuck with it?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Set MyRange = Range("$C$4:$C$10")

If Not Intersect(Target, Range("$B$3")) Is Nothing Then
MyRange.Value = ""
End If

' The next 3 lines are the problem
If Range("$C$3").Value = "" Then
MyRange.Value = ""
End If

If Range("$C$3").Value = "monthly" Then
Range("$C$9").Value = ""
End If

End Sub

**venkat1926** · 07-06-2011, 10:50 PM

at the beginning of the code introduce this statement
application.screenupdating=false

at the end before "end sub" type
application.screenupdating=true

**MarvinP** · 07-06-2011, 11:26 PM

Hi BuzzT

Here are all those hints about faster code and screenupdating too
http://www.cpearson.com/excel/optimize.htm

**BuzzT** · 07-07-2011, 08:49 AM

Thanks venkat1926, but that was one of the first things I tried and was a little surprised it did not really help.

**watersev** · 07-07-2011, 09:03 AM

hi, BuzzT, the problem is that clearing the cell content is triggering the code again and it's going into the cycle.

To avoid that try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Set MyRange = Range("$C$4:$C$10")

Application.EnableEvents = 0

If Not Intersect(Target, Range("$B$3")) Is Nothing Then
MyRange.Value = ""
End If

' The next 3 lines are the problem
If Range("$C$3").Value = "" Then
MyRange.Value = ""
End If

If Range("$C$3").Value = "monthly" Then
Range("$C$9").Value = ""
End If

Application.EnableEvents = 1

End Sub

**BuzzT** · 07-07-2011, 11:22 AM

Not sure why it didn't work when I tried it this way before (maybe I made a mistake somewhere), but this is working now.

Thanks watersev (and everyone).