How To Build A Modern-Looking VBA Userform

Post Banner 1@2x.png

The Need For A Modern UI

Let’s face it, VBA Userforms haven’t had much love from Microsoft over the past 10 or so years…..and it shows! Unfortunately, while Excel’s UI gets a fresh paint coat every 3-4 years, the userform controls still look like they were built back in the 90s.

Dell Computers Website in the 1990s (click to enlarge)

Why do we care? It’s simple, appearance can lead to credibility.

Let me put it into perspective for you. Would you buy a new Dell computer from a website that looked like this? —>>
My answer would be “hell no!”
, but this very website is what thousands of customers used back in the 90s to design and purchase their own PCs.

If you are anything like me, the first site of a webpage like the one shown in my example triggers an immediate search for the broswer’s back button. Let’s take a second to think about this: if we ourselves don’t like re-experiencing the lackluster design of the nineties, why on Earth would we want to give ourselves or our users that very same experience within Excel?

Since Microsoft doesn’t seem to care, I’m taking it into my own hands and am going to provide you with the exact formatting requirements to bring your userform’s appearance back to present day and match the current UI implementations of Excel online.

Note: The color of a userform’s Title Bar is determined by a setting within the operating system, not within Excel or Office

Note: The color of a userform’s Title Bar is determined by a setting within the operating system, not within Excel or Office

Formatting The Userform Object

It’s very important to keep in mind what your overarching formats will be when first building a Userform. Properties that are shared between all controls (such as Font color and BackColor), default to whatever the Userform Object is currently set to prior to inserting a new control.

We can leverage these inter-workings to our benefit and knock out a large amount of the formatting needs. If you are performing a makeover to an existing Userform, you’re unfortunately going to have to apply these formats to all the controls individually (but make sure to select multiple controls by holding down your Ctrl key and “bulk change” the formats to save time.

Here are the key property changes you should make at the Userform level and therefore be applied to every control on your Userform where applicable.

Font Size: 10
Font Type: Calibri
Font ForeColor: &H00464646& (Dark Gray)
BackColor: &H00FFFFFF& (White)
BorderColor: &H00A9A9A9& (Light Gray)

Formatting TextBox Controls

TextBox controls allow users to enter information into them and are controls I see quite frequently while interacting with userforms. To modernize these controls, you’ll want to tweak the below properties for them. Though these changes may be subtle, they do just enough to bring the default sunken textbox appearance to a more current and familiar look.

Create Textbox.png

Border Effect: Flat (I would recommend changing this on all controls where possible)
Border Style: fmBorderStyleSingle
BorderColor: &H00A9A9A9& (Light Gray)

Create Your Buttons In Excel

All the property changes so far have been rather painless, but here is where the extra effort will come into play. But don’t worry, this time spent will be well worth it!

Button Creation.png

I will typically make buttons by simply drawing a textbox object (Insert tab > Textbox) on the spreadsheet. From there, you can manipulate the following properties to that textbox to create both the white and green formats of the buttons. These two button styles will be used on the backend with your VBA code to toggle between a “hovered state” and an “inactive state”.


Generic Formats

Textbox Size: 0.25H x 0.80W
Font Color: RGB(70, 70, 70)
Font Name: Calbri
Font Size: 10

Hovered State

Fill Color: RGB(211, 240, 224)
Border Color: RGB(134, 191, 160)
Border Weight: 1pt

Inactive State

Fill Color: RGB(255, 255, 255)
Border Color: RGB(169, 169, 169)
Border Weight: 1pt


PRO TIP: If you do not know how to manually add a custom RGB color code, THIS ARTICLE will show you how.

Adding The Hovering Affect With VBA

I am an absolute sucker for hovering effects on buttons. I think they are so simple and elegant but give the user the most subtle of clues of where one might want to click. This affect has erased the need to change the cursor icon from pointer to the pointing hand (in my opinion) and I HIGHLY RECOMMEND you only implement one or the other within your userforms. Remember, you are going for a subtle effect and don’t want to overkill it with too many changes when you user is hovering over buttons.

I dedicated an entire article to explaining the concept of this effect so I won’t re-hash the contents of that post entirely, but if the following code doesn’t make sense, you should definitely consider reading my article entitled: Creating Userform Buttons That Highlight Mouse Hovering.

The general concept equates to you using Image Controls to toggle the two button images created in the prior section (green and white buttons). A green button will hide directly behind a white button.

You can use the OnMouse VBA event to determine when the user’s cursor is hovering over one of your white buttons and trigger the white button to be invisible (revealing the green button which will be the button the user actually clicks). Consequently, when the user moves their mouse away from a button, the OnMouse VBA event can trigger the white button to reappear and hide the green button. Hopefully, that makes some sense and if not, definitely check out my more in-depth article.

VBA Example For The Create Button

Going along with the simple userform we’ve been working with throughout this article, there will be three VBA subroutines you will need to add to your Userform in order to make both buttons have a proper hover effect.

This first snippet of VBA code handles hiding the Save button’s white button when the user hovers over it. Notice the code is also ensuring the Cancel button’s white button is visible. This guarantees that only one button can appear green at a time.

Sub SaveButtonInactive_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Make Save Button Green when hovered on

  CancelButtonInactive.Visible = True
  SaveButtonInactive.Visible = False

End Sub

This next snippet of VBA code addresses the functionality for the Cancel Button.

Sub CancelButtonInactive_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Make Cancel Button Green when hovered on

CancelButtonInactive.Visible = False
SaveButtonInactive.Visible = True

End Sub

This last snippet of VBA code handles the MouseMove event from the Userform’s perspective. This is essentially your reset code for when the mouse cursor leaves any of your buttons with the hover effect impletemented.

Sub Userform_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Reset Userform buttons to Inactive Status

CancelButtonInactive.Visible = True
SaveButtonInactive.Visible = True

End Sub

Your Userform Is Now Current!

It’s amazing what a few little steps can do to bring your userform’s experience past nearly 30 years of aged design techniques. I hope you were able to easily follow along and now have some techniques under your belt that you can start to implement as you build or refresh the look of your userforms.

If you are more of a visual learner, I have put together an example file with the userform and all the various components I described throughout this article. Like all of the available example files on this site, you can get instant (and free) access to them after you have signed up for my free Excel tips email newsletter. You can sign up by clicking the green button below and get sent a password to the Subscribers-Only area of my website.

 
Already Subscribed? Click   HERE   to log-in to the "Example Files" section

Already Subscribed? Click HERE to log-in to the "Example Files" section

 

Let Me Hear From You!

I’d love to hear your thoughts on Userform and anything you might do to bring your forms into the modern age. Let me know in the comments section below!

- Chris