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 setup 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 in 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.
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!
Chris "Macro" Newman :)