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!
Example File Available
If you would like to see the finished solution inside a live Excel spreadsheet, I have added an example file with the above userform built to my Example Files vault. You can gain access through the button below.
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 refering 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 it’s 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 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.
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
Founder of TheSpreadsheetGuru