CODEWITHSUNDEEP
excelvbaloopsrefresh
Lucas
Lucas

Reputation: 511

Excel VBA: Screen doesn't refresh during loop

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.

Upvotes: 2

Views: 11017

Answers (2)

Geoff Kendall
Geoff Kendall

Reputation: 1415

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"))

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61860

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.

Upvotes: 4

Related Questions