How To Vertically Center A Userform Textbox
There’s No Middle Alignment Option…
Let’s just get straight to the point….unfortunately for some (lame) reason, Microsoft did not give us the option to middle align text within our Userform textbox controls. This can lead to some sloppy-looking interfaces (see the userform on the left below). But fear not, with a little bit of creativity, we can get the look and feel our userforms deserve!
Step 1: The Overlay
The essence of the trick is going to rely on overlaying one textbox on top of another one. So first, make a copy of your textbox and resize the height to the height of your textbox font size. Please note, I will be referring to the “original” textbox and the “new” textbox throughout these steps.
Step 2: Alignment
Next, you will want to center and middle align your New Textbox with your original one. To ensure that your Original Textbox does not move, select it last (while holding the Ctrl key). You will know your selection is correct if your Original Textbox has white boxes around it instead of black (shown below).
With both textboxes selected, proceed to right-click your mouse and navigate to the Align options and select both Middle and Centers. This will ensure you have perfect alignment.
Step 3: Remove Sunken Effect
Next, we will do some manipulation to “hide” the New Textbox. Select your New Textbox and go into its Properties (keyboard shortcut F4 to make appear). Scroll down to the SpecialEffect property and change it to 0 – fmSpecialEffectFlat.
After doing this you may notice that the New Textbox is covering up the right/left edges of the Original Textbox’s border. To fix this, just bring in the sides of your New Textbox a bit until it is no longer covering any border.
Step 4: A Couple Finishing Touches
If you go to test your userform now, you might notice a couple of things:
- There is an indentation in your text
- It may still be possible to select the textbox in the background (aka Original Textbox)
Fix Indentation – Select your New Textbox and change the SelectionMargin property to False
Fix Selection Issue – Select your Original Textbox and change the Enabled property to False
You’ve Done It!
You have now completed the steps to stick it to Excel and show that you can really do anything you want inside this amazing program. If you have any questions, ideas, or need further clarification let me know if the comments section below.
Download The Excel Example File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Determine YTD Stock Growth With Excel’s STOCKHISTORY Function
Summarizing Stock Data Microsoft Excel now has some great built-in functionalities to track stocks whether it be current or historical...
How To Pull Cryptocurrency Prices In Excel
Cryptocurrencies Now In Excel! When Excel received the capability to retrieve stock data directly within the spreadsheet via data types,...
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.