VBA - Animating an image in a worksheet

[rconverse]

Hello,

I'm trying to make a picture look of a truck look like it's "leaving" the spreadsheet at the start of a macro and "arriving" when the macro is complete. Below is the code I have, which is close. However, it's not working quite the way I'd like. Basically what I'm trying to get is for the image to appear for about 1 second and then "take off", similar to a truck. When the truck is "leaving, it should start at about the middle of the worksheet and "leave" the spreadsheet (which it does in my code below but, if the truck is arriving, it should start at the left hand part of the woorksheet and move to the center (which it also does in my code below).

My issues are that the speed that the image (truck) "leaves" is great but I want the image to show stationary for a second before having the image move. On the "arrival" side, the image moves much, much slower than when it "leaves" (and by a lot, like four seconds).

At the end of the day, I'd like the "AnnimationLeave" sub to show an image for a second at the center of the worksheet, and then start moving that image to the right, off the page visibly. For the "AnnimationArrive" sub, I'd like the image to show up at the left of the workshet and move to the center.

As always, any help is greatly appreciated.

Thank you,
Roger

Sub AnnimationLeave()

Dim i As Long
    
With ActiveSheet
    For i = 400 To 400 Step 1
        Shapes("Picture 2").Visible = True
        Shapes("Picture 2").Left = i
        Application.Wait (Now + TimeValue("0:00:01"))
        DoEvents
    Next
End With

With ActiveSheet
    For i = 405 To 1200 Step 5
        Shapes("Picture 2").Visible = True
        .Shapes("Picture 2").Left = i
        Application.Wait (Now + (ms * 0.000001))
        DoEvents
    Next
End With

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

End Sub
Sub AnnimationArrive()

Dim i As Long

ActiveSheet.Shapes("Picture 2").Left = i
ActiveSheet.Shapes("Picture 2").Visible = True
Application.Wait (Now + TimeValue("0:00:01"))

With ActiveSheet
    For i = 5 To 400 Step 5
        .Shapes("Picture 2").Left = i
        Application.Wait (Now + (ms * 0.000001))
        DoEvents
    Next
End With

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

End Sub
Sub TestTruck()

Call AnnimationLeave

MsgBox ("Testing"), vbOKOnly

Call AnnimationArrive

MsgBox ("Process Complete"), vbOKOnly, "PROCESS COMPLETE"

End Sub

 

[Yongle]

Re: VBA Help - Annimating an image in a worksheet

Your code contains several errors -I have added the missing .
Why from 400 to 400?
Variable ms is not delared and has not been given a value - why is it in the code?

Sub AnnimationLeave()

Dim i As Long
    
With ActiveSheet
    For i = [COLOR=#0000cd]400[/COLOR] To [COLOR=#0000cd]400[/COLOR] Step 1
        [SIZE=4][COLOR=#ff0000].[/COLOR][/SIZE]Shapes("Picture 2").Visible = True
        [SIZE=4][COLOR=#ff0000].[/COLOR][/SIZE]Shapes("Picture 2").Left = i
        Application.Wait (Now + TimeValue("0:00:01"))
        DoEvents
    Next
End With

With ActiveSheet
    For i = 405 To 1200 Step 5
        [SIZE=4][COLOR=#ff0000].[/COLOR][/SIZE]Shapes("Picture 2").Visible = True
        .Shapes("Picture 2").Left = i
        Application.Wait (Now + ([COLOR=#006400]ms[/COLOR] * 0.000001))
        DoEvents
    Next
End With

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

End Sub
Sub AnnimationArrive()

Dim i As Long

ActiveSheet.Shapes("Picture 2").Left = i
ActiveSheet.Shapes("Picture 2").Visible = True
Application.Wait (Now + TimeValue("0:00:01"))

With ActiveSheet
    For i = 5 To 400 Step 5
        .Shapes("Picture 2").Left = i
        Application.Wait (Now + ([COLOR=#006400]ms[/COLOR] * 0.000001))
        DoEvents
    Next
End With

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

End Sub
Sub TestTruck()

Call AnnimationLeave

MsgBox ("Testing"), vbOKOnly

Call AnnimationArrive

MsgBox ("Process Complete"), vbOKOnly, "PROCESS COMPLETE"

End Sub

 

[Yongle]

Re: VBA Help - Annimating an image in a worksheet

To centre an image horizontally, you can use this technique which takes account of both the image width and the application width

Sub CentrePicture()
    With ActiveSheet.Shapes("Picture 2")
        .Visible = True
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
   End With
End Sub

 

[rconverse]

Re: VBA Help - Annimating an image in a worksheet

Thank you for the replies. I'll give this a try later this evening.