VBA Animation in UserForm (4 Suitable Examples)

In this article, I will demonstrate various examples of VBA animation in UserForm in Excel. These examples will not only enhance the aesthetics and visual appeal of your UserForms but also captivate and surprise your audience. By utilizing animation effects in your UserForms, you can create a more engaging and interactive experience for your users, making your applications stand out from the rest. With VBA, creating animations can be used to draw attention to important information, provide visual feedback, or simply add some flair to your UserForms. To know more, read this article carefully.

The above overview shows an animated clock in UserForm. Follow along to learn about how you can create this and other animations.

VBA Animation in UserForm: 4 Suitable Examples

In this section, we will demonstrate 4 useful examples of VBA animation. Let’s explore those examples one by one.

1. Creating an Animated Clock in UserForm

Sometimes, we feel the need to show the current date and time in UserForm. In this example, I will show how we can display the current date and time in UserForm. We will also display a scrolling text beneath the date and time. Our Animated Clock will look like this.

To learn how to create the UserForm, follow the steps below.

Steps:

First, we need to go to the Developer tab and open the Microsoft Visual Basic for Application window by clicking the Visual Basic command. Alternatively, we can open the window by using the Alt+F11 shortcut key.

As a result, we will see the Microsoft Visual Basic for Application From there, insert a new Userform by going Insert >> UserForm.

As a result, Excel will create a new userform like this.

Now, we will insert different tools from the Toolbox. Here, we need 3 Labels and 1 To insert a Label, click on the Label icon on the Toolbox and then drag it to your desired position on the UserForm.

Likewise, drag another two Labels and put them below the first Label.

Now, we will Insert a CommandButton.

Now, we need to work on the Properties of each inserted element. First, click on the Label1 and open the property tab by going to View >> Properties Window if the properties Window is not already there.

As a result, the Properties tab should be visible in the left corner. Now, we will edit the Caption of Label1.

We also need to change the Font Style and Size of Label1. To do that, click on the 3 dots on the Font option and choose font style and size from the pop-up window.

From the popup window, I chose the following Font style and size.

Now, to make the UserForm more attractive, change the ForeColor. I chose Green from the color palette.

Now, change the Properties of the rest of the components as per your taste. I have formatted all the components like this.

Here, on Label3, whatever caption we set, will scroll when the UserForm will run. Now that we designed the layout of our UserForm, now we need to write the VBA code.
To write down the VBA code, double-click on any part of the UserForm to open the code window.

VBA Code:

Private Sub CommandButton1_Click()
End
End Sub
Private Sub UserForm_Activate()
On Error Resume Next
Do
Label1 = Format(Date, "dddd, dd mmm yyyy")
Label2 = Format(Time, "hh:mm:ss AM/PM")
Label3.Left = Label3.Left - 2
If Label3.Left <= 0 - Label3.Width Then Label3.Left = Me.Width
For i = 1 To 8000000
Next
DoEvents
Loop
End Sub

📌 How Does the Code Work?

Private Sub CommandButton1_Click()
End
End Sub

This code is activated when CommandButton1 is clicked. It ends all the running Subroutine.

Private Sub UserForm_Activate()

Here, the subroutine will run whenever the UserForm will be activated.

On Error Resume Next

The code will neglect any error and resume the next line.

Label1 = Format(Date, "dddd, dd mmm yyyy")

In Label1 the Current Date will be shown and its format wil be “dddd, dd mmm yyyy”. Here, you can modify the format to display according to your choice of format.

Label2 = Format(Time, "hh:mm:ss AM/PM")

In Label2 the current time will be shown and its format will be “hh:mm:ss AM/PM”. Like ythe Label1, you can also modify the format.

Left = Label3.Left - 2
If Label3.Left <= 0 - Label3.Width Then Label3.Left = Me.Width

Here, the position of Label3 is continuously shifted to the left of the UserForm until it reaches at the left end. When its position is on the Left edge of the UserForm, the position of Label3 is shifted towards the right end of the UserForm. When the code will run under a For loop, it will look like a scrolling text.

For i = 1 To 8000000
Next
DoEvents

The above code is executed within a For loop. Hence, it becomes dynamic. You can decrease the upper limit of the iteration variable (i) to make the scrolling fast.

Now if you run the UserForm, you will see the Dynamic UserForm with Date, Time, and Scrolling Text.

2. Create Vertical Scrolling Text in VBA UserForm

In this example, we will see how we can display a vertical scrolling text in UserForm. The Vertical Text will look like this.

To create this kind of vertical text, follow the steps below.

Steps:

First, Insert a new UserForm, and inside the UserForm insert a Label and a CommandButton just like the first method. Then we edited the caption according to our needs. In Label1, we write the text that we want to scroll in the caption.

After rewriting the caption of those components, open the code window by double-clicking any part of the UserForm. Then paste the following code in the code window.

VBA Code Syntax:

Private Sub UserForm_Activate()
On Error Resume Next
Do
Label1.Top = Label1.Top - 1
If Label1.Top <= 0 - Label1.Height Then Label1.Top = UserForm2.Height
For i = 1 To 5000000
Next
DoEvents
Loop
End Sub
Private Sub CommandButton1_Click()
End
End Sub

📌How Does the Code Work?

Do
Label1.Top = Label1.Top - 1
If Label1.Top <= 0 - Label1.Height Then Label1.Top = UserForm2.Height
For i = 1 To 5000000
Next
DoEvents
Loop

Here, the code uses a Do loop. Inside the Do loop, the vertical position of Label1 is decreased until it reaches the Top edge of the UserForm. Then the Vertical position of Label1 is set to the height of the UserForm. This loop continues which makes the label scrolling text. If we want to make the scrolling faster, we need to decrease the upper value of iteration variable i.

Private Sub CommandButton1_Click()
End
End Sub

This subroutine is run when the CommandButton1 is clicked. As it is clicked, it will end all the running subroutines.

Now, if we run the code, we will see the following result.

3. Creating Animation Image in UserForm

In this example, we will create an animated scrolling image in UserForm. Sometimes we may need to display a banner, poster, or a photo of something important to notice. If we make the photo scrolling then it will become more noticeable. For example, here I have created a UserForm with a picture of Superman which is floating from right to left.

To make this animation, follow the steps below.

Steps:

First of all, we need to insert a new UserForm. Then in the UserForm, insert an Image and a CommandButton from the Toolbox. We edited the caption of the CommandButton according to our needs.

Then, we inserted the image that I want to scroll by clicking on the Picture property.

Then I selected the picture from the popped-up Load Picture.

As a result, you will be able to see the selected picture on the UserForm. To accommodate the picture inside the Image tool, you may need to resize it.

Now, we need to make the Background color of the UserForm same as the image so that the image while scrolling looks like an integral part of the UserForm. As the background color of my selected image is white, hence I change the color of the UserForm to white as well.

We also need to remove the border of the image if the image has any by changing the BorderStyle to 0-fmBorderStyleNone.

As a result, our image should be ready for animation.

Now, we need to write some code to scroll the image. To do that, double-click anywhere on the UserForm to open the Code Window.
Then, clear all the existing code.
Now, paste the following code.

Code Syntax:

Private Sub UserForm_Activate()
On Error Resume Next
Do
Image1.Left = Image1.Left - 1
If Image1.Left <= 0 - Image1.Width Then Image1.Left = Me.Width
For i = 1 To 8000000
Next
DoEvents
Loop
End Sub
Private Sub CommandButton1_Click()
End
End Sub

📌How Does the Code Work?

Do
Image1.Left = Image1.Left - 1
If Image1.Left <= 0 - Image1.Width Then Image1.Left = Me.Width
For i = 1 To 8000000
Next
DoEvents
Loop

This code will shift the image toward the left direction as long as its right corner reaches the UserForm’s left edge. When it reaches the Left edge of the UserForm, the code then brings the left corner of Image1 to the right edge of the UserForm. This loop continues.

Private Sub CommandButton1_Click()
End
End Sub

When the CommandButton1 will be clicked, it will end all the running subroutines.

Now, if we run the UserForm, we will see the scrolling image on the UserForm which is our VBA animation output in UserForm.

4. Slideshow Using VBA UserForm

In this example, I will show you how you make a slideshow of images (.jpg file) inside a selected folder. After selecting the folder, you will see a slideshow like this.

To create this kind of VBA animation in UserForm, follow the steps below.

Steps:

First, insert a new UserForm. In this UserForm, you need to Insert 2 CommandButtons and 1 Image from the ToolBox.
Then, we need to edit the caption of the After editing, the Layout of the UserForm should look like this.

After rewriting the caption of those components, open the code window by double-clicking any part of the UserForm. Then paste the following code in the code window.

Code Syntax:

Private Sub CommandButton1_Click()
Set Pic_File = Application.FileDialog(msoFileDialogFolderPicker)
With Pic_File
    .Title = "Select a Folder where pictures are"
    If .Show <> -1 Then GoTo Exit_Sub
    My_Path = .SelectedItems(1)
End With
Set Img_Obj = CreateObject("Scripting.FilesystemObject")
Set Img_Folder = Img_Obj.GetFolder(My_Path)
Do Until a = 1
 For Each Obj_File In Img_Folder.Files
 DoEvents
 If Obj_File.Type = "JPG File" Then
 Photo_Var = Photo_Var + 1
 Me.Image1.Picture = LoadPicture(Obj_File.Path)
 Me.Image1.PictureSizeMode = fmPictureSizeModeStretch
 Application.Wait Now + 0.00001
 End If
 Next
 Loop
Exit_Sub:
End Sub
Private Sub CommandButton2_Click()
End
End Sub

📌How Does the Code Work?

Private Sub CommandButton1_Click()

This subroutine will run when the ComamndButton1 is clicked.

Set Pic_File = Application.FileDialog(msoFileDialogFolderPicker)
With Pic_File
    .Title = "Select a Folder where pictures are"
    If .Show <> -1 Then GoTo Exit_Sub
    My_Path = .SelectedItems(1)
End With

A FileDialoge box will ask the User to select a Folder where the pictures are. If nothing is selected then the subroutine will exit.

Set Img_Obj = CreateObject("Scripting.FilesystemObject")
Set Img_Folder = Img_Obj.GetFolder(My_Path)

The Scripting.FileSystemObject is an object in VBA that provides access to the computer’s file system. Img_Folder stores the Folder Path of the selected folder.

Do Until a = 1
.
.
Loop

Here, a Do Until loop is introduced. A new random variable a is taken which was never initialized before. Here, The Do Until loop is essentially an infinite loop as the value of a will never reach 1 in the future.

For Each Obj_File In Img_Folder.Files
 DoEvents
.
.
Next

Here, For Each loop is used to loop through each file in the selected folder. DoEvents will allow the user to interact with the interface while the macro is running.

If Obj_File.Type = "JPG File" Then
.
.
End If

Here, an If statement is used to determine whether the file is a JPG file. We will only take the JPG file as the UserForms in Excel can only load JPG type images.

Me.Image1.Picture = LoadPicture(Obj_File.Path)

If the condition is True then the image file is load to Image1 in UserForm.

Me.Image1.PictureSizeMode = fmPictureSizeModeStretch

The JPG file is stretched across the whole area of image1 in the UserForm.

Application.Wait Now + 0.00001

The next step is delayed by 1 second.

Private Sub CommandButton2_Click()
End
End Sub

This code will run when the CommandButton2 will be clicked. It will end all the running subroutines.

Now, after running the UserForm, click on the Select Folder (CommandButton1).

As a result, a dialogue box will open asking for selecting a folder. From here, select the folder where all the JPG files are. Then click OK.

As a result, a slideshow will be made consisting of all the JPG files in that folder. This is the final VBA animation output in UserForm.

Things to Remember

Be aware of the Name of the Components (Labels, CommandButtons, Images). If the names are not the same as shown in the examples above, the provided code may not work properly.
Always resize the UserForms and components according to your convenience.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Animated UserForm.xlsm

Conclusion

That brings us to the end of this article regarding VBA animation in UserForm. Please share this post with your friends if you find it useful. Kindly let us know if you have any further questions. Finally, please explore Exceldemy for more fun Excel articles.

Get FREE Advanced Excel Exercises with Solutions!

VBA Animation in UserForm (4 Suitable Examples)

VBA Animation in UserForm: 4 Suitable Examples

1. Creating an Animated Clock in UserForm

2. Create Vertical Scrolling Text in VBA UserForm

3. Creating Animation Image in UserForm

4. Slideshow Using VBA UserForm

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply