+ Reply to Thread
Results 1 to 6 of 6

Screen Flicker

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    31

    Screen Flicker

    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
    Last edited by BuzzT; 07-07-2011 at 11:22 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Screen Flicker

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

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

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,442

    Re: Screen Flicker

    Hi BuzzT

    Here are all those hints about faster code and screenupdating too
    http://www.cpearson.com/excel/optimize.htm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    12-30-2010
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Screen Flicker

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

  5. #5
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: Screen Flicker

    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:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-30-2010
    Location
    Montreal, Canada
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Screen Flicker

    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).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1