application.screenupdating = false not working

[Guest]

I've got a dead simple bit of code where I switch screenupdating on, run a
small section of code (basically runs a routine that puts up a percentage of
progress so the user doesn't stare at a mental screen but still gets some
feedback) and then I try to switch the updating off again so that the code
can carry on.

Problem is that the updating isn't switching off, if I go through the code
line by line it gets to the '=false' line and it executes without any errors
but in the watchwindow screenupdating is still showing as being '=True'.

Never come across this before, any suggestions?

 

[Niek Otten]

What's your code?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've got a dead simple bit of code where I switch screenupdating on, run a
| small section of code (basically runs a routine that puts up a percentage of
| progress so the user doesn't stare at a mental screen but still gets some
| feedback) and then I try to switch the updating off again so that the code
| can carry on.
|
| Problem is that the updating isn't switching off, if I go through the code
| line by line it gets to the '=false' line and it executes without any errors
| but in the watchwindow screenupdating is still showing as being '=True'.
|
| Never come across this before, any suggestions?

 

[Guest]

Hi Sabre -

The ScreenUpdating property always displays a value of true when you query
its value within the VB Editor. This and other application properties
(ScreenUpdating, EnableEvents, etc.) always show a True value even when
you've set them to False (I don't know why). The proof of whether it is
working is if screenupdating is suppressed when you want it to be.

 

[Guest]

Thanks for the replies, that explains why it's not showing up as false then.

The snippet of code is below, there's just the one Sub in the module.

Sub ClearInvalidSales()

'Application.ScreenUpdating = False
< VARIABLES SET IN HERE >
<SETUP SHEETS (CLEAR CELLS etc.)>


'@@@ Clear blank rows and uninvoiced sales
RowCounter = 1
BlankCounter = 0
TotalRows = 0

While BlankCounter < 10
Range("C" & RowCounter).Select
If Selection.Value = "" Then
Rows(RowCounter & ":" & RowCounter).Select
Selection.Delete shift:=xlUp
BlankCounter = BlankCounter + 1
ElseIf Not Selection.Value = "I" Then
Rows(RowCounter & ":" & RowCounter).Select
Selection.Delete shift:=xlUp
BlankCounter = 0
RowCounter = RowCounter - 1
Else
BlankCounter = 0
End If
RowCounter = RowCounter + 1
TotalRows = TotalRows + 1
+ Application.ScreenUpdating = True
Sheets("Progress").Select
Range("D5").Select
Selection.Value = TotalRows
Sheets("Sales").Select
+ Application.ScreenUpdating = False
Wend

The idea I said before at this point is just that everytime a row is checked
'rowcounter' is incremented by 1. This happens in the background but so that
the user stays informed the sheet 'Progress' is updated visibly then the
screenupdating (between the '+') is switched off again to run some more code
in the background.

 

[Niek Otten]

<The ScreenUpdating property always displays a value of true when you query its value within the VB Editor>

It doesn't for me!

Sub test()
Dim a As Double
a = 1
Application.ScreenUpdating = False
a = 2
Application.ScreenUpdating = True
End Sub

I set a watch for a and for Application.Screenupdating and saw both change at the expected moment.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi Sabre -
|
| The ScreenUpdating property always displays a value of true when you query
| its value within the VB Editor. This and other application properties
| (ScreenUpdating, EnableEvents, etc.) always show a True value even when
| you've set them to False (I don't know why). The proof of whether it is
| working is if screenupdating is suppressed when you want it to be.
| --
| Jay
|
|
| "Sabre" wrote:
|
| > I've got a dead simple bit of code where I switch screenupdating on, run a
| > small section of code (basically runs a routine that puts up a percentage of
| > progress so the user doesn't stare at a mental screen but still gets some
| > feedback) and then I try to switch the updating off again so that the code
| > can carry on.
| >
| > Problem is that the updating isn't switching off, if I go through the code
| > line by line it gets to the '=false' line and it executes without any errors
| > but in the watchwindow screenupdating is still showing as being '=True'.
| >
| > Never come across this before, any suggestions?

 

[Norman Jones]

Hi Jay,

'----------------
The ScreenUpdating property always displays a value of true when you query
its value within the VB Editor. This and other application properties
(ScreenUpdating, EnableEvents, etc.) always show a True value even when
you've set them to False (I don't know why). The proof of whether it is
working is if screenupdating is suppressed when you want it to be.
'----------------
'=============>>
Public Sub TestIt()
Const sStr = "ScreenUpdating is on = "

With Application
.ScreenUpdating = False
MsgBox sStr & .ScreenUpdating
.ScreenUpdating = True
MsgBox sStr & .ScreenUpdating
End With
End Sub
'<<=============

 

[Guest]

Sorry about the formatting, it was clearer when the tabs were still in there.

 

[Guest]

Sabre

I notice your Application.Screenupdating = False has been remmed out. Remove
the appostrophe

Regards
Peter

 

[Guest]

yeah I rem'd that out deliberately while I was trying to debug it, it is
overrideen as soon as it gets into the 'While' loop later on anyways, I just
rem'd it to minimise confusion.

 

[Guest]

Just tried using the bit of code provided by Norman Jones for debugging and
it set to True and False as it should. Removed the debugging code and it all
started working perfectly!

Weird. But thanks Norman all the same!

 

[Guest]

Hi Norman and Niek -

Thanks for the clarification. When I run your respective test procedures,
my Watch Window does display the expected values for
Application.ScreenUpdating, but my QuickWatch always shows a value of True
(even when its value is False, as confirmed in the Watch Window). Also,
hovering the cursor over the ScreenUpdating term in the code always shows
"True". Do your QuickWatch dialogs behave similarly (always show
ScreenUpdating=True)?

Excel 2003/WinXP

 

[Guest]

Hi Sabre -

Glad to hear working with Norman's code set your procedure back on track.

While trouble-shooting your problem, I noticed that your procedure skips the
testing of the column C cell after it finds a blank and deletes the row. If
this is what you intended, disregard this post.

However, if you want to test every row for the presence of "I", consider
adding the following line:

RowCounter = RowCounter - 1

After:

BlankCounter = BlankCounter + 1