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 spreadsheetOutput:
Press the command button1
Type the User ID given in the spreadsheet.
Figure 7:
User Id login panelCheck 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.