Screen Flickers Even with Application.ScreenUpdating set to False

I have a very simple piece of code that will divert the selected cell to cell A1 (hidden behind a logo) so that the users will not see the frame of a selected cell, making this tool look less like an Excel file and more like a normal software program. This sub sits in a normal worksheet (not in modules folder or ThisWorkBook part of the VB code).

The problem is that the screen flickers 2-3 times for each click on the worksheet even with the ScreenUpdating value set to False. It behaves exactly the same even if I comment out the statement that turns ScreenUpdating back to True. Does anybody know why? Thank you in advance for your help!

Please see code below:

Please Login or Register  to view this content.

~ M3atball

You can't Select if you don't want the screen to update.

shg -

Thanks for the quick reply!

I change the statement to Activate and it still flickers. Did you mean that I shouldn't be use the WorkSheet_SelectionChange() event? I cannot think of other ways to detect a user's mouse click.

Thanks!

Please Login or Register  to view this content.

~ M3atball

Hi m3atball

You'll get "Screen Flicker" when you use "Select" or "Activate"because the cursor is physically moving. That's the "Flicker" you see. To avoid the "Flicker', use the "With...End With" convention.

I think.

John

Why not lock the sheet so they can't select anything?

John,
What does With...End with have to do with screen flicker? All it does is hold a reference to an object, so you can perform multiple operations on it without having to refer to it each time.

Hi romperstomper

You know, I don't really know, that's why I said

I think.

It's my limited experience that when you use With...End With, you're neither Selecting nor Activating so that you're not moving the cursor thus causing Screen Flicker. Not true?

In re-reading the OP's query, I must confess I don't see how this apples to the post.

I appreciate ANY feedback that adds to my education.

John

Turning screenupdating off (generally) prevents the screen being redrawn while your code is running but it has to get redrawn at the end so, if you changed the active sheet in some way, such as switching cells, you will appear to get a little flicker.

Originally Posted by jaslake

It's my limited experience that when you use With...End With, you're neither Selecting nor Activating so that you're not moving the cursor thus causing Screen Flicker. Not true?

John,
Sorry, I missed this post.

The two things are not actually related although it is true that you often see parallels. Consider the following 4 examples and hopefully you will see:
One:

Please Login or Register  to view this content.

Two:

Please Login or Register  to view this content.

Three:

Please Login or Register  to view this content.

Four:

Please Login or Register  to view this content.

They all achieve much the same thing, but two do it without selecting. As you can see, the With...End With is not required for that. It is only really useful if you are performing multiple operations on the same object - in example 4 it serves no real purpose at all.

Does that make sense?

krishnap2w2, I have removed your link, we do not allow links to commercial sites here. If you want to offer your (free) services and help here, then we welcome you. If you are just trying to advertise your business, then I would ask that you look for alternative vanues

I have included the basis of your pdf file below...

This article helps to handle the screen updates using macro. We can speed up
our macro by turning off the screen updating while the macro runs, by adding the
below line of code to the macro:
Sub Macro ()
Application.ScreenUpdating = False
Write the necessary code Here
——————-
——————-
Application.ScreenUpdating = True
End Sub.


The prior versions of excel 2000, it was not required to turn Screen Updating back to true. When the macro is finished, Excel would revert back to turning the screen updating to true.
But now whether you are writing macros in Excel 97, Excel 2000, Excel 2002, 2003, 2007 or 2010 always we have to turn the screen updating back to true with the below code:
Application.ScreenUpdating = True

Thanks for that tip

Originally Posted by FDibbins

Thanks for that tip

Shame it's incorrect though.

Thanks for the update on that, Rory

We know. You already posted that thinly-veiled spam and Ford deleted it once...