Create UserForm Control Templates

Add to Favorites
Author: | Edits: don

How to create a UserForm Controls template that will allow you to quickly add pre-formatted buttons, input areas, check boxes, and any other control into a UserForm, as well as groups of controls.

This saves you a lot of time by being able to store commonly used elements and groups of elements within the UserForm Toolbox so that you can quickly drag-and-drop the controls into the form and have them already formatted and positioned correctly.

(This tutorial assumes that you have a general understanding of UserForms, if not, view our course on UserForms in Excel to learn more.)

Sections:

Create a UserForm Controls Template

Organize Templates in their Own Section in the Toolbox

Save Groups of Controls Together

Change Template Button Image and Name

Change Section Tab Name

Delete Template Button or Template Section

Notes

Create a UserForm Controls Template

This works for any control in the Toolbox for forms.

Short form of the following steps: Create controls > drag to Toolbox.

  1. Create the buttons or controls or layout on an actual form. You have to create the form and controls first before you can save the template.
    Here is our sample form:
    11beb4403effd2882966bac81e59a05d.png
  2. Format the controls exactly as you want them to appear. Change the Caption or display text, the font size and appearance; change all elements that you want to save. If you change the Name of the button, what you use to reference it in VBA, that will also be saved with the template; basically everything from the Properties window will be saved. (no vba code will be saved in the template)
  3. If you want to save a group of elements, make sure you have the spacing and layout correct.
  4. Make sure the Toolbox is visible. If you don't see it, click the form and go to ViewToolbox
    a9a121e3ff7525d828a3f23e06e3c05c.jpg
    You should now see the toolbox:
    4d98a11be993eeb84ebd30e437682622.jpg
  5. Left-click the desired button on the UserForm and hold it down while you drag it to the Toolbox.
  6. You should now have a new item appear that is the new template for your control.
    e38460cd01025058ecb55d04e935244b.jpg
  7. You can now drag this control onto the UserForm and it will automatically have all of the settings and properties that you attached to it before you dragged it into the Toolbox.

Note these changes will automatically save to Excel, at least in later versions, so that you can use them in any project going forward.

This will not save any VBA code that you may have associated with the controls, only the settings changed in the Properties window will be saved.

You can also save groups of controls and arrange them on their own tab; look to the next sections for this.

Organize Templates in their Own Section in the Toolbox

In the above example, the template is placed in the same section as the default controls, but we can make a new tab and store them there as well.

Right-click the Controls tab and click New Page

011c9613cb0c70a48685fa9b9ed8e5ad.jpg

Now, you will see a new tab in the Toolbox:

61f85dde2e139c822dd7ebc653e1f6f8.jpg

You can drag and drop controls just like you did on the default tab and everything will be saved as usual.

Save Groups of Controls Together

You can save multiple controls together and this will not only save the controls and their settings, but their spacing from each other; this is what really allows you to quickly build UserForms that have similar reusable sections.

Organize all controls as you want and then hold down the left mouse button and drag it over all of the controls that you want to save and release.

Then, you'll have multiple controls selected like this:

6f2ef738790778a6efde0ea3f19e1c07.png

Now, click one of the controls and drag it over to the Toolbox and release.

You'll see a new button appear and that is your new template button for all of the controls that you just selected.

864830812cf6e562b2be1bf4da338e34.png

This is maybe one of my favorite little features in Excel because it will save you so much time!

In this example, I saved all of the controls except the title, but, a better example is to, perhaps, save the buttons at the bottom of the form that you might use in all of your forms, such as the OK and Cancel buttons. This way, when creating a new form, you just use the template from the Toolbox and have the buttons along with their formatting and spacing and names all correctly input instead of having to do it all by hand.

Change Template Button Image and Name

You can easily change the template button information so that it is more useful.

Right-click the template button in the Toolbox and click Customize New Group

edbaeb6279ebb6672dafbe4f6ed65636.jpg

5c283c020de5ce3d855a7693f3548fc6.png

From this window, you change the text that appears when you hover over the control in the Toolbox and also the image that appears for the button.

For images, you can use small images that you find on your computer, Excel doesn't automatically give you thousands of cool image options, unfortunately.

Note if you saved a single control, it will take the default name and image of that control type along with New in front of the name. So, the commandbutton saved in the first example, looks like a commandbutton in the Toolbox and is called New CommandButton. Of course, you can rename it and change its image as you like.

Change Section Tab Name

Righ-click the tab and click Rename...

Then, you'll see a window like this:

a65626519d010dcaaca3154982c1bc4c.png

Rename it:

8325b60c5f7be3c95e38d0b725dec4b4.png

Hit OK and view result

6fcd64242026e3810d39f196ecacf0d6.jpg

Delete Template Button or Template Section

Right-click template button and click the option with Delete in front of it.

Right-click the new tab and click the option with Delete in front of it.

Notes

This is a fun example of how you can make the creation of UserForms in Excel much easier and faster and with more continuity.

Download the sample file for this tutorial to work with the form used in the above examples.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
UserForm Controls
Tutorial: This tutorial gives you an overview of what a UserForm Control is and how to add them to y...
UserForm Controls Events
Tutorial: Explanation of Events for Controls in UserForms in Excel. This includes what they are, how...
UserForm Events
Tutorial: Explanation of UserForm Events in Excel. This includes what they are, how to use them, and...
UserForm Properties List and Explanation
Tutorial: This is a listing of all properties for the UserForm in Excel. Each property includes an e...
Loop Through Controls in a UserForm
Tutorial: How to loop through all controls in a UserForm; this allows you to do things like get valu...
UserForm Control Properties Overview
Tutorial: Overview of Control properties in UserForms; this includes what they are, how to change th...
Tutorial Details
Downloadable Files: Excel File
Similar Content
UserForm Controls
Tutorial: This tutorial gives you an overview of what a UserForm Control is and how to add them to y...
UserForm Controls Events
Tutorial: Explanation of Events for Controls in UserForms in Excel. This includes what they are, how...
UserForm Events
Tutorial: Explanation of UserForm Events in Excel. This includes what they are, how to use them, and...
Excel Forum