VBA User Form creation- Password Masking

Question

I started out using an InputBox as a UI to get a password to run SQL off a DB. I found out that InputBoxes do not have the ability of masking input characters (ex. *******). I then found out that I need to use a user form in order to build a text box with a password masking field. I've never done this before.

I found this post (http://www.mrexcel.com/archive/VBA/19882a.html) which seems like it would get me most of the way and I could add a few things I know how to do. When I plopped it into a blank spreadsheet I got a whole list of errors coming from it, and since the post is ridiculously old, I figured maybe there were some updates to VBA that make this code obsolete. Would anyone be able to provide critique on it in order to get it to work? I'll list some of the errors I'm coming across trying to fix it, as well as the code.

Errors:
-Statement invalid Type block
-User-defined type not defined
-Method 'VBE' of object'_Application' failed
-Method 'VBProject' of object'_Workbook' failed
-Object required

Code:

Option Explicit
Public OK As Boolean
Public Const sMyPassWord As String = "test"

Function GetPassWord(Title As String)
'---------------------------------------------------------------------------    ------------
' Procedure : GetPassWord
' DateTime : 4/02/02 19:04
' Author : Ivan F Moala
' Purpose : Creates a Dynamic UF to Test for aPassword
' : so there is no need to create one.
'---------------------------------------------------------------------------    ------------
Dim TempForm
Dim NewTextBox As MSForms.TextBox
Dim NewCommandButton1 As MSForms.CommandButton
Dim NewCommandButton2 As MSForms.CommandButton
Dim x As Integer

' Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False

' Create a Temp UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)

' Add a TextBox
Set NewTextBox = TempForm.Designer.Controls.Add("forms.textbox.1")
With NewTextBox
 .PasswordChar = "*"
 .Width = 140
 .Height = 20
 .Left = 48
 .Top = 18
End With

' Add the OK button
Set NewCommandButton1 = TempForm.Designer.Controls.Add    ("forms.CommandButton.1")
With NewCommandButton1
 .Caption = "OK"
 .Height = 18
 .Width = 66
 .Left = 126
 .Top = 66
End With

' Add the Cancel button
Set NewCommandButton2 = TempForm.Designer.Controls.Add    ("forms.CommandButton.1")
With NewCommandButton2
 .Caption = "Cancel"
 .Height = 18
 .Width = 66
 .Left = 30
 .Top = 66
End With

' Add event-handler subs for the CommandButtons & Userform
With TempForm.CodeModule
 x = .CountOfLines
 .insertlines x + 0, "Sub CommandButton2_Click()"
 .insertlines x + 1, "OK = False: Unload Me"
 .insertlines x + 2, "End Sub"

.insertlines x + 3, "Sub CommandButton1_Click()"
 .insertlines x + 4, "If TextBox1 = sMyPassWord Then OK = True: Unload Me"
 .insertlines x + 5, "End Sub"

.insertlines x + 6, "Private Sub UserForm_Initialize()"
 .insertlines x + 7, "Application.EnableCancelKey = xlErrorHandler"
 .insertlines x + 8, "End Sub"
End With

' Adjust the form
With TempForm
 .Properties("Caption") = Title
 .Properties("Width") = 240
 .Properties("Height") = 120
 NewCommandButton1.Left = 46
 NewCommandButton2.Left = 126
End With

' Show the form
VBA.UserForms.Add(TempForm.Name).Show

' Delete the form
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

' Pass the Variable back to the calling procedure
GetPassWord = OK

End Function

Sub ThisIsHowToUseIt()
'>>> This is the Main line <<<<br>Dim OKToProceed As Variant
OKToProceed = GetPassWord("Password Entry")
If OKToProceed = False Then End
'>>>-----------------------<<<<p>'>>> Your routine goes here     <<<<p>MsgBox "My routine is running now"

End Sub

Answer 1

If you really just care about masking the password (as you type) in a TextBox on a UserForm then you can use the built in functionality.

There is actually the property to set the password masking character for any TextBox. While the characters are masked with the set character, the TextBox can be still referenced and checked for its value and UserForm1.TextBox1.Value will return the unmasked string (in VBA). Check out the screenshot below and let me know if this answers your question.

Answer 2

Looks like it is having an issue with x being = 0 in this section when it gets to the first insertlines command where it adds 0 to X and then tries to insertlines at line 0. If you increment all the the values that are getting added to x by 1 so that it starts at line 1 it plays through just fine.

' Add event-handler subs for the CommandButtons & Userform
With TempForm.CodeModule
 x = .CountOfLines
 .insertlines x + 1, "Sub CommandButton2_Click()"
 .insertlines x + 2, "OK = False: Unload Me"
 .insertlines x + 3, "End Sub"
 .insertlines x + 4, "Sub CommandButton1_Click()"
 .insertlines x + 5, "If TextBox1 = sMyPassWord Then OK = True: Unload Me"
 .insertlines x + 6, "End Sub"
 .insertlines x + 7, "Private Sub UserForm_Initialize()"
 .insertlines x + 8, "Application.EnableCancelKey = xlErrorHandler"
 .insertlines x + 9, "End Sub"
End With

Also, make sure in your Sub that you move the Dim statement to a new line so that it isn't part of the comment like you have it in your code example.

Sub ThisIsHowToUseIt()
'>>> This is the Main line <<<<br>
Dim OKToProceed As Variant
OKToProceed = GetPassWord("Password Entry")
If OKToProceed = False Then End
'>>>-----------------------<<<<p>'>>> Your routine goes here     <<<<p>MsgBox "My routine is running now"

End Sub

Answer 3

In your VBA project you can add a UserForm (Insert->UserForm). Drag a TextBox from the toolbox onto the form. You can then right-click on the new form and select 'View Code'

In the code edit window you can include this code:

Private Sub UserForm_Initialize()
    Me.TextBox1.PasswordChar = "*"
End Sub

When you run form you will see * for every character you type.