Post
Ask Question

How to Perform Validation in User Form Using Visual Basic For Application

Step 1: Create Form

  1. Start the Excel application.
  2. On the Developer tab click Insert then click Command Button from ActiveX Controls group.
  3. Place the button on the spreadsheet.


Figure 1: Login Image

  • Press Alt+F11 to activate the Visual Basic Editor (VBE)
  • On the Insert tab click User Form to insert the user form.


Figure 2: User Id

Step 2: Add Controls

Add the controls listed in the table below. Clicking on control from the Toolbox then drag a control onto a User Form.

Control Name
Image1 Login Image
Image2 User Id
Image3 Correct Id
Image4 Home
TextBox User Id Text
ChackBox User Check
Label Submit Label
  • Add all the preceding specified controls on the user form
  • Add the controls such that the form looks as in Figure 3


Figure 3: User Validation

  • In the Project Explorer, right-click on UserForm1 and then click View Code.
  • Add the following code lines.
  1. Private Sub SubmitLabel_Click()  
  2. Me.Hide  
  3. Call Setting  
  4. UserForm2.Show  
  5. End Sub  
  6. Private Sub UserIDText_Change()  
  7. Dim i As Integer  
  8. If UserIDText.Value = "" Then  
  9. UserIDText.BorderColor = RGB(255, 102, 0)  
  10. End If  
  11. i = 1  
  12. Do Until IsEmpty(Cells(i, 1).Value)  
  13. If UserIDText.Value = Cells (i, 1).Value Then  
  14. With UserIDText  
  15. .Border Color = RGB (186, 214, 150)  
  16. .Back Color = RGB (216, 241, 211)  
  17. .Fore Color = RGB (81, 99, 51)  
  18. End With  
  19. Image3.Visible = True  
  20. End If  
  21. ii = i + 1  
  22. Loop  
  23. End Sub  
  24. Private Sub UserForm_Initialize()  
  25. Call Setting  
  26. End Sub  
  27. Sub Setting ()  
  28. UserIDText. Text = ""  
  29. UserCheck.Value = False  
  30. UserIDText.BackStyle = fmBackStyleTransparent  
  31. UserIDText.BorderStyle = fmBorderStyleSingle  
  32. UserIDText.BorderColor = &H8000000D  
  33. SubmitLabel.TextAlign = fmTextAlignCenter  
  34. SubmitLabel.BackColor = &H8000000D  
  35. Image3.Visible = False  
  36. End Sub  
Step 3: Test the User from
  • Press Alt+F11 to exit from VBA
  • Right-click CommandButton1 and click View code.


Figure 4: Design Mode

Right-click Sheet1 and click View code to write the code between Private Sub CommandButton1_Click () and End Sub.

Add the code shown below.

Private Sub CommandButton1_Click ()

UserForm1.Show

End Sub

  • Press Alt+F11 to exit from the Visual Basic Editor (VBE)
  • Press the Command button (Be sure Design made is deselected)


Figure 5: Command Button

Output

  • Press the command button1
  • Type the User ID given in the spreadsheet


Figure 6: User Form

Check the Check box then press the submit button.


Figure 7: Submit Button


Figure 8: TradingX

Conclusion

I hope this article is useful for VBA beginners and those new to VBA. Thanks for reading and please provide your valuable suggestions.

HAVE A NICE DAY


Similar Articles