PC Tips and Tricks to make your life easier

Archive for April 18th, 2010

Employing UserForms In Microsoft Excel 2007

April 18, 2010 By: lilybird Category: Software

No matter what organization you work for, it is almost certain that either you or one of your colleagues uses Microsoft Excel. Different users manage to achieve differing degrees of expertise with this powerful program and one of the advanced features it offers is automation through the creation of Excel macros using Visual Basic for Applications (VBA). If you are able to persuade your company to get you some Excel VBA training, go for it. It will be a worthwhile investment.

When you are developing Excel VBA applications, one of the features at your disposal is UserForms. These are custom forms which pop up and float above the users worksheet complementing and sometimes entirely replacing interaction with the worksheet. Each UserForm can contain a variety of controls; some for input, some for output and some for design and display.

Adding controls to your UserForms is done visually in design mode using a floating palette called the Toolbox. You can add controls in two ways: firstly, you can drag a control icon from the Toolbox onto the form to create an item at the default size and, secondly, you can select a control icon on the Toolbox and then draw the control at the required size and position.

To set the various attributes of each control, use the Properties Window. (To display both the Toolbox and the Properties Window, choose the appropriate option from the View menu.) One of the first attributes you should always set is the name of the control. Excel will automatically assign a name to each control that you add to a form, such as “TextBox1”; but it is much better to give each control a name which describes its function.

It is also helpful to precede the name of each control with a two or three letter prefix which denotes its type. Thus, for example, you might use the prefix “lbl” for all Label controls, “txt” for all TextBox controls, and so forth. The benefit of naming your controls becomes apparent when you begin to code. Excel’s Intellisense feature, which displays keywords automatically as you enter code, will also include the name of your controls. So, if you are creating code relating to a TextBox control, you simply type your prefix (for example, “txt”) and up pop the names of all of your TextBox controls for you to choose from. A great way of preventing one source of silly errors!

Mastering Microsoft Office Excel VBA is a good move for any IT specialist. Need to master MS Excel VBA? We offer Microsoft Excel 2007 VBA tuition in London and all over the UK.