Trending September 2023 # How To Generate Login User Form In Vba? # Suggested October 2023 # Top 15 Popular

You are reading the article How To Generate Login User Form In Vba? updated in September 2023 on the website Chivangcangda.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Generate Login User Form In Vba?

Excel VBA Login Form

VBA Login is a unique way to protect excel workbook and worksheets. Although we have options of Protect Sheet and protect workbook which is available in Review menu option, which we have the fix patterns of locking the worksheet and workbook. With the help of the VBA login, we have created a customize login box by which we can create dialog box or login form in which we can authorize the login id and password to access the complete excel workbook and any specific worksheets. For better understanding, suppose we have an Excel file that has multiple sheets and each sheet has some confidential data to be protected. And each data is meant for everyone, so we can create a login form by giving every user their login id and password and user will only be able to open and access the data which is meant for him. This could be done only by VBA Login.

How to Generate Login User Form in VBA?

We will learn how to generate logic user form in Excel by using the VBA Code.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Login Excel Template here – VBA Login Excel Template

Example #1

In this example, we will learn how to create login box using User form. For this, follow the below steps:

Step 1: For this open VBA and from the Insert menu tab select UserForm.

Step 3: At the left hand side we have a Properties of the Label which we have created. We can change the name, font, size, and color of created label.

Step 4: As you can see in below screenshot, we have change the name user form as Label1, Font as Arial Narrow, Fore Color as Blue and Caption as Enter User Name. We can further customize it.

Step 5: Now we will create a text box here to enter the user name for login as shown below.

Step 6: Now we would insert a label for entering the password.

Step 7: Again we would change the name of Label, color, font, size, and name as required below.

Step 8: Insert a text box for entering the password against the Password label.

Step 10: Now edit the properties of created Command Button from the Properties window at left.

End Sub

Step 12: Open an IF – End IF loop and write the condition as if the Login flag is FALSE then activate the worksheet and then show login.

Code:

LoginFlag =

False Then

Worksheets(“Sheet1”).Activate LogIn.Show

End If

End Sub

Step 13: Now we will compile the code by pressing the F8 key and And run the code by pressing the Play button located below the menu bar, we would get the Login box.

Example #2

Although there are various ways to create a VBA Login. But we will be seeing another code where we will be using IF – End IF loop to list the login. For this, again follow the same process to create a UserForm which we have seen in example-1. Include 2 labels and 2 text boxes, each for entering User Name and Password.

Code:

UsernameBox.Value = “”

Then

PasswordBox.Value = “”

Then

MsgBox “Enter Login Credentials!”

Else

MsgBox “Enter Username”

End If

End Sub

Step 2: Now define the Else If condition again, if username value is USER1 and password is Blank then give me the message as “Enter Password” and if the password is “abcd” then hide the application or else give the message as “Re-Enter Login Credentials.”

Code:

UsernameBox.Value = “”

Then

PasswordBox.Value = “”

Then

MsgBox “Enter Login Credentials!”

Else

MsgBox “Enter Username”

End If

ElseIf

UsernameBox.Value = “USER1”

Then

PasswordBox.Value = “”

Then

MsgBox “Enter Password”

ElseIf

PasswordBox.Value = “abcd”

Then

Me.Hide: Application.Visible =

True

Else

MsgBox “Please Re-Enter Login Credentials.”

End If

End Sub

Code:

UsernameBox.Value = “”

Then

PasswordBox.Value = “”

Then

MsgBox “Enter Login Credentials!”

Else

MsgBox “Enter Username”

End If

ElseIf

UsernameBox.Value = “USER1”

Then

PasswordBox.Value = “”

Then

MsgBox “Enter Password”

ElseIf

PasswordBox.Value = “abcd”

Then

Me.Hide: Application.Visible =

True

Else

MsgBox “Please Re-Enter Login Credentials.”

End If

Else

If PasswordBox.Value = “” Then MsgBox “Enter Password”

Else

MsgBox “Please Enter Correct Credentials.”

End If

End Sub

Step 4: Open a new sub procedure for Command Button which we created for Login, and write the below line for quitting the application.

Code:

ThisWorkbook.Application.Quit

End Sub

Step 5: And at last, write the final sub procedure for Created UserForm name to close the application.

Code:

Private Sub

LoginArena_QueryClose(Cancel

As Integer

, CloseMode

As Integer

) ThisWorkbook.Application.Quit

End Sub

Step 6: Final would look like this below.

Pros & Cons of VBA Login

VBA Login allows different users to login in a different way in a single Excel worksheet.

We restrict the user to the data which is meant for him/ her.

With the help of VBA Login, we can customize the excel workbook and give the different login user id and password to different users to access the data.

Because of lengthy lines of code, the process of creating VBA Login is quite complicated.

Things to Remember

We can use Protect Worksheet and Protect Workbook which also consider the password for login and accessing the data. But the password would be the same for each login. Whereas VBA Login allows use to create multiple user id and password.

Always create a simple user form first, because when we customize the data the size and attributes of created shapes and boxes, we may end of complicating the coding part.

We can create VBA login to direct the user to his login area after login.

Once coding is done, save the Excel in macro-enabled excel format.