BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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).
- An Introduction to UserForms in VBA
- Creating and Using UserForms in VBA
- The Properties Window
- Selecting Forms and Controls
- Formatting Forms and Controls (this blog)
- Grouping, Aligning, Sizing, Spacing and Arranging
- 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:

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:

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

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:

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:

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:

Phew! Hopefully this now looks familiar.
Horizontal Alignment (within Controls)
In the form below, the selected labels are right-aligned horizontally:

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:

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 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!

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:

The Order button is horizontally centred on the form.
To achieve this, select the control in question and choose one of the following options:

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:

This control has a black border.
To do this, set the two properties shown below:

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!

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!

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.

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 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.