Step 1


Figure 1: Adding Command button


Figure 2: Inserting User Forms

Step 2

Add Controls

Add the controls listed in the table below. Clicking on control from the Toolbox next you can drag a control on a User Form 1.

Control Name
Image1 Login Image
Image2 User ID
Image3 Correct ID
Image4 Home
Text Box UserIDText
Check Box User Check
Label Submit Label

Add all the preceding specified controls on the User Form 1.

Add the controls such that the form looks as in Figure 3.


Figure 3: User ID login Form

Step 3

Add the controls listed in the table below. Clicking on control from the Toolbox next you can drag a control onto User Form 2.

Control Name
Image 1 Image 1
Image 2 Image 2
Image 3 Image 3
Image 4 Image 4
Image 5 Image 5
Image 6 Image 6
Frame 1 Frame 1
Button 1 Upload
Button 2 Submit
Label 1 TradingX_Label

Add all the preceding specified controls on the User Form 1.

Add the controls such that the form looks as in Figure 4.


Figure 4: TradingX Form

Step 4

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. i = 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 5

In the Project Explorer, right-click on UserForm 2 and then click View Code.

Add the following code lines.
  1. Public f As String
  2. Public FSO As New FileSystemObject
  3. Private Sub CommandButton1_Click()
  4. Dim OpenMsg As String
  5. Dim FileName As String
  6. f = Application.GetOpenFilename (Title:="Select the Trading File")
  7. If f = "False" Then Exit Sub
  8. FileName = f
  9. TextBox1.Value = FileName
  10. End Sub
  11. Private Sub CommandButton2_Click()
  12. Dim WrdArray () As String
  13. Dim txtstrm As TextStream
  14. Dim line As String
  15. Dim word As Variant
  16. Dim i As Long
  17. Dim Count As Long
  18. Set txtstrm = FSO.OpenTextFile (f)
  19. Count = 1
  20. Do Until txtstrm.AtEndOfStream
  21. line = txtstrm.ReadLine
  22. i = 1
  23. WrdArray() = Split(line, ",")
  24. For Each word In WrdArray()
  25. Sheet2.Cells (Count, i) = word
  26. i = i + 1
  27. Next word
  28. Count = Count + 1
  29. Loop
  30. txtstrm.Close
  31. MsgBox "Data Imported. " & Count & “Records Found."
  32. End Sub
  33. Private Sub Image6_Click ()
  34. Me.Hide
  35. UserForm1.Show
  36. End Sub
Test the User from

Press Alt+F11 to exit from VBA.

Right-click CommandButton1 and click View code.


Figure 5: Assign macro for Command button

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

Add the code line 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 (Ensure Design made is deselected)


Figure 6: User ID list in spreadsheet

Output:

Press the command button1

Type the User ID given in the spreadsheet.


Figure 7: User Id login panel

Check the Check box then press the Submit button.


Figure 8: User ID validation panel

Press the upload button.


Figure 9: TadingX design panel

Choose the Trading Data.

Press the Open button on the Open dialog box.


Figure 10: Browse the Trading file


Figure 11: Select the trading file

Press the Submit button.

Open the workbook to check the sheet2.


Figure 12: TradingX final result

Conclusion

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