Creating modern-looking userforms in VBA
Content
Introduction
The Microsoft VBA editor was designed somewhere around 1996. This means
that the standard design of the userforms you create with that editor look
really old-school if you don't do anything. Here I show how we might create
a more modern-looking user-interface by changing some properties of the
form and by using some tricks.
The example file
Download
the file
which contains both the old-school userform and the modern form.
When you insert a new userform into your VBA project, the form is set
to default properties:
![Default settings of a userform](../images/modernforms01.jpg)
The default settings of a userform are used as a template
for any new control you add to it, so you can save yourself a lot of time
by making the right choices right away.
Here are the settings I use to make my forms look more modern, next
to the defaults. I've marked the ones I changed:
![Default settings of a userform](../images/modernforms02.jpg)
The properties I change are:
- Backcolor
I set that to Window Background (which is usually white) rather than
the stale grey we normally get
- Font
I change that to Calibri, just because I like that better than the default
Tahoma. Calibri turns out slightly smaller than Tahoma, which is why
I set the size to 9 rather than 8.
- ForeColor
I set that to the darkest grey there is on the pallette (just because
I find Black is too black):
![Setting BorderColor in a userform](../images/modernforms03.jpg)
Modern looking controls
Checkbox alternative
This is the standard look of a userform with a checkbox:
![A standard userform with a checkbox](../images/modernforms04.jpg)
Boring!
Let's see if we can improve on that:
![A redesigned userform with a redesigned checkbox](../images/modernforms05.jpg)
See? That is better. So how did I create this?
- From the Insert tab of Excel, choose Insert Icon:
![The Insert Icon button of Excel](../images/modernforms06.jpg)
- In the subsequent window, type "switch" in the search box, this
is what I got:
![The Insert Icon window of Excel after searching for Switch](../images/modernforms07.jpg)
- I selected both icons and clicked Insert, giving me this:
![The Icons inserted in Excel](../images/modernforms08.jpg)
- I don't like the black. Let's change that. Right-click either of
the icons and choose "Convert to shape"
![Converting an icon to a shape is easy](../images/modernforms09.jpg)
- This ensures the icon is converted to a grouped set of built-in
shapes, which are easy to adjust. I did not change the selection, instead,
I clicked the Shape Format contextual tab:
![The Shape Format contextual tab of Excel](../images/modernforms10.jpg)
- I changed both Shape Fill and Shape Outline to green
- I repeated steps 4 to 6 for the black (filled) switch
- I dragged a checkbox control on my form (used a slightly larger
Font size of 14)
![A chekcbox control](../images/modernforms11.jpg)
- Next I added an Image control, set its border to be hidden and the
backcolor and BorderColor to Window background, BorderStyle to 0 - BorderStyleNone:
![An Image control](../images/modernforms12.jpg)
- I copied this control (we need two image controls)
- One by one I then copied the switches, by right-clicking them on the
worksheet and choosing Copy, clicked inside the Picture property of
the Image control and
pressed control+v:
![An Image control with the icon inside](../images/modernforms13.jpg)
- I changed the name of both Image controls, respectively to SwitchOn
and SwitchOff and placed them on top of each other, right on top of
where the checkbox is:
![The Image controls on top of the checkboxwith the icon inside](../images/modernforms14.jpg)
- To make things easier (Thanks Andy Pope, for the suggestion in
the comments), set the Enabled property of both switches to False.
That way, you appear to click the image, but the click will "go" to
the checkbox control underneath, which is what we want.
- Finally, we need some VBA code to make it look like we're moving
the switch when we click on the images and when we click on the text
of the check box (I did not change the name of the check box, so that
is CheckBox1).
We do not need the Image control events, as everything is handled
from teh click on the checkbox. Here is the VBA code we need to make this work:
Option Explicit
' ******Events******
Private Sub CheckBox1_Click()
SetSwitchInCorrectPosition
End Sub
Private Sub UserForm_Initialize()
SetSwitchInCorrectPosition
End Sub
' ******Private routines******
Private Sub SetSwitchInCorrectPosition()
If CheckBox1.Value
Then
SwitchOn.Visible =
True
SwitchOff.Visible =
False
Else
SwitchOn.Visible =
False
SwitchOff.Visible =
True
End If
End Sub
Of course things become more complicated if you have multiple
checkboxes on the form that you want to work this way. I've implemented
that in the multiple checkboxes example (in the dowload) using two class
modules.
More to follow
I've only discussed how you might create a nice-looking alternative for
the check-box. I plan to add more controls in the near future. If you've
designed modern-looking alternatives yourself, why not add your examples
in the comments below?