Cách tạo biểu mẫu nhập dữ liệu tự động trong Excel VBA

Dùng biểu mẫu trong VBA cho bạn một canvas trống để thiết kế, sắp xếp biểu mẫu phù hợp với nhu cầu bất kỳ lúc nào. Hãy cùng Pgdphurieng.edu.vn học cách tạo biểu mẫu nhập liệu tự động trong Excel VBA nhé!

Tạo một biểu mẫu người dùng bằng Excel VBA

Mở một workbook Excel mới và thực hiện vài bước sơ bộ trước khi bắt đầu tạo biểu mẫu nhập dữ liệu.

Lưu workbook bằng tên mong muốn, đừng quên đổi kiểu file sang Excel Macro-Enabled Workbook.

Thêm 2 sheet vào workbook này với các tên sau:

1. Sheet1: Home
2. Sheet2: Student Database

Tất nhiên, bạn có thể thay đổi tên của chúng như ý muốn.

Trong sheet Home, thêm nút điều khiển macro biểu mẫu của người dùng. Tới tab Developer > click Button từ danh sách Insert thả xuống. Đặt nút bấm này vào vị trí bất kỳ trên bảng tính.

Sau khi đặt nút bấm, đổi lại tên cho nó. Click chuột phải vào nó > nhấn New để gắn một macro hiển thị biểu mẫu.

Nhập code sau vào cửa sổ trình chỉnh sửa:

Sub Button1_Click()
UserForm.Show
End Sub

Khi sheet Home và Student Database sẵn sàng, giờ là lúc thiết kế biểu mẫu người dùng. Điều hướng tới tab Developer > click Visual Basic để mở Editor. Ngoài ra, bạn có thể nhấn ALT+F11 để mở cửa sổ trình chỉnh sửa.

Click tab Insert và chọn UserForm. Một biểu mẫu cho người dùng đã sẵn sàng được sử dụng. Một toolbox đi kèm mở ra cùng biểu mẫu này, chứa tất cả công cụ cần thiết cho thiết kế bố cục.

Từ toolbox này, chọn Frame. Kéo nó sang biểu mẫu người dùng và chỉnh lại kích thước của nó.

Trong (name), bạn có thể thay đổi tên của khung này. Để hiện tên ở front-end, bạn có thể thay đổi tên trong cột Caption.

Tiếp theo, chọn Label từ toolbox và chèn 2 nhãn dán trong khung này. Đổi lại tên đầu tên là Application Number và thứ hai là Student ID.

Áp dụng quy trình đổi tên tương tự qua Caption trong Properties. Đảm bảo bạn chọn nhãn tương ứng trước khi đổi tên của nó.

Tiếp theo, chèn 2 hộp văn bản vào box nhãn dán. Chúng sẽ được dùng để chụp lại các input của người dùng. Đổi tên của 2 text box qua cột (Name) trong Properties như sau:

• Textbox1: txtApplicationNo
• Textbox2: txtStudentID

Thiết kế khung thông tin học sinh

Chèn một khung dọc và thêm 10 nhãn và 10 text box. Đổi lại tên nhãn như sau:

• Label3: Name
• Label4: Age
• Label5: Address
• Label6: Phone
• Label7: City
• Label8: Country
• Label9: Date of Birth
• Label10: Zip Code
• Label11: Nationality
• Label12: Gender

Chèn text box tương ứng cạnh những nhãn này, chèn từ hai box optionbutton từ toolbox biểu mẫu người dùng cạnh nhãn gender. Đổi lại tên của chúng sang Male và Female (cùng với Custom) tương ứng.

Thiết kế khung chi tiết khóa học

Thêm khung khác theo chiều dọc và chèn 6 nhãn dán, 6 text box tương ứng với từng nhãn. Đổi lại tên nhãn dán như sau:

• Label13: Course Name
• Label14: Course ID
• Label15: Enrollment Start Date
• Label16: Enrollment End Date
• Label17: Course duration
• Label18: Department

Thiết kế khung chi tiết thanh toán

Chèn một khung mới, thêm một nhãn mới và đổi lại tên cho nó “Do you wish to update the Payment details?” Chèn 2 optionbuttons; đổi lại tên chúng sang Yes và No.

Tương tự, thêm một khung mới chứa 2 nhãn bổ sung và 2 box combo. Đổi lại tên nhãn như sau:

• Label19: Payment Received
• Label20: Mode of Payment

Thiết kế bảng điều hướng

Ở khung cuối cùng, thêm 3 nút bấm từ toolbox, chứa code triển khai các biểu mẫu. Đổi lại tên các nút bấm như sau:

• Button1: Save Details
• Button2: Clear Form
• Button3: Exit

Viết code biểu mẫu tự động: Nút lưu thông tin

Click đúp vào nút Save Details. Ở mô đun tiếp theo, chèn code sau:

Private Sub CommandButton2_Click()

‘declare the variables used throughout the codes
Dim sht As Worksheet, sht1 As Worksheet, lastrow As Long

'Add validations to check if character values are being entered in numeric fields.
If VBA.IsNumeric(txtApplicationNo.Value) = False Then
MsgBox "Only numeric values are accepted in the Application Number", vbCritical
Exit Sub
End If

If VBA.IsNumeric(txtStudentID.Value) = False Then
MsgBox "Only numeric values are accepted in the Student ID", vbCritical
Exit Sub
End If

If VBA.IsNumeric(txtAge.Value) = False Then
MsgBox "Only numeric values are accepted in Age", vbCritical
Exit Sub
End If

If VBA.IsNumeric(txtPhone.Value) = False Then
MsgBox "Only numeric values are accepted in Phone Number", vbCritical
Exit Sub
End If

If VBA.IsNumeric(Me.txtCourseID.Value) = False Then
MsgBox "Only numeric values are accepted in Course ID", vbCritical
Exit Sub
End If

'link the text box fields with the underlying sheets to create a rolling database
Set sht = ThisWorkbook.Sheets("Student Database")

'calculate last populated row in both sheets

lastrow = sht.Range("a" & Rows.Count).End(xlUp).Row + 1

'paste the values of each textbox into their respective sheet cells

With sht
.Range("a" & lastrow).Value = txtApplicationNo.Value
.Range("b" & lastrow).Value = txtStudentID.Value
.Range("c" & lastrow).Value = txtName.Value
.Range("d" & lastrow).Value = txtAge.Value
.Range("e" & lastrow).Value = txtDOB.Value
.Range("g" & lastrow).Value = txtAddress.Value
.Range("h" & lastrow).Value = txtPhone.Value
.Range("i" & lastrow).Value = txtCity.Value
.Range("j" & lastrow).Value = txtCountry.Value
.Range("k" & lastrow).Value = txtZip.Value
.Range("l" & lastrow).Value = txtNationality.Value
.Range("m" & lastrow).Value = txtCourse.Value
.Range("n" & lastrow).Value = txtCourseID.Value
.Range("o" & lastrow).Value = txtenrollmentstart.Value
.Range("p" & lastrow).Value = txtenrollmentend.Value
.Range("q" & lastrow).Value = txtcourseduration.Value
.Range("r" & lastrow).Value = txtDept.Value

End With
sht.Activate

'determine gender as per user's input
If optMale.Value = True Then sht.Range("g" & lastrow).Value = "Male"
If optFemale.Value = True Then sht.Range("g" & lastrow).Value = "Female"
'Display a message box, in case the user selects the Yes radio button

If optYes.Value = True Then
MsgBox "Please select the payment details below"
Else:
Exit Sub

End If

End Sub

Giải thích code trong biểu mẫu tự động:

Các textbox bao gồm text và giá trị số, vì thế, bạn cần hạn chế dữ liệu nhập vào từ người dùng. Application Number, Student ID, Age, Phone, Course ID và Course Duration sẽ chỉ chứa số, phần còn lại chứa text.

Dùng lệnh IF, mã kích hoạt cửa sổ bật lên lỗi nếu người dùng nhập một ký tự hoặc giá trị văn bản vào bất kỳ trường số nào.

Vì đã có xác thực lỗi, bạn cần liên kết các hộp văn bản với các ô trong trang tính.

Những biến lastrow sẽ tính toán hàng được điền cuối cùng và lưu giá trị bên trong để sử dụng linh hoạt.

Cuối cùng, những giá trị đó được dán vào box text trong sheet Excel được liên kết.

Code nút xóa biểu mẫu và thoát

Trong nút clear, bạn cần viết code xóa các giá trị hiện tại từ biểu mẫu của người dùng như sau:

With Me
.txtApplicationNo.Value = ""
.txtStudentID.Value = ""
..txtName.Value = ""
.txtAge.Value = ""
.txtAddress.Value = ""
.txtPhone.Value = ""
.txtCity.Value = ""
.txtCountry.Value = ""
.txtDOB.Value = ""
.txtZip.Value = ""
.txtNationality.Value = ""
.txtCourse.Value = ""
.txtCourseID.Value = ""
.txtenrollmentstart.Value = ""
.txtenrollmentend.Value = ""
.txtcourseduration.Value = ""
.txtDept.Value = ""
.cmbPaymentMode.Value = ""
.cmbPayment.Value = ""
.optFemale.Value = False
.optMale.Value = False
.optYes.Value = False
.optNo.Value = False

End With

Trong nút exit, nhập code sau vào biểu mẫu của người dùng:

Private Sub CommandButton5_Click()
Unload Me
End Sub

Ở bước cuối cùng, bạn cần nhập một vài phần code cuối cùng để tạo các giá trị thả xuống cho box combo (trong khung thanh toán).

Private Sub UserForm_Activate()

With cmbPayment
.Clear
.AddItem ""
.AddItem "Yes"
.AddItem "No"
End With
With cmbPaymentMode

.Clear
.AddItem ""
.AddItem "Cash"
.AddItem "Card"
.AddItem "Check"
End With
End Sub

Trên đây là cách tạo biểu mẫu nhập dữ liệu tự động trong Excel VBA. Hi vọng bài viết hữu ích với các bạn.