How To Build A Modern-Looking VBA Userform
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.
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 sight 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.
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.
Border Effect: Flat (I would recommend changing this on all controls where possible)
Border Style: fmBorderStyleSingle
BorderColor: &H00A9A9A9& (Light Gray)
Height: With font size = 8, use 15.75 | with font size = 10, use 18
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!
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 Effect 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 effect has erased the need to change the cursor icon from the 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 your user is hovering over buttons.
I dedicated an entire article to explaining the concept of this effect so I won’t rehash 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 SubThis 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 SubThis 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 implemented.
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 SubYour 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.
Download The Excel Example File
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. Feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.