Application.ScreenUpdating is ineffective

[Ken McLennan]

G'day there Once Again,

As usual, I've returned to ask advice after a dismal failure with
a Google search. Actually, my search was successful, but none of the 718
returned hits actually dealt with my problem.

I'm using a small one liner in a module to halt & begin screen
updating:

Public Sub scrnOn()
Application.ScreenUpdating = True
End Sub

Public Sub scrnOff()
Application.ScreenUpdating = False
End Sub

A simple call to the appropriate procedure should, I would have
thought, toggled screenupdating on & off. However, I found that my
routine for clearing cellcontents from a 13 x 18 range flickers up &
down is not what I was trying for. I bunged in a breakpoint on the End
Sub line, and found that after executing ScreenUpdating = False,
ScreenUpdating is actually True. The code does nothing. I then tried
replacing my call to the Sub with a direct statement:

Application.ScreenUpdating = False

and the result was the same. The next line of code after this still had
Application.ScreenUpdating as True!!

I've not read of any setting which could force either state
permanently, although just because I've not heard of one doesn't mean
there isn't. Has anyone else struck this scenario? I can't find anything
on Google, nor in the archives. There are plenty of entries with both
"Application.ScreenUpdating" and "Error", but none that apply to this
situation. Perhaps I'm unique?

Any ideas will be gratefully looked at.

I should point out, that the rest of the worksheet and code works fine.
The cellcontents cleaning out works, but the cells in my range flicker
badly as the code executes.

Hope to hear from you soon,
Ken McLennan
Qld, Australia.

 

[paul.robinson]

Hi
Screenupdating is automatically turned back on at the end of a sub
so...

regards
Paul

 

[Chip Pearson]

Screenupdating is automatically turned back on at the end of a

sub

Very close, but not quite right. ScreenUpdating is automatically
turned by on when control is returned to Excel. Thus if procedure
A calls procedure B, which turns off ScreenUpdating,
ScreenUpdating is False even when B ends. It doesn't turn back on
until A ends.

Your statement is correct if you're talking about only a single
proc, which doesn't call another proc.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Ken McLennan]

G'day there Chip & Paul,


Thanks very much for your quick responses. I appreciate getting
any answer, but one which may point me in the right direction is even
better =)

Your statement is correct if you're talking about only a single
proc, which doesn't call another proc.

Well, that answers why my little one liner didn't work (I thought
it might be something similar to that. However I still get the same
result when I explicitly enter Application.ScreenUpdating = False in my
procedure. This was, however on the sheet page (which I forgot to
mention previously) in the code for 1) a CommandButton, and 2) in the
Selection_Change code. Should I change this over to an ordinary module?
I'll probably try that anyway, but I just wonder whether that should
make any difference.

See ya, and thanks again,
Ken McLennan
Qld, Australia

 

[paul.robinson]

Hi
didn't realise that. I tend to turn ScreenUpdating off by default in
every sub unless I explicitely want the screen to update.
cheers
Paul

 

[Ken McLennan]

G'day there Again, Chip & Paul,

Well, that answers why my little one liner didn't work (I thought

Actually, it doesn't. I stuffed that up in my mind when I
responded. The explanation provided by you, Chip, could easily be
considered pseudo code for what I have running.

result when I explicitly enter Application.ScreenUpdating = False in my
procedure.

That still throws me. I explicitly tell Excel to stop with the
screenupdates and it ignores me. It may be related to my wife in that
regard, but I can't figure out why it doesn't work.

I have this:

Private Sub CommandButton3_Click()
Dim cell As Range
' scrnOff
Application.ScreenUpdating = False
For Each cell In Range("Sheet1!letters").Cells
cell.Interior.Color = vbRed
If Not cell.Interior.Color = vbBlack Then
cell.ClearContents
End If
cell.Interior.ColorIndex = 36
Next
xOut
Application.ScreenUpdating = True
' scrnOn
Application.EnableEvents = True
Exit Sub

If I put a breakpoint at the "For Each..." line, when I then test
Application.ScreenUpdating it indicates "True". I can't understand why
this is so.

This was, however on the sheet page (which I forgot to
mention previously) in the code for 1) a CommandButton, and 2) in the
Selection_Change code. Should I change this over to an ordinary module?
I'll probably try that anyway, but I just wonder whether that should
make any difference.

Still haven't had a chance to do this and probably won't until
tomorrow.

Thanks for listening,
See ya
Ken McLennan
Qld, Australia