Controlling Screen flashing using LockWindowUpdate

Hi,

A while back I was assisted with trying to prevent excessive screen flashing...

I was originally using the Application.ScreenUpdating=False method...but it was still a little jumpy... so Richard Schollar (a valued member in our forum) helped me with this code and it seemed to work...

Now I am trying to apply it again to another macro and it is not working so well. The thing I found is that on some people's computers it does work well, but not on mine.

Does anyone know why that is and how I can fix it so it doesn't flash on mine either?

Please Login or Register  to view this content.

Thanks for any advice.

Hmmm..

Thanks for the reply.

It kinda' works but maybe works to the other extreme... I.e. it freezes everything. And unless I click anywhere on my sheet minutes later, it does not automatically refresh my results.

When I click the button I have to invoke this macro, it doesn't give me the sense or assurance that the button was clicked. I mean I don't even see the button getting depressed and I don't see an hour glass and I don't see anything happen in the status bar. Then it never seems to finish... only when I click some time later, the new info suddenly appears...

Any way to fix this...

btw: I took this:

and of course, set it back to True at the end!

to mean add this line at the end:

Please Login or Register  to view this content.

correct?

The screenupdating didn't really help... still sits there and you don't know when it starts or finishes or what's going on in between.

Here's the full code:

Please Login or Register  to view this content.

I commented out the LockWindow calls..and replaced them with your SetRedraw calls...

Basically the code goes through and refreshes several tabs each with a MS Query to our database. Then it creates Pivot Table summaries with additional formulas added in 2 separate summary tabs.

The previous code would flash between some of the tabs and when it updated the formulas...

I didn't really want to complicate the code much... just wanted it to stop jumping around and only show the end result.

Originally Posted by romperstomper

It seems to work fine using XL2003 on Vista in my tests (better than 2003 on XP was earlier in fact). What setup are you running that is having problems?

Not sure what you mean by "What setup am I running?"

I am running XP Profession Ver. 2002 SP2 and Excel 2003 SP2.

I tried it all again this morning and it just doesn't give the sense that it is doing anything... Only when I clicked on another application and returned to Excel, was I able to navigate around Excel...

Well, that seems to work a lot better... I'll keep testing it.

I did try fixing it according to your previous post

Hmm, it was working ok in my tests, though I should have added that it's probably safer to use 0& and 1& rather than False and True in the API calls. Can you give me some idea what your code does in between and which version of Excel and Windows you are using so I can test appropriately?

but I guess I replaced the wrong things...

Thanks alot for your assistance... Now I need to figure out a way to get this thing to process faster...

Thanks again Romperstomper,

NBVC

Thanks again for the advice...

I will definitely look into those options.


NBVC

I'm not trying to step on any toes here on a solved thread. Or anyone elses help. But felt the need since it was mentioned. This is something that I use on mine. I am still a total noob to vba, but I was wondering if this would help you NBVC?
Replacing this part:

Please Login or Register  to view this content.

with this:

Please Login or Register  to view this content.

or maybe this would work for you, it would help with the elimination of selecting, since from your code the range seems fixed at this time:

Please Login or Register  to view this content.

Of course, change it however it suits you. Or ignore it.
I had gotten tired of having such big codes myself, and managed to just eliminate alot of the lines by simply removing "(name of border)". I don't know if there is a reason for why this isn't used as much, maybe there is something wrong with it? Feed back is welcome on this.
I just saw the opportunity to maybe help out a little. If it does, great.

David
note: I am still wet behind my ears on Excel and helping others as well with it

dcraker,

I did try your code and it worked great.

Thank you.

I am not sure it made the code run much faster, but it does look a lot cleaner.

I'm glad that it worked for you. And thanks for letting me know.