Excel VBA: InputBox with Password Mask (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to create InputBox with password mask in Excel VBA? Then, this is the right place for you.

To prevent shoulder spying, you should use a hidden format for password. In this article, I will demonstrate how to use masked password in a Textbox in Userform with some easy steps. So let’s get started.

Excel vba inputbox password mask


How to Launch a VBA Editor in Excel

To write any VBA code, you must launch the VBA Editor first. To do that, click on the Developer Tab from Excel Ribbon. In case you don’t have the Developer Tab in Excel Ribbon, to bring the developer tab follow this article about how to add Developer Tab in Excel ribbon. Afterward, select the Visual Basic option. Follow the picture below.

Visual Basic Option of Developer Tab

After clicking on Visual Basic, Excel will lead you to the VBA Editor Window. In this window, you can write your VBA code.

VBA editor window


Mask Password in Inputbox with Excel VBA: With Easy Steps

In order to create a InputBox with password mask in Excel VBA, you need to follow these steps. Here, we also provided a short video to make you understand this topic.


📌 Step 1 :  Insert UserForm

  • Firstly, right-click on the code window. Then  Insert UserForm.

Inserting UserForm to create inputbox with password mask in Excel VBA

  • You have inserted UserForm1. This will show a Toolbox Controls as well.

UserForm in VBA code Editor


📌 Step 2 : Add Label and Textbox from Controls

  • Next, insert a Label from Controls. Edit the caption of the label as “ Enter a Password”.

Adding Label in UserForm to create inputbox with password mask in Excel VBA

  • Align the Caption of the Label by setting the TextAlign Property to fmTextAlignCenter.

Inserting Caption to UserForm

  • Now, insert a Textbox to the UserForm from Controls.

Adding TextBox in UserForm to create inputbox with password mask in Excel VBA

Read More: Excel VBA: Custom Input Box (7 Examples)


📌 Step 3 : Edit UserForm TextBox Properties

  • In the TextBox1 properties you will find PasswordChar. Set PasswordChar as an asterisk symbol “*”. Now, if you enter anything into the Textbox it will be hidden with a symbol.

PasswordChar Propertiy of TextBox

Read More: How to Use Excel VBA InputBox with Example


Excel VBA: InputBox with Password Mask (2 Cases)

There may occur a situation that you want to run a code with a password because of protection. Here are 2 examples where we can run a code with  masked Password.


1. Check Employee Details with VBA Userform InputBox with Masked Password

Suppose, I have a dataset where Employee ID, Name, Department, and Salary is given. By using Vlookup I can see the details in a MsgBox. To do this task a VBA code is needed. Our goal is to run the code with a masked password. Follow the steps below to do this.

  • First, Insert a UserForm in VBA code editor. Then Insert a Label, a TextBox, and a CommandButton from Controls Toolbox.

Inserting Label, TextBox, and CommandButton to UserForm to create inputbox with password mask in Excel VBA

  • Write the following code in the CommandButton1_click
  • Next, Run this code to see the result by Pressing Alt+F5.

VBA code of Checking Employee Details using Vlookup

Private Sub CommandButton1_Click()
On Error GoTo Line1:
Dim ID As Long, Det As String
ID = InputBox("Enter the Employee ID :")
Det = "Employee ID : " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 1, False)
Det = Det & vbNewLine & "Employee Name : " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 2, False)
Det = Det & vbNewLine & " Department: " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 3, False)
Det = Det & vbNewLine & "Monthly Salary: " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 4, False)
MsgBox "Employee Information : " & vbNewLine & Det
Exit Sub
Line1:
If Err.Number = 1004 Then
MsgBox "Employee not Found"
ElseIf Err.Number = 13 Then
MsgBox "Invalid ID"
End If
Unload UserForm1
End Sub

🛠️ Code Breakdown

Private Sub CommandButton1_Click()
  • This is a Sub procedure named CommandButton1_Click that is triggered when a user clicks on a CommandButton on a user form.
On Error GoTo Line1
  • The first line of the code is an error-handling statement that redirects the program flow to a line labelled as Line1 in case an error occurs during the execution of the code.
Dim ID As Long, Det As String
  • The next line declares 2 variables: ID and Det. ID is of Long data type and Det is of String data type.
ID = InputBox("Enter the Employee ID :")
  • The third line of the code displays an input box that prompts the user to enter an Employee ID. The value entered by the user is assigned to the ID variable.
Det = "Employee ID : " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 1, False)
Det = Det & vbNewLine & "Employee Name : " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 2, False)
Det = Det & vbNewLine & " Department: " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 3, False)
Det = Det & vbNewLine & "Monthly Salary: " & Application.WorksheetFunction.VLookup(ID, Sheet1.Range("B4:E14"), 4, False)
  • The next few lines of the code use the Application.WorksheetFunction.VLookup function to retrieve employee information from a range of cells (B4:E14) in Sheet1 of the workbook. The function is used four times to retrieve four pieces of information (employee ID, name, department, and monthly salary) corresponding to the employee ID entered by the user. The retrieved information is concatenated into a single string variable Det using the ‘&’ operator and the ‘vbNewLine’ constant, which inserts a new line character in the string.
MsgBox "Employee Information : " & vbNewLine & Det
Exit Sub
Line1:
    If Err.Number = 1004 Then
         MsgBox "Employee not Found"
    ElseIf Err.Number = 13 Then
        MsgBox "Invalid ID"
    End If
Unload UserForm1
End Sub

Finally, the MsgBox function is used to display a message box with the employee information retrieved from the worksheet. If an error occurs during the execution of the code, the error-handling code will be triggered, which will display an appropriate error message to the user. The UserForm1 is then unloaded, which closes the form.

Vba Code for initializing the UserForm:

Private Sub UserForm_Initialize()
TextBox1.SetFocus
End Sub

🛠️ Code Breakdown

Private Sub
  • It is a VBA code that defines the beginning of a procedure that is specific to an object in the workbook or document.
UserForm_Initialize()
  • It  is the name of the procedure that is being defined. This procedure will automatically execute when you will initialize the UserForm.
SetFocus
  • It is a VBA code that sets the focus on the first text box on the UserForm. This means that when the UserForm is initialized, the cursor will be placed in the first text box.

Overall, this VBA code initializes a UserForm and sets the focus on the first text box to make it easier for the user to input data.

Output running a VBA code with a masked password using UserForm in Excel


2. Filter Students with VBA Macro and Restrict Code with Masked Password in UserForm InputBox

This is another example where I want to filter the details of the students who have failed. But you have to enter a password to see the failed list. Following are the steps.

  • To begin, Insert a UserForm. Add a Label, 3 TextBoxes, and a CommandButton.
  • Edit the captions of these accordingly.

UserForm in VBA code editor

  • Insert the code in the CommandButton1_Click
  • To see the output, Run the code by entering Alt+F5.

VBA code to get list of failed students with a password using InputBox of UserForm

Private Sub CommandButton1_Click()
Dim lastRow As Long
Dim i As Long
If TextBox3.Text = "mypassword" Then
With ThisWorkbook.ActiveSheet
lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 5 To lastRow
If .Cells(i, "D").Value < 60 Then
.Cells(i, "D").EntireRow.Hidden = False
Else
.Cells(i, "D").EntireRow.Hidden = True
End If
Next i
End With
Else
MsgBox "Incorrect Password"
End If
Unload UserForm3
End Sub

🛠️ Code Breakdown

This is VBA code that will run when a user clicks on a Command Button named “CommandButton1“.

Dim lastRow As Long
 Dim i As Long
  • This 2 lines declare 2 variables: “lastRow” and “i“. We will use “lastRow” to find the last row with data in column D of the active worksheet, and “i” to loop through the rows of the worksheet.
If TextBox3.Text = "mypassword" Then
    With ThisWorkbook.ActiveSheet
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
  • Check if the text entered in a TextBox named “TextBox3” is equal to the string “mypassword“. If the password is correct, the code will proceed to the next step. With the active worksheet, find the last row with data in column D using the “End” method and the “xlUp” constant. We set the variable “lastRow” as the row number of the last row with data.
For i = 5 To lastRow
            If .Cells(i, "D").Value < 60 Then
                    .Cells(i, "D").EntireRow.Hidden = False
            Else
                    .Cells(i, "D").EntireRow.Hidden = True
            End If
        Next i
    End With
  • Loop through the rows of the worksheet, starting from row 5 (assuming that the first four rows are headers). For each row, check if the value in column D is less than 60. If it is, unhide the entire row using the “Hidden” property. If not, hide the entire row.
Else
    MsgBox "Incorrect Password"
End If
Unload UserForm3
End Sub
  • Once the loop is complete, unload the UserForm3 to close it.

Overall, we desinged this code to filter a worksheet based on a password and a condition in column D. If the password is correct, it will show rows where the value in column D is less than 60, and hide rows where the value in column D is greater than or equal to 60. Otherwise, it will display a message box saying “Incorrect Password” and exit the subroutine.

Final output image


How to Create Inputbox Through Excel VBA UserForm That Takes Only Number

In some cases, the value of TextBox must be a number. For example, Phone Number is always numeric. You can write a VBA code to accept only numeric value in a Inputbox.

Write the following code in TextBox2_Exit event and run the code to see the result.

VBA code of creating InputBox with Only Number in UserForm

Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(TextBox2.Value) Then
MsgBox "Numbers Only"
Cancel = True
End If
End Sub

🛠️ Code Breakdown

Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  • This is the subroutine declaration portion. It indicates that the code is associated with the “Exit” event of the TextBox2 control. The Cancel argument is of type MSForms.ReturnBoolean, which allows the code to cancel the focus change if necessary.
If Not IsNumeric(TextBox2.Value) Then
  • This is an If statement that checks whether the value in TextBox2 is numeric. The IsNumeric() function returns True if the value is a number, and False otherwise.
MsgBox "Numbers Only"
  • This line displays a message box with the text “Numbers Only” if the value in TextBox2 is not numeric.
Cancel = True
  •  This line sets the Cancel argument to True, which cancels the focus change and prevents the user from moving to another control on the UserForm.

Final output image

Read More: Excel VBA: Create InputBox with Multiple Inputs


Frequently Asked Questions (FAQs)

1. How do I Use InputBox in Excel VBA?

Open the VBA editor in Excel by entering ALT+F11. Afterward, select the module or sheet where you want to add the InputBox Code. Write the code below:

Sub Example()
    Dim Input As Integer
    Input = InputBox("Give a number between 1 & 100:", "Number Input", 50, 1, 100)
    MsgBox "You have given " & userInput & "."
End Sub

 

In this code InputBox function prompts the user to enter a number between 1 and 100. We set the title of the input box is as “Number Input”.

2. Can I Use Password Mask in VBA InputBox?

Yes you can Use Password Mask in VBA InputBox. For this you have to Insert a UserForm in the VBA code editor. In the UserForm, insert a TextBox. From Properties option Set PasswordChar property to an asterisk sign. As a result, if you enter anything into the TextBox it will remain hidden by asterisk symbol.

3. How to hide passwords in VBA?

To hide passwords, firstly, insert a UserForm in the VBA editor. Then, insert a TextBox from Controls in the UserForm. Set the PasswordChar Property of the TextBox to an asterisk symbol. When you run this code, it will hide your entered characters in the TextBox.


Key Takeaways from Article

In conclusion, you got to know about:

  • How to Insert a Masked Password in VBA UserForm
  • 2 examples of Running a Code with Masked password
  • Creating a InputBox with Numeric Only Number

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In this article, I have shown how to use masked passwords in an Inputbox in a very simple way. If you follow the images and videos carefully, you can insert a masked password quickly. If you face any difficulty regarding this topic, please comment so that we can help.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahfuza Anika Era
Mahfuza Anika Era

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo