Solved: Screen Updating

[neilholmes]

Hi everyone... I was hoping you would be able to help me (once again)

I know the code that stops a screen updating when a macro runs...


Application.ScreenUpdating = False

(My code)

Application.ScreenUpdating = True


...but it doesn't seem to be working effectively in my current project.

My code is designed to create a document made up of various statements that are housed in different word templates. 'My code' based on the users selection will open the relevant word template, copy its content, close it, then paste it into the original document. This works great, however no matter where I place the above code I always get screen updates (flashes) while the macro runs. This tends to be when the template being copied opens/closes.

Is there anything that I can add to my code that might eliminate the updating? Something more effective that then above ?

Would it help it the above was placed in the template that is being opened ?

Many thanks

- Neil

[macropod]

Hi Neil,

Application.ScreenUpdating controls the updating of the current document's window, but not the updating that occurs when documents are opened/closed. You could reduce the flickering by using ActiveWindow.Visible = False in the macro that calls the others (resetting to True at the end).

[Frosty]

Yes, .ScreenUpdating is very unreliable. I'd say, in general, Word's desire to keep pagination accurate is the most common cause for the unreliability (i.e., running code in normal view rather than page layout view can also help, etc).

In addition to Paul's suggestion, you might also try minimizing/removing your use of the Selection object (use ranges instead).

Also, if you are opening multiple documents without user interaction, you might try opening them with the optional Visible argument set to false.

Without specific code, it's hard to be specific. However, in my experience, asking this question generally means (with respect), that you would probably benefit from a revamp of your code, rather than bracketing all your subroutines with some way of hiding your actual processing.

[macropod]

Without specific code, it's hard to be specific. However, in my experience, asking this question generally means (with respect), that you would probably benefit from a revamp of your code, rather than bracketing all your subroutines with some way of hiding your actual processing.
Indeed, having 'Application.ScreenUpdating = True' in a called subroutine invariably means you're causing the screen to flicker at that point if any ranges before the end of the visible range have been expanded/contracted or anything on screen has had its formatting changed.

[neilholmes]

Thanks everyone for your feedback and help on this so far. I have tried to place in...


ActiveWindow.Visible = False

ActiveWindow.Visible = True


...but this has had little positive effect on the screen flashing problem. Below is an example of the code I am using, sorry, for security reasons I can't put in the actual code, but this is the basics. If anyone could help streamline this code that would be great. I have little experience of VBA and what I have put together comes from information obtained online.



Dim strVARIABLE1 As String

If Opt1 = True Then strVARIABLE1 = Word.Documents.Open("E:\MYDOCUMENT.dot")
Word.ActiveDocument.Select
Word.Selection.Copy

If Opt2 = True Then strVARIABLE1 = Word.Documents.Open("E:\MYDOCUMENT2.dot")
Word.ActiveDocument.Select
Word.Selection.Copy

Word.ActiveDocument.Close

With ActiveDocument
.Bookmarks("BOOKMARK1").Select
Word.Selection.Paste
Selection.Delete



The code is built into a userform, where the user has the option to select either OptionButton 'Opt1' or 'Opt2'. Dependant upon their selection, will depend upon what document is opened, copied, and then pasted into the template.

Any help would be really good.

Thanks again.

- Neil

[macropod]

Hi Neil,

There is no indication in that code of where you might be using ActiveWindow.Visible = False. What you have posted, though is very inefficient and, moreover, is liable to increase the flashing. Try something along the lines of:
Dim Doc As Document, StrVar As String
StrVar = ""
If Opt1 = True Then
StrVar = ""
ElseIf Opt2 = True Then
StrVar = "2"
End If
Set Doc = Documents.Open("E:\MYDOCUMENT" & StrVar & ".dot", _
AddToRecentFiles:=False, Visible:=False)
Doc.Range.Copy
Doc.Close
ActiveDocument.Bookmarks("BOOKMARK1").Range.Paste

[neilholmes]

Thanks macropod,

I will try this code to see if it helps. However, one of the reasons for using the original code was because I could understand the code and therefore its actions. (Please excuse my vb ignorance).

If I need to add multiple variables to the string (if my userform contains Opt3, 4, 5 etc) do I just continue to duplicate the 'ElseIf'' statement ?

As follows :


Dim Doc As Document, StrVar As String
StrVar = ""
If Opt1 = True Then StrVar = ""
ElseIf Opt2 = True Then StrVar = "2"
ElseIf Opt3 = True Then StrVar = "3"
ElseIf Opt4 = True Then StrVar = "4"
End If
Set Doc = Documents.Open("E:\MYDOCUMENT" & StrVar & ".dot", _
AddToRecentFiles:=False, Visible:=False) Doc.Range.Copy Doc.Close ActiveDocument.Bookmarks("BOOKMARK1").Range.Paste

[macropod]

Hi Neil,

Yes, you could use more ElseIf arguments, though it does seem odd to have so many Opt variables first. You could also use a series of If statements on their own:
Dim Doc As Document, StrVar As String
If Opt1 = True Then StrVar = ""
If Opt2 = True Then StrVar = "2"
If Opt3 = True Then StrVar = "3"
If Opt4 = True Then StrVar = "4"
Set Doc = Documents.Open("E:\MYDOCUMENT" & StrVar & ".dot", _
AddToRecentFiles:=False, Visible:=False)
And what is meant to happen if two of them are true (assuming that's possible)? It also seems you could use the InsertFile method instead of copy/paste or, if you were going to stay with copy/paste, have all the source material in a single document, bookmark the ranges of interest and do the copying based on the bookmarked ranges.

Note: Your last line of posted code is messed up.

[neilholmes]

Hi macropod,

In some cases there are more than two OptionButtons. For example the userform might say:

"Please select one of the following statements to include in your report.

Option 1 - Statement 1
Option 2 - Statement 2
Option 3 - Statement 3"

When using OptionButtons in a Frame you can only select one OptionButton at a time I have found.

Sorry to be a pain macropod but would you mind showing how the 'InsertFile' method works and a code for it based on the above?

Thanks once again.

- Neil

[Frosty]

I think Paul was asking whether there are scenarios where you need to open/copy/close/paste from multiple documents, not whether you might have multiple option buttons selected at once (in which case, I would suggest a use of the collection object, but it doesn't appear to be a need for you)

The performance hits are the (primarily) the following:
1. Opening and closing a document (which is why .InsertFile might be helpful, although I don't think it will be appreciably different from opening with .Visible = False, which Paul has already demonstrated). Some of the performance issue is network speed related, which is outside of your control from VBA.

2. Not using selection object, but a range object instead.

Set oDoc = Documents.Open("E:\MyDocuments1.dot") gives you a document.
You don't have to select to copy, you can simply use
oDoc.Content.Copy
rather than
Selection.Copy

Making the Visible parameter false in the Documents.Open method will speed up the process of opening and closing... but you no longer have a selection object in that document (since selection only refers to the ActiveDocument). Thus the use of the .Range object (aka .Content for an entire document).

One of those commands tells Word to give some visual feedback to the end-user (i.e., highlight stuff in black) and one just does it (that's the concept of *the* Selection vs. just *a* Range). You'll want to keep looking for any use of a selection object in your code, and then post sample for how to translate (I'm guessing it's rampant, since at least some of this started as a recorded macro-- which is a great way to start, very easy to remember what you did and see the recorded equivalent).

But, big picture, worrying about screen-flickering sounds like you're trying to make this super slick-- which is, frankly, probably not possible without a lot of help, given your level of expertise, unless you're also using this as a learning exercise.

In that case, I would suggest looking up "Working with Ranges" in the Word help file. There is a lot of material there which will help you translate from recorded macros/heavy use of the Selection object to a slicker program. But it's a bit of a long process (not to take away from the excellent specifics Paul is giving).

Sorry for the long-winded explanation, but you said one of the reasons why you used the old code was because you understood it. I think that's a fine reason to use that code and not worry about the screen-flickering too much.

In some respects, the flickering can be nice-- it lets you know something is going on. If your screen "locks up" because of an effective use of the Application.ScreenUpdating = False, and then proceeds to take 2 minutes to do all of the other stuff, you're going to be making a post saying "How do I give my end-users a progress bar when running a long program?"

Grin.

[macropod]

Hi Neil,

I take a different view from Frosty on the flickering issue: I don't regard it as a matter of making the app 'super slick'. Rather, I regard it as a symptom of something that could possibly be done better - it generally indicates the use of selections and window switching that can usually be avoided. With the code you posted, that was certainly the case. And, IMHO relying on flickering for a progress indicator is poor practice - the showing something meaningful on Word's status bar or a proper progress bar is far more preferable.

As for using the InsertFile method:
Dim StrVar As String
If Opt1 = True Then StrVar = ""
If Opt2 = True Then StrVar = "2"
If Opt3 = True Then StrVar = "3"
If Opt4 = True Then StrVar = "4"
With ActiveDocument.Range
.InsertFile FileName:="E:\MYDOCUMENT" & StrVar & ".dot", _
Range:=.Bookmarks("BOOKMARK1").Range, Link:=False
End With

[Frosty]

I disagree that we disagree, Paul. Grin.

Let me clarify my point: it was not that screen-flickering was good practice, but that the next step into making it work "correctly" might not necessarily be worth the time-investment/learning-curve... especially if the OP gets to the point where so many documents are being opened/closed in the background that the screen is actually frozen for a noticeably long time. At that point, a progress bar should be displayed... unless the whole thing is simply going to be used by the programmer and no one else... since the programmer can always use CTRL+BREAK to see if the program is "doing something" vs. stuck.

So I was just tossing in a ROI (Return On Investment) concept, rather than saying screen-flickering as progress was a good practice :)

[neilholmes]

Thanks for your input and the time you have spent invested in it.

The document I have created is a report building tool which is designed to create a tailored report for the user. It's content is dependant on situation, so the user may need to select one recommendation or up to say ten !

It's all dependant on the individual situation.

I'd like to polish the code and stop the screen updating because I don't think it looks professional. The type of business I work in wants a result and understands little in terms of 'how these things work' or how much time they take to implement. They wanted a quick solution to build reports which I implemented, but now they have seen its potential and want 'this and that' done to better the reports... Me being the only member of staff with a small amount of knowledge on vb has sudden made me an authority on the subject !

:rotlaugh:

With all the amendments now in place it has made the documents coding bloated, so I need to slim it down. Thankfully the coding is really all centered around this function, it's just used multiple times to reach the goal.

Reading back on your help, I can say that yes a lot of the code was recorded so have found that 'select object' is indeed rampant in the coding. I'm now going to take a step back and look at everything, de-constuct and rebuild... Should be fun :question:

Thanks again for your help.

- Neil