+ Reply to Thread
Results 1 to 4 of 4

Prevent Screen Changes During Procedure

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    41

    Prevent Screen Changes During Procedure

    Hi,

    I have 2 workbooks open. In the workbook that I am working in I run a macro from a userform to copy a worksheet from the other workbook that is idle in the back ground. I also display another user form that says "please wait" while the macro is running. The problem I have is even though screen updating is set to false the screen switches over to the other workbook while it is copying the desired sheet and switches back to the workbook I am copying to after the macro completes. Also the "Please Wait" userform disappears while the other workbook is displayed and reappears when the initial workbook returns. Below is my code which works without issue but perhaps there's a better way to copy between workbooks that would prevent the screen changes?

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 04-26-2014 at 02:01 PM. Reason: Corrected Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Prevent Screen Changes During Procedure

    Hello ezrizer,

    There is a misunderstanding of how Application.ScreenUpdating functions. When set to false, Excel will prevent the worksheet window from being updated (refreshed) when data changes. When you switch to another window on the task bar, the Windows operating system controls the updating of the desktop window where all application windows are displayed.

    The reason your code is causing you problems is the other workbook is activated by the macro. First lesson in VBA should be "It is rare that an object needs to activated or selected before an action is performed on the object." The revised code below should work for you.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Prevent Screen Changes During Procedure

    Leith thanks for the reply but that made no difference. A couple more things that might help you understand my situation. The sheet being copied is hidden in the other workbook so it needs to be made visible once in the new workbook. The activation occurs after it is copied so the user doesn't have to select the new sheet in order to start working in it. Removing the activate does nothing more than return you to the sheet that the macro was run from rather than bring you to the new sheet.

  4. #4
    Registered User
    Join Date
    08-10-2012
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    41

    Re: Prevent Screen Changes During Procedure

    Any other ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Prevent Screen Updating
    By ezrizer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2014, 09:35 AM
  2. Screen Updating reset at end of procedure
    By anteagles20 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-28-2013, 08:07 AM
  3. How do I lock a screen to prevent scrolling?
    By raveepoojari in forum Excel General
    Replies: 5
    Last Post: 04-06-2009, 02:12 PM
  4. Macro - Prevent screen refresh
    By Bharath Rajamani in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2006, 06:20 AM
  5. prevent event procedure under condition
    By short_n_curly in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-27-2005, 04:05 PM

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