+ Reply to Thread
Results 1 to 5 of 5

ScreenUpdating not working properly

Hybrid View

  1. #1
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Thumbs up ScreenUpdating not working properly

    Hi all, my problem isn't a major one but it is a curious one. I've been working on an automating data transfers and was told to use this code to stop the risk of siezure:

    Application.ScreenUpdating = False
      Code
    Application.ScreenUpdating = True
    It has worked great for me but today I opened the workbook and the jumping screens are back. Not for every Sub but only for one, even though it has the ScreenUpdating code.

    I've uploaded the workbook. The problem is located in Module2 Sub "SourceTransfer"
    Can someone run this and let me know if it is an Excel bug or if I have done something wrong?
    To run it, copy the data from Sheet1 and paste it into cell B2 of Insert sheet.

    Thank you.
    Attached Files Attached Files
    Last edited by Mordred; 07-23-2010 at 12:01 PM.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: ScreenUpdating not working properly

    Your sub calls InsertLoopLines which turns screenupdating back on. Change that sub to:
    Sub InsertLoopLines()
    Dim blnUpdate as Boolean
    blnUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    '
    ' This Sub is important because the For Each Loops that search for matches need a stopping point or else the
    ' the data is transfered wrong.  This Module may requird som rework.
    '
        
    Range("33:33,65:65,97:97").Select
    Range("A97").Activate
    Selection.Insert Shift:=xlDown
        
    Application.ScreenUpdating = blnUpdate
        
    End Sub
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: ScreenUpdating not working properly

    Wow, thanks romperstomper for the fast reply, your solution works great and I will mark this as solved in just a moment. I was curious though, is it necessary to use ScreenUpdating for every Sub? I have been since I've learned about it but should I be? The InsertLoopLines Sub could probably do without the ScreenUpdating procedure because it doesn't do a lot right, what is your opinion?

  4. #4
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,971

    Re: ScreenUpdating not working properly

    It's not absolutely necessary but since that sub inserts rows I'd probably leave it in there.

  5. #5
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: ScreenUpdating not working properly

    Ok, I will. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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