2

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.

Community
  • 1
  • 1
Lucas
  • 401
  • 1
  • 8
  • 20
  • 1
    Why don't you try with >Application.Wait (Now + TimeValue("0:00:50")) < and remove the Sleep lib and the sleep? – Vityata Oct 13 '16 at 08: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 Oct 13 '16 at 09: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 Oct 13 '16 at 09:27
  • Tried but no luck :( Does it work for you? – Lucas Oct 13 '16 at 10:10
  • Actually not, I was just giving ideas. :) What has worked for me was to put the pictures in a form and to make them visible and unvisible from there. Form.Repaint works perfectly exactly for this. – Vityata Oct 13 '16 at 10:29
  • What kind of form? I tried with an ActiveX image but didn't seem to work. Also, what should the code look like? Is it simply Form.Repaint? Got an example of the syntax? – Lucas Oct 13 '16 at 12:14
  • 1
    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 Oct 13 '16 at 12:37

2 Answers2

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.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • This works! Thanks for the suggestion. So there's no way to reduce the pause to < 1 second? – Lucas Oct 13 '16 at 15:06
  • 1
    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. – Axel Richter Oct 13 '16 at 15:25
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"))
Geoff Kendall
  • 1,307
  • 12
  • 13