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.

Private Sub SubmitLabel_Click()
Me.Hide
Call Setting
UserForm2.Show
End Sub
Private Sub UserIDText_Change()
Dim i As Integer
If UserIDText.Value = "" Then
UserIDText.BorderColor = RGB(255, 102, 0)
End If
i = 1
Do Until IsEmpty(Cells(i, 1).Value)
If UserIDText.Value = Cells (i, 1).Value Then
With UserIDText
.Border Color = RGB (186, 214, 150)
.Back Color = RGB (216, 241, 211)
.Fore Color = RGB (81, 99, 51)
End With
Image3.Visible = True
End If
i = i + 1
Loop
End Sub
Private Sub UserForm_Initialize()
Call Setting
End Sub
Sub Setting ()
UserIDText. Text = ""
UserCheck.Value = False
UserIDText.BackStyle = fmBackStyleTransparent
UserIDText.BorderStyle = fmBorderStyleSingle
UserIDText.BorderColor = &H8000000D
SubmitLabel.TextAlign = fmTextAlignCenter
SubmitLabel.BackColor = &H8000000D
Image3.Visible = False
End Sub

Step 5

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

Add the following code lines.

Public f As String
Public FSO As New FileSystemObject
Private Sub CommandButton1_Click()
Dim OpenMsg As String
Dim FileName As String
f = Application.GetOpenFilename (Title:="Select the Trading File")
If f = "False" Then Exit Sub
FileName = f
TextBox1.Value = FileName
End Sub
Private Sub CommandButton2_Click()
Dim WrdArray () As String
Dim txtstrm As TextStream
Dim line As String
Dim word As Variant
Dim i As Long
Dim Count As Long
Set txtstrm = FSO.OpenTextFile (f)
Count = 1
Do Until txtstrm.AtEndOfStream
line = txtstrm.ReadLine
i = 1
WrdArray() = Split(line, ",")
For Each word In WrdArray()
Sheet2.Cells (Count, i) = word
i = i + 1
Next word
Count = Count + 1
Loop
txtstrm.Close
MsgBox "Data Imported. " & Count & “Records Found."
End Sub
Private Sub Image6_Click ()
Me.Hide
UserForm1.Show
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.