I am experiencing some odd behavior in some of my VBA and I'm hoping
someone can help me out. I have a rectangle on my sheet that I'm
trying to toggle on and off during a long VBA process to let the user
know something is happening in the background. My code is below:

Sub BuildAll()
Call DoIt("Rectangle 1", True) 'Make the updates are in progress
message appear
Application.ScreenUpdating = False
Call BuildSubj
Call BuildAud
Call BuildCert
Call BuildCat
Application.ScreenUpdating = True
Call DoIt("Rectangle 1", False) 'Make it disappear
End Sub

Sub DoIt(ShapeName, Vis)
ActiveSheet.Shapes(ShapeName).Visible = Vis
End Sub


The odd thing is that if I step through the BuildAll (using F8), the
rectangle appears and disappears like I would expect. If I just run
the BuildAll sub the rectangle never appears.

Any thoughts on this?

Maybe try inserting the line

DoEvents

Right before Application.ScreenUpdating = False

I don't know why, but sometimes it makes a difference.

Hth

-John Coleman






On Apr 1, 7:16 pm, "Ferris" wrote:
I am experiencing some odd behavior in some of my VBA and I'm hoping
someone can help me out. I have a rectangle on my sheet that I'm
trying to toggle on and off during a long VBA process to let the user
know something is happening in the background. My code is below:

Sub BuildAll()
Call DoIt("Rectangle 1", True) 'Make the updates are in progress
message appear
Application.ScreenUpdating = False
Call BuildSubj
Call BuildAud
Call BuildCert
Call BuildCat
Application.ScreenUpdating = True
Call DoIt("Rectangle 1", False) 'Make it disappear
End Sub

Sub DoIt(ShapeName, Vis)
ActiveSheet.Shapes(ShapeName).Visible = Vis
End Sub

The odd thing is that if I step through the BuildAll (using F8), the
rectangle appears and disappears like I would expect. If I just run
the BuildAll sub the rectangle never appears.

Any thoughts on this?

Thanks, but I'm still getting different results when I step-through
vs. run. Any other thoughts?


On Apr 1, 5:23 pm, "John Coleman" wrote:
Maybe try inserting the line

DoEvents

Right before Application.ScreenUpdating = False

I don't know why, but sometimes it makes a difference.

Hth

-John Coleman

On Apr 1, 7:16 pm, "Ferris" wrote:

I am experiencing some odd behavior in some of my VBA and I'm hoping
someone can help me out. I have a rectangle on my sheet that I'm
trying to toggle on and off during a long VBA process to let the user
know something is happening in the background. My code is below:

Sub BuildAll()
Call DoIt("Rectangle 1", True) 'Make the updates are in progress
message appear
Application.ScreenUpdating = False
Call BuildSubj
Call BuildAud
Call BuildCert
Call BuildCat
Application.ScreenUpdating = True
Call DoIt("Rectangle 1", False) 'Make it disappear
End Sub

Sub DoIt(ShapeName, Vis)
ActiveSheet.Shapes(ShapeName).Visible = Vis
End Sub

The odd thing is that if I step through the BuildAll (using F8), the
rectangle appears and disappears like I would expect. If I just run
the BuildAll sub the rectangle never appears.

Any thoughts on this?

Figured it out...If I add a 1 second delay after the command to show
the rectangle it works. Not sure why the delay is necessary, but it
does work.

On Apr 2, 11:33 am, "Ferris" wrote:
Figured it out...If I add a 1 second delay after the command to show
the rectangle it works. Not sure why the delay is necessary, but it
does work.

Truly strange.

If I try:

Sub BuildAll()
Call DoIt("Rectangle 1", True) 'Make visible
Application.ScreenUpdating = False

Call waste(3) 'a fill in for other subs

Application.ScreenUpdating = True
Call DoIt("Rectangle 1", False) 'Make it disappear
End Sub

Sub waste(s As Double)
Dim start As Double
start = Timer
Do While Timer < start + s
Loop
End Sub

Sub DoIt(ShapeName, Vis)
ActiveSheet.Shapes(ShapeName).Visible = Vis
End Sub

I am able to reproduce your problem, which seems to be some sort of
timing issue where Application.ScreenUpdating = False is executed
before the rectangle is visible.
For me, adding DoEvents after Call DoIt and before
Application.ScreenUpdating = False fixes the problem at once. On the
other hand, not even

Sub BuildAll()
Dim start As Double
Call DoIt("Rectangle 1", True) 'Make visible
start = Timer
Do While Timer < start + 5
Loop
Application.ScreenUpdating = False ...

works for me. Is this what you meant by a 1 second delay? (5 second
over-kill attempted in my case). On my machine, no mere delay seems to
work.

Interestingly, the following seems to work:

Sub BuildAll()
Call DoIt("Rectangle 1", True) 'Make visible
Application.ScreenUpdating = True
Application.ScreenUpdating = False

If this works it might be more portable across different versions of
Excel / machine configurations.

Hth

-John Coleman

On Apr 2, 10:34 am, "John Coleman" wrote:
On Apr 2, 11:33 am, "Ferris" wrote:

Figured it out...If I add a 1 second delay after the command to show
the rectangle it works. Not sure why the delay is necessary, but it
does work.

Truly strange.

If I try:

Sub BuildAll()
Call DoIt("Rectangle 1", True) 'Make visible
Application.ScreenUpdating = False

Call waste(3) 'a fill in for other subs

Application.ScreenUpdating = True
Call DoIt("Rectangle 1", False) 'Make it disappear
End Sub

Sub waste(s As Double)
Dim start As Double
start = Timer
Do While Timer < start + s
Loop
End Sub

Sub DoIt(ShapeName, Vis)
ActiveSheet.Shapes(ShapeName).Visible = Vis
End Sub

I am able to reproduce your problem, which seems to be some sort of
timing issue where Application.ScreenUpdating = False is executed
before the rectangle is visible.
For me, adding DoEvents after Call DoIt and before
Application.ScreenUpdating = False fixes the problem at once. On the
other hand, not even

Sub BuildAll()
Dim start As Double
Call DoIt("Rectangle 1", True) 'Make visible
start = Timer
Do While Timer < start + 5
Loop
Application.ScreenUpdating = False ...

works for me. Is this what you meant by a 1 second delay? (5 second
over-kill attempted in my case). On my machine, no mere delay seems to
work.

Interestingly, the following seems to work:

Sub BuildAll()
Call DoIt("Rectangle 1", True) 'Make visible
Application.ScreenUpdating = True
Application.ScreenUpdating = False

If this works it might be more portable across different versions of
Excel / machine configurations.

Hth

-John Coleman

The DoEvents inserted before setting the ScreenUpdating = False didn't
do the trick for me. However, I've found my wait timer can cause an
Out of System Memory error if I'm running Real Player (streaming
content) at the same time if I select two ranges before I run it. For
example, the code below would throw the OOM every time during the 1
second pause until I removed the first A3 selection. I have 2Gb of RAM
and I was definitely not running out of memory... Weird stuff. I'll
have to see if O'Reilly has a VBA Annoyances book...

Range("A3").Select 'I have my view frozen and I want to be sure I'm in
the part of the sheet that will show the rectangle
Range("A2").Select 'Go to my header row
Call DoIt("Rectangle 1", True)

Sub DoIt(ShapeName, Vis)
ActiveSheet.Shapes(ShapeName).Visible = Vis
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime 'Gives time for the rectangle to show
End Sub