Macros 3 -User Forms

In this assignment we will learn about

         Creating a user form

         Creating macro sub-procedures.

         Editing the properties of an object

         Using form controls in a user form.

         Creating an animation in Excel

         Creating a splash screen in Excel



Task - Create an ActiveX control

1.      DevelopTab -> ActiveX Controls -> Command Button. Draw the button. Notice that there are several types of ActiveX controls you can insert into a worksheet.

2.      Assign an Active X control to the button.

3.      Click on the new Command control you have just inserted into the worksheet.

4.      Click on the Properties icon. Here is where you can control what the ActiveX control does. Change the name property and the description property.

Try it.




Introducing User Forms†††† Time = 5:00


To get to the VBA Development screen in Excel, press Alt + F11

Right Click on MicroSoft Excel Objects. Select Insert.

Your choices are User Form, Module, and Module Class.Select UserForm.

The UserForm template will appear in the working area. The UserForm name will appear in the list in VBA Projects window.

If the properties are not showing in the bottom left, press F4 or Click on the View menu and select Properties.

The Property options are listed on the left. their current setting is shown on the right. To change a property setting, just click on the setting. Itís that simple.

In the properties window, Select the Name property. Type in a new Name, MyUserForm, for your UserForm.

Click on the UserForm template (in the working area.) The Toolbox should appear. It contains tools that you can insert into your UserForm.

You can also Show/Hide the toolbox by clicking on the View Menu and selecting Toolbox.

Just above the upper left corner of the working area, in the row of icons. you will see an icon of a triangle pointing to the right. That is the icon to push when you want to test your UserForm.

Click on the triangle to view the form. Click on the red X in the upper corner of the form to return to the programming windrow.


The next part of the video talks about how to change properties.


At 3:45 he shows how to create a VBA subroutine to use with a FormControl.

In the VBA Projects list, click on one of the modules.TheVBA editor window opens.You can type code directly into this window to create a subprocedure.This is basically the same thing as a macro.

Note that sub procedures always start with the word Sub followed by the name of the procedure Sub†† openUserForm()

The () is a placeholder. The name of the specific Userform to open will be entered as part of the first command.

The commands go next†††† MyUserform.Show

The subprocedures (macros) always end with EndSub



Task: Recreate the user form shown in the video.


UserForm Command ButtonsTime = 6:13

This video shows how to work with the Control Tools to:

         Insert a Text box(The icon shows a large A)

         Inert a Command Button (the icon shows an L shape rotated around so it is laying on its back)

         Edit the text on a command button.

         Change the font for a command button.

         Change the background and foreground colors for a command button.

         Create a macro for a command button


To add a macro to a command button, click on the button and then double click to open the VBA editor.



Create a text box control. Label it 2. Change the name of the text box to MyNumber

Create a command button. Label it Double.

Create a macro for the Command button. Use the same code as shown in the video, but change the + 1 (or Ė 1) part of the code option to * 2†††

Run the macro. Did it work?Edit the code to say / 2 instead of * 2 and create a command button called Half to assign the code to.††


Create and Use a Check Box†††† Time = 4:43

Shows how to create a button users can click on to hide or display columns.

Task: Create a Show/Hide button.




Create and Use a Combo Box ††††††† Time = 11:38

Task: Create a user form with a combo box basd on a dynamic range.


Insert an animated GIF into Excel†††† Time = 3:37


Introductions to Animations in Excel†††† Time = 2:39

A brief video to show you what is possible and give us some ideas.

Note that all of the graphics in this video were created in Excel!

The course requires a fee, so we will not be using this trainerís material. If you want to take his entire course (13 hours of videos) the fee on December 20, 2016 was only $15.


Create a Splash Screen††† Time = 5:52

Create another Splash Screen††† Time = 6:07


Task: Create your own splash screen.


A copy of the CFU is due to be turned in during the next class session.I do not accept CFUís through email.


Name _______________________________††††† Date___________†††††† Section _______

Check for Understanding Ė Macros 3

Y/N I can create a user form

Y/N I can create macro sub-procedures.

Y/N I can edit the properties of an object

Y/N I can use form controls in a user form.

Y/N I can create an animation in Excel

Y/N I can create a splash screen in Excel