 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
|
|
Creating a Template
One thing you will find very often when creating UserForms via Excel VBA is that you will probably use the same types of Controls numerous time over. For instance, nearly every UserForm will have a Cancel button and quite possibly an OK button. Excel allows us to easily create Template Controls which we can format and name appropriately so that each time the Control is needed, rather than repeat the process over and over again, we can simply select the Template Control that we have created and place it on to a UserForm. This is extremely easy to do and can save many hours of repetitious work in the long run.
For example, as we have mentioned above a Cancel button and an OK button are very common buttons to have on a UserForm. Lets see now how we can easily create these two buttons and then make them into Templates.
First of all, insert a new UserForm and on this UserForm place two CommandButtons. Change the name of CommandButton1 to CancelButton (one word), size it accordingly and change any other appearance Properties that you would normally associated with it, eg; bold font, background colour etc. Do exactly the same for the OK button, but of course change the name Property of this button to OKButton (one word). Now, with the Control Toolbox visible, right click on the tab named Controls and select New Page. Now right click on this New Page that has been created and select Rename, and give it a meaningful name such as Templates. All you simply need to do now is to left click to select the Control on your UserForm and drag and drop the button or whichever Control you have used as a Template on to the new page called Templates. Each time you now use Excel (providing of course that you have saved) you will have a new page on your Control ToolBox called Templates and on that Control Toolbox page will be your most commonly used Controls all pre-named and pre-formatted. You can also, if you wish, right click on these Controls that you have placed onto your Template sheet, select Customise New<ControlName> and :
Type in some ToolTip text (this is the text you would see when hovering your mouse pointer over the Control)
Load a new picture to represent the Control on the Templates sheet. Important to note that the picture you use will only be seen while it is residing on the Control ToolBox. Once the Control is dragged and dropped onto the UserForm, it will appear as it did before.
Possibly the only thing that we cannot do and it would be nice to, is also to have code pre-built into the Control as well. Unfortunately, saving the Control as that type of Template does not allow this flexibility. If we do want to give ourselves this extra flexibility, possibly the simplest way to do this is to insert a UserForm and on this UserForm insert or place all the commonly used Controls, name them and change any other Properties appropriately, then go through and code the Events for the Controls as you see fit and then give the UserForm perhaps a name such as ControlsTemplate. Right click on the ControlsTemplate form while within the Project Explorer and select Export File. Save the file to a location (it is probably best to create a folder in its default location) and click Save. Each time you need a specific Control(s) you simply right click within the Project Explorer and select Import File and import the UserForm housing all your Controls with all Properties and coding pre-done.