Masking (Hiding) A Password As It Is Entered In An Excel Text Box
Today, I came across a spreadsheet that allowed a user to connect to a remote database via VBA. The spreadsheet had a nifty little interface allowing you to enter your username and password for access to the database. The thing that caught my eye was the password had masked characters, so no one passing by your desk could see your password.
Since I had never seen this in a spreadsheet before, I naturally had to dig in and see how this was done. It turned out to be very easy to set up and in the following steps, you too will learn how this neat feature can be accomplished.
Adding The Text Box
First, you will need to add the text box where you or your users need to enter their password. For this, you will need to insert an ActiveX Control Text Box (a Form Control version will not work). To do this:
- Go to the Developer Tab
- Click Insert inside the Controls group
- Go down and click the Text Box icon underneath the ActiveX Controls heading
After you click the Text Box icon, you can simply draw your text box on your spreadsheet. Below, I did this process twice, so my users could enter in their Username and Password into Text Boxes.
Adding The Mask
Now for the fun stuff! To get your text box to mask what is being entered into it, you will first need to right-click on the text box while in Design Mode (Developer Tab > Design Mode). From the menu that appears, select Properties.
Next, you will get this form box popping up with an enormous list of settings that you can tweak. We are going to focus on a setting called PasswordChar (about two-thirds of the way down the list). in the PasswordChar field you can enter any character in and the text box will only display that character within your text box. Typically, everyone uses an asterisk (*), but you can use a question mark (?), exclamation mark (!), or whatever your hear desires
The Final Touches
After you are finished tweaking your text box settings, you will need to exit out of Design Mode. Simply click on the Design Mode button in your ribbon so it is no longer highlighted.
By adding a couple personalized tweaks, you can have your very own login screen within Excel!
Security Things To Keep In Mind
While this is a very cool feature, it is important to note even though your password is masked visually, it is still stored within the Excel spreadsheet. You need to be aware of this because someone using VBA could easily pull the password from the text box.
Also, if you simply remove your special character from the PasswordChar field, your password will be revealed within the text box.
To put it simply: this in NOT a secure way to store your login information or any confidential information for that matter.
My Recommendation For Masking Information
I recommend only using this feature for personal spreadsheets that are not shared with anyone else. This feature is really meant to prevent passers-by from catching a glimpse of personal information while displayed on your screen. Excel's password masking feature is not meant for preventing other spreadsheet users from discovering your password.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Online Excel & VBA Blog Resources
Your Excel Resource Guide I'm sure every Excel blog out there has some sort of directory page with recommendations of...
XLOOKUP Formula To Return All Matches
Can You Return All Matches With XLOOKUP? Currently, you cannot return multiple lookup results with Excel’s XLOOKUP function. You can...
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.