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.

Default userform properties

When you insert a new userform into your VBA project, the form is set to default properties:

Default settings of a userform

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

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

Modern looking controls

Checkbox alternative

This is the standard look of a userform with a checkbox:

A standard userform with a checkbox

Boring!

Let's see if we can improve on that:

A redesigned userform with a redesigned checkbox

See? That is better. So how did I create this?

  1. From the Insert tab of Excel, choose Insert Icon:
    The Insert Icon button of Excel
  2. 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
  3. I selected both icons and clicked Insert, giving me this:
    The Icons inserted in Excel
  4. 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
  5. 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
  6. I changed both Shape Fill and Shape Outline to green
  7. I repeated steps 4 to 6 for the black (filled) switch
  8. I dragged a checkbox control on my form (used a slightly larger Font size of 14)
    A chekcbox control
  9. 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
  10. I copied this control (we need two image controls)
  11. 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
  12. 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
  13. 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.
  14. 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?


Comments

Showing last 8 comments of 16 in total (Show All Comments):

 


Comment by: Ryder (25-10-2022 23:50:00) deeplink to this comment

Using office 2019... no "switch" in icons, sadly...
perhaps I'm doing it wrong?


Comment by: Jan Karel Pieterse (26-10-2022 09:50:00) deeplink to this comment

Hi Ryder,

I'm afraid the insert Icon feature is unavailable in Excel 2019.


Comment by: reza (28-12-2022 12:05:00) deeplink to this comment

Office v2019 Version 2211 (Build 1583120208) Updated December 13 already has the "insert icon" feature.


Comment by: Rahib (18-3-2023 11:18:00) deeplink to this comment

Hi Jan

The idea is good, but to simplify it, what I did, I just wrote a code that when one picture is clicked, that picture becomes invisible and the other picture becomes visible, and vice versa. So, I dont have to deal with checkbox. and on my command button, the code is:

If Image1.Visibility = True
'do something
Else
'do other thing
End If


Comment by: Dan W (7-6-2023 22:12:00) deeplink to this comment

Great post, and quite possibly my favourite topic - better GUIs with VBA Userforms! I've been exploring how to use the standard control set in different ways (inspired by your excellent treeview control). I wrote a class to make a pseudo titlebar a few months back for someone on MrExcel. Here is a link to an animated GIF of an early version of it: https://gifyu.com/image/Sk4zA


Comment by: Jan Karel Pieterse (8-6-2023 09:33:00) deeplink to this comment

Hi Dan,

Nice!


Comment by: SURENDRA KUMAR (16-6-2023 09:21:00) deeplink to this comment

How do I create a menu bar within a user form in vba excel?


Comment by: Jan Karel Pieterse (16-6-2023 11:23:00) deeplink to this comment

Hi SURENDRA,

I think the easiest way is by adding a drop-down to the form.
I have two examples.

1. A simple one
https://jkp-ads.com/downloadscript.asp?filename=Userform_with_Menu.xlsm

2. A more complicated one (more features):
https://jkp-ads.com/downloadscript.asp?filename=UserformMenu.zip

Both might not be error-free!


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].