Password Form With CapsLock Warning and Show Password

This post covers a password form I’ve developed developed and am adding to my database-connected utilities.

As I’ve mentioned, I use Excel mostly as a development environment for database queries. So, along with tools like SQL Developer and SQL Server Management Studio, I run and test a lot of my SQL right in Excel. This lets me use all of its filtering, pivoting and other data features to explore and validate query results. I recently even developed a data dictionary in Excel which queries database metadata for helpful information about the tables, fields, foreign keys, etc.

We adhere to an old-school coding style in my workplace. We don’t alias our tables with meaningless one-letter names and we type our SQL in ALL CAPS. I like this, but it means that I often typo case-sensitive passwords because CapsLock is on. So I’m updating my password entry to include a CapsLock check. And while I’m at it I’m adding a “show text” button. And, as often happens when I make something I like, I’m sharing my password form with you.

The Form

In the GIF below, CapsLock was on when the form was called. Once the user clicks in the password textbox the CapsLock warning is displayed. The CapsLock check is called from the textbox’s Enter and KeyUp events, in case it’s pressed while typing the password. The warning is turned off in the Exit event.

You can also see the Show Password feature in action. It’s just a toggle button that switches the textbox’s PasswordChar from “*” to “”.

The code includes the GetKeyState API code I found on the web and modified. In the downloadable example below the password form is called from a main module and the username and password are passed back from the form, as in my flexible chooser form post.

Features I Didn’t Add

For a while I made it so that clicking the Show Textbox button put the focus back into the password textbox. This was cool, but harder to code with a predictable circular event between the textbox and the togglebutton. Also, it seemed like overkill for a password form that you won’t be spending much time with. The other thing I looked at was having the Show Textbox control engage only while it was pressed, which seems more secure from shoulder-hackers.

Do you have a password form or suggestions for this one?

Download

Here’s a sample workbook with the form.

6 thoughts on “Password Form With CapsLock Warning and Show Password”

Doug,
For me the option to build GUI via MS Excel is obsolete. Microsoft will not put in any time to improve it. That’s why I advocate to learn VB.NET and create add-ins based on the .NET technology. By using .NET we also avoid all kind of WinAPI.

Reply ↓

Doug Glancy on October 11, 2018 at 8:04 pm said:

Hi Dennis. I’ve written a couple of programs in VB.Net, one that automates Crystal Reports and one that automated ArcGIS using a backend SQL Server database. I love coding in .Net! Oddly, when I’ve tried, and it’s been a while, I’ve never found .Net Excel addins or automation to be more convenient than VBA.

However your comment has me thinking about some of my these SQL development tools. I think they might be especially suitable for .Net forms, given Excel’s use as a presentation layer. Thanks for the reminder! (It won’t be the first time something you’ve written has inspired me to write some .Net code.)

Reply ↓

Hi Doug. I downloaded your sample file and looked at the code and copied the modMain and frmPassword to my project.
I have an Excel worksheet with two interface sheets. the first sheet has an Admin button with five other buttons to run other macros, when the Admin button is clicked, it takes the Admin to another interface sheet with other macro buttons only for selected users, I’d like to use your Password userform to get to the administrative sheet. How do I integrate them with my project? Where in the code do I insert a password to let your code let the user go to the Admin sheet or not let them because of an incorrect password?
I am sort of new to VBA and I can follow the code.

Reply ↓

Doug Glancy on October 17, 2018 at 6:48 am said:

Hi Derick. Thanks for the question.

You will wrap the form in a function that returns the password. Then you’ll compare what’s returned to the password, right there in your code, or to a password stored somewhere else.

To understand what I mean by “wrapped in a function: please follow the “flexible chooser form” link in the post above and refer the the section titled “the Function code”.

Reply ↓
- DERICK PITCHER on October 17, 2018 at 9:16 am said:
  
  Hi Doug,
  Well I dug into your suggestion and understood, for the better part anyway, but your coding is a little outside of my comprehension. What I’m currently using is within my VBA level.
  I will deduce that you’re not a coach nor inclined to do so and that the code you’ve posted is for upper level coders. I’m not there yet but will be. I thank you for your generosity in sharing.
  
  My Form Code:
  “mom” (temporary password) is the password hard-coded within the Okay button.
  
  Option Explicit
  
  Private Sub CmdCancel_Click()
  Unload Me
  
  End Sub
  
  Private Sub cmdOk_Click()
  If TxtBxPwd.Text <> "mom" Then
  MsgBox "Password is Incorrect. Please check your password and/or check if Caps Lock is on then re-enter."
  TxtBxPwd.Text = ""
  TxtBxPwd.SetFocus
  Else
  Sheets("Admin").Activate
  Unload Me
  End If
  
  End Sub
  
  Private Sub ViewPwd_Change()
  If ViewPwd = False Then
  TxtBxPwd.PasswordChar = "*"
  Else
  TxtBxPwd.PasswordChar = ""
  End If
  
  End Sub
  
  Private Sub UserForm_Activate()
  Me.TxtBxPwd.PasswordChar = "*"
  End Sub
  
  My Module Code:
  
  Sub PassWd()
  FrmPass.Show vbModeless
  End Sub
  
  Reply ↓

Thank You ! I try tolve this for a lang time, it is very usefull for me

Reply ↓