Drawing UserForms in the VBA code editor
Part five of a seven-part series of blogs

Learn how to create your own custom dialog boxes in VBA, using UserForms. This is the first part of a three-part series (the other parts are on Writing Code for UserForms and Creating Advanced Controls).

  1. An Introduction to UserForms in VBA
  2. Creating and Using UserForms in VBA
  3. The Properties Window
  4. Selecting Forms and Controls
  5. Formatting Forms and Controls (this blog)
  6. Grouping, Aligning, Sizing, Spacing and Arranging
  7. Controlling How Form Keys Work

This blog is part of our Excel VBA tutorial series.  We also run training classes in Excel and courses in VBA.

Posted by Andy Brown on 28 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Formatting Forms and Controls

You can format a form or any of its controls by selecting it/them (as shown in the previous part of this blog), then changing properties as shown below.

The Form Caption

To change a form's caption:

Changing a form caption property

To change the caption of a form:


Changing Background and Foreground Colours

You can select any control(s) or the form itself to change their colours:

The ForeColor and BackColor properties



When changing colour properties, be sure to use the Palette of colours and not System colours:

Palette tab of Colors tear-off

Choose the Palette tab to see what colours you are choosing!


You can set a control's background colour to transparent in a slightly different way:

BackStyle property

Change a control's BackStyle property to transparent to make it the same colour as the form it sits on.


Font or Typeface

To change the font of control(s) initially doesn't seem very promising:

The Font property

When you click in the Font property, a build button (with 3 dots on) appears. Click on this!


However, when you get to the Font dialog box, everything should be straightforward:

The Font dialog box

Phew! Hopefully this now looks familiar.

Horizontal Alignment (within Controls)

In the form below, the selected labels are right-aligned horizontally:

Right-aligned labels

The default alignment is left-aligned, so someone has changed these controls.


To change horizontal alignment, you'll probably expect there to be tools like this:

Horizontal alignment tools

There should be tools like this. Where are they?


Failing that, you'll probably look for the Alignment property, but in neither case will you have any joy.  It's the TextAlign property (of course!) that you want:

The TextAlign property

The TextAlign property has 3 less than obvious possible values.


Vertical Alignment within Controls

Vertically aligning text within a control is simple - you can't do it in user forms!

Vertically aligned control

If you want the title to appear in the centre of its box vertically, you'll have to press Enter before it.


Centre-Aligning Controls on Forms

In the screen shot below, the command button is centre-aligned horizontally on the form:

A centre-aligned command button

The Order button is horizontally centred on the form.


To achieve this, select the control in question and choose one of the following options:

Centre horizontal menu option

You can centre a control in the middle of a form going across or down.


Setting Borders for Controls

You can set the colour and thickness of a control's border:

Control with border

This control has a black border.


To do this, set the two properties shown below:

Border properties

It's no use setting a border colour if you don't also set the border style too.


Four Properties you Probably Shouldn't Change

There are some properties I reckon you shouldn't change!

Picture on form

The Picture property allows you to set a background picture on a form, but they always obscure the contents. You can tile, stretch and zoom pictures, but the best thing to do with them is delete them!

Form with vertical scroll bars

You can set the ScrollBars property of a form to Horizontal, Vertical or Both, but you'd be much better off designing your form properly (using MultiPage controls if necessary) so that you don't need them.

Start-up position property

A form has a start-up position property, but the most sensible position seems to be in the middle of the parent application (Excel), which is the default, as shown here.

A non-modal form

A form has a Modal property: if you set this to False, your users can ignore the form and click in Excel, which probably isn't a good idea.



Now that we've looked at formatting a form and individual controls on it, let's move on to formatting multiple controls relative to each other: that is, aligning, sizing, spacing and grouping controls.


This blog has 0 threads Add post