Hello
bigme,
Thanks for sharing your feedback with us. I understand that you want a breakdown of the given code. The VBA Code used is a Click event for a CommandButton on a UserForm. Here is a block-by-block breakdown of the code.
Code:
Dim ufID, ufNm, ufAdrs, ufCt As String
Dim ufArr() As Variant
Here, I have declared 4 string variables ufID, ufNm, ufAdrs, and ufCt to take input ID No., Name, Address, and City respectively from the 4 TextBoxes in the UserFrom. I have also declared a variant type array ufArr that can hold a list of variables of any data type.
Code:
ufID = UserForm1.TextBox1.Value
ufNm = UserForm1.TextBox2.Value
ufAdrs = UserForm1.TextBox3.Value
ufCt = UserForm1.TextBox4.Value
This part of the code assigns the values of TextBox1, TextBox2, TextBox3, and TextBox4 to the variables ufID, ufNm, ufAdrs, and ufCt respectively.
Code:
If ufID = "" Or ufNm = "" Or ufAdrs = "" Or ufCt = "" Then
MsgBox "Please fill all the fields before submitting."
Exit Sub
End If
This part of the code checks if any of the four input fields is empty. If any of them is empty, it displays a message box with a warning and exits the sub (i.e. the code stops execution).
Code:
ufArr = Array(ufNm, ufAdrs, ufCt, ufID)
The values of the variables
ufNm,
ufAdrs,
ufCt, and
ufID are stored in the array
ufArr in the given order.
Code:
Set wb = ThisWorkbook
Set ws = wb.Sheets("Database")
Here, two objects wb and ws are set. The variable wb refers to the workbook containing this VBA code and ws refers to the sheet titled “Database” in the current workbook.
Code:
Dim lastRow, i, j, rowMatch As Integer
Dim matchID As Boolean
Here, 4 integer variables and 1 boolean variable is declared.
Code:
rowMatch = 2
matchID = False
lastRow = ws.Cells(Rows.Count, 4).End(xlUp).Row
The integer variable rowMatch is set to the first-row value 2 (excluding the row of column headers) and the boolean variable matchID is set to False. The last row in Column 4 of the ws (i.e. Database) worksheet is determined using the End(xlUp) method and assigned to the lastRow variable.
Code:
For i = 2 To lastRow + 1
If ws.Cells(i, 4).Value = ufID Then
matchID = True
rowMatch = i
Exit For
End If
Next i
This block iterates through Column 4 (starting from Row 2 to lastRow + 1) of the ws worksheet using a For loop. If a match for the ufID variable is found in any cell of Column 4, then the matchID variable is set to True, the rowMatch variable is assigned the row number where the match was found and For loop is terminated.
Code:
If lastRow = 1 And matchID = False Then
For j = 1 To 4
ws.Cells(2, j).Value = ufArr(j - 1)
Next j
End If
If the lastRow variable is equal to 1 and the matchID value is False, then the database doesn't contain any data (except the column headers) currently. Therefore, the data from the UserForm (stored in the ufArr array) is updated in cells of Row 2.
Code:
If lastRow <> 1 And matchID = False Then
i = lastRow + 11
For j = 1 To 4
ws.Cells(i, j).Value = ufArr(j - 1)
Next j
End If
If the lastRow variable is not equal to 1 and the matchID value is False, then the ID No. of the data from the UserForm is not in the database. Hence, the next available row after the last row (with a reserve of 12 rows down) is updated with user input from the data entry form.
Code:
If matchID = True Then
For i = rowMatch To rowMatch + 11
If ws.Cells(i, 1).Value = "" Then
For j = 1 To 4
ws.Cells(i, j).Value = ufArr(j - 1)
Next j
Exit For
End If
Next i
End If
If the matchID variable is True, then the ID No. of the data from the UserForm is present in the database. Hence, the code iterates through 12 rows from where the match was found for the user-given ID No. and updates the first empty row with the user input from the data entry form.
Code:
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox3.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.Hide
Finally, we clear the
TextBoxes in the
UserForm for the next data entry and hide the
UserForm.
We also used the following code for the
ActiveX Controls CommandButton.
Code:
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
This code represents a Click event. Whenever we click on the Command Button in the Data Entry Sheet, the Data Entry UserForm will appear.
Hopefully, these breakdowns of the VBA code segments will help you understand more about the process of entering data. Let us know your feedback.
If you wish to learn more about how to create a UserForm, insert elements from ToolBox, and change various properties of these elements, navigate through the following article.
Regards,
Seemanto Saha
ExcelDemy
