VBA - can I stop screen updating when activating different workbooks?
Results 1 to 5 of 5

Thread: VBA - can I stop screen updating when activating different workbooks?

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - can I stop screen updating when activating different workbooks?

    I have set

    Application.ScreenUpdating = False


    And, it turns off screen updating except for one thing. When i activate a different workbook (with Windows(bo).Activate) the screen changes to that different workbook. Since this is within a loop, it is happening many times.

    And, this tremendously slows down the macro execution, which is what I was trying to eliminate by setting ScreenUpdating=False in the first place. Is there a more thorough way to turn off ALL screen updating so this macro can execute faster and will not show changing windows when it hits the Windows.Activate statement?

    Thank you.

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,996
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA - can I stop screen updating when activating different workbooks?

    Why do you need to activate the other workbook?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - can I stop screen updating when activating different workbooks?

    Copying a whole bunch of info from one workbook to another. Maybe it could be designed more efficiently...however, just wanted to write a few quick and dirty lines of code and let the processor do the work. Was disappointed that Application.ScreenUpdating=False seems to have this 'exception'. So, back to the question, is there any way to "truly" turn off screen updating? I envision Excel sitting there and the screen doing absolutely nothing until the macro ends. Possible?
    Thanks!

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,996
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA - can I stop screen updating when activating different workbooks?

    There is no need to activate a workbook to copy data to it from another workbook. Do away with the activate and you don't have the issue and so not the question.

    And yes there has been code for a better screenupdating but "write a few quick and dirty lines of code" it isn't
    Last edited by MARK858; Sep 9th, 2018 at 02:57 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - can I stop screen updating when activating different workbooks?

    Thanks very much! I thought i had to activate the book in order to provide "context" for the destination cell. But, as you say, this is not necessary; full context can provided as:
    Workbooks(bnw).Sheets(sht).Range(addr).Formula = ftxt (as you know). Even when the active workbook is not that workbook.

    This did speed it up...although less than i had hoped. Maybe by about 25%.

    I have a "highly corrupted" workbook i am trying to recover, and so i want to try copying everything as text, not any copy of cell formulas or cell contents, so that i can see if i can recreate the workbook that way. "open and recover" nor anything else have worked. I have charts and PivotTables too, so it is going to be a long process to recover.

    Thanks!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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