Excel VBA: Screen doesn't refresh during loop

Question

I'm trying to make an image disappear and reappear while a loop is taking place. The code works as intended when I step through it, but when I run it the screen doesn't update until the loop is finished.

I've tried adding things like DoEvents and ActiveWindow.SmallScroll as found here but nothing seems to work. I have a feeling this problem may have something to do with my PC/settings/version of Excel and that the loop may work on some peoples' machines. I've uploaded a sample file here if you want to try it.

My Code is:

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub ToggleImage()

For i = 1 To 20

Application.ScreenUpdating = True

ActiveSheet.Shapes("Picture 1").Visible = False
ActiveSheet.Shapes("Picture 2").Visible = True

ActiveSheet.Shapes("Picture 1").Visible = True
ActiveSheet.Shapes("Picture 2").Visible = False

Sleep 50


Next


End Sub

Sample workbook is attached.

Why don't you try with >Application.Wait (Now + TimeValue("0:00:50")) < and remove the Sleep lib and the sleep? — Vityata
– Vityata, Commented Oct 13, 2016 at 8:45
Hey Vityata. Unfortunately, I get the same problem when using Wait instead of Sleep. Also, you can't do a time shorter than 1 second so looping 20 times takes far too long (20 seconds). Thanks though. — Lucas
– Lucas, Commented Oct 13, 2016 at 9:14
I have an idea - you can simply put a debug.print, that prints the numbers from 1 to 5000 in the loop, and it would slow the code enough to see the pictures. :) Without any sleep and wait. It would work for sure. — Vityata
– Vityata, Commented Oct 13, 2016 at 9:27
Well, I meant a UserForm, from the VBEditor (where the modules and the classes are). In the form, as an element, you add a few images (as many as you want). Then once the macro starts, you decide which one to show and which one not, using their "Visible" property. The "FormName.Repaint" is needed at the end of each loop, to update the images that should be shown and which should not be shown. I hope I have explained it well. — Vityata
– Vityata, Commented Oct 13, 2016 at 12:37

Axel Richter · Accepted Answer · 2016-10-13 12:22:33Z

4

The DoEvents must have time to do events ;-). So it is totally useless if you call it once after a sleep. It must work during the pause.

The following should work:

Sub ToggleImage()

 Dim dTime As Double

 For i = 1 To 20

  'ActiveSheet.Range("a1").Value = i

  ActiveSheet.Shapes("Picture 1").Visible = False
  ActiveSheet.Shapes("Picture 2").Visible = True

  dTime = Time
  Do While Time < dTime + 1 / 24 / 60 / 60 / 2
   DoEvents
  Loop

  ActiveSheet.Shapes("Picture 1").Visible = True
  ActiveSheet.Shapes("Picture 2").Visible = False

  dTime = Time
  Do While Time < dTime + 1 / 24 / 60 / 60 / 2
   DoEvents
  Loop

 Next


End Sub

But you will not be able shortening the pause to 50 milli seconds. Even the refreshing the sheet will take more time.

answered Oct 13, 2016 at 12:22

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lucas Over a year ago

This works! Thanks for the suggestion. So there's no way to reduce the pause to < 1 second?

Axel Richter Over a year ago

The problem is not "reduce the pause to < 1 second". In my example it is 0.5 second (1 / 24 / 60 / 60 / 2). But if the refresh process of the sheet gets not ready in this time, then you can reduce the pause as you want. Try 0.25 seconds (1 / 24 / 60 / 60 / 4) or lesser. It will not have any effect.

Geoff Kendall · Accepted Answer · 2023-04-12 17:03:07Z

0

The crucial thing seems to be to allow the application time to refresh the screen. I found that a combination of the two answers above did the trick, with the time set at 1 second:

Application.Wait (Now + TimeValue("0:00:01"))

answered Apr 12, 2023 at 17:03

Geoff Kendall

1,43514 silver badges13 bronze badges

Collectives™ on Stack Overflow

Excel VBA: Screen doesn't refresh during loop

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related