How to Read Trading File and Append the Data to Excel Sheet Using Event Driven Programming

Step 1

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


Figure 1: Adding Command button

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


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.