Tạo form nhập liệu bằng vba trong excel

Chào anh chị em trên Diễn đàn,
Mình là nhân viên kho nên tự làm 1 form nhập xuất kho để tự theo dõi vật tư trong kho, ở đây có 1 số vấn đề trong nhập liệu nhiều dòng mà mình tự mò giải quyết như sau
1. Làm sao để quản lý số lượng dòng trên Form
- Mình tạo 1 textbox tên LineCount để lưu trữ số lượng dòng
2. Làm thế nào để xác định được hiện thêm mới dòng nào, xóa dòng nào
- Mình đặt tên các Control như: Column001_Row002, Column006_Row012
- Mình tạo 1 class module để lưu Control.name vào Textbox1, muốn biết số dòng đang chọn là bao nhiêu thì là Right(Textbox1,3)
- Khi thêm dòng thì thì + giá trị cho Textbox1, xóa dòng thì - giá trị Textbox 1
- Khi xóa 1 dòng thì mình dòng lệnh .remove(Textbox1.value) (.remove chỉ áp dụng cho các Control tạo ra bằng code)
3. Làm sao để đưa dữ liệu từ Form xuống Sheet, VD như trong Video mình đã đưa giá trị của 2400 Control (800 x 3) xuống Sheet
- Theo cách bình thường
Sheet1.range("A"&lastrow).value = control1.value
Sheet1.range("B"&lastrow).value = control2.value
Cách này máy tính chạy rất chậm
- Vì vậy mình đã tạo 1 mảng động rồi đưa tất cả Control.value vào đó (Kích thước mảng bằng (LineCount.value,4))
- Sau đó mình dùng code Insert Shift row thêm dòng vào bảng (Số dòng mới thêm bằng LineCount.value), như vậy mình không cần phải kẻ sẵn bảng nhiều, dữ liệu bao nhiêu thì code tự kẻ tiếp, như vậy file đỡ nặng
- Đưa mảng xuống Sheet
Mời mọi người tham khảo, mình cũng không học bài bản nên đụng cái nào mới mò thôi, hiểu biết có thể không đúng

Trong thực tế công việc văn phòng, các bạn nhân viên thường phải làm nhiều loại báo cáo đặc thù theo nhu cầu của doanh nghiệp. Ngoài việc dùng công thức Excel thuần túy, nhiều người đã tìm hiểu về VBA trong excel để thiết kế những báo cáo đơn giản, hiệu quả, giảm dung lượng file và tăng tốc độ xử lý công thức, dữ liệu trong file. Hôm nay mình sẽ hướng dẫn các bạn làm một báo cáo động có kết hợp VBA và một công cụ mới là A-Tools. Mục đích là tạo được báo cáo động, lọc theo một điều kiện "Mã hàng" nhưng sẽ "lọc theo một hoặc nhiều mã hàng" .

Giả sử ta có bài toán như sau: Có một bảng dữ liệu gồm nhiều cột, trong đó có cột "Mã hàng". Vậy yêu cầu bài toán là: Tôi muốn xem báo cáo theo một mã hàng hoặc nhiều mã hàng, khi tôi chọn một mã hàng thì lọc toàn bộ dữ liệu của mã hàng đấy, khi chọn 2 thì lọc toàn bộ dữ liệu của 2 mã hàng,... tương tự cho n mã hàng.

Để giải quyết bài toán trên chúng ta làm như sau:

Bước 1: Thiết kế mẫu file Excel gồm 3 sheet như sau:

Sheet Setup: để lưu các thiết lập chung, ở đây chỉ có 1 sheet báo cáo nên chỉ cần lưu bảng dữ liệu điệu kiện gồm tất cả mã và tên hàng. Tôi tên vùng dữ liệu là DMCT_DT chẳng hạn, các bạn có thể đặt tên theo cách hiểu hoặc cấu trúc dữ liệu của mình (note: tên tiếng việt viết liền không giống, ngăn cách nhau bởi dấu gạch dưới).

SheetReport: Là sheet để xem báo cáo, cấu trúc báo cáo thì mình thiết kế như hình dưới đây, gồm 1 Shap để chọn mã hàng, Shap này sẽ hiển thị Form chọn danh mục mã hàng theo ý. Các dòng tiêu đề mình thiết kế bằng tiếng việt, có dòng tổng cộng ở phía dưới, khi lập công thức chỉ việc lập từ dòng thứ 2. 

Bước 2: Tiến hành thiết kế Form trong VBA.

Các bạn vào môi trường lập trình VBA (ALT+F11), vào menu Insert ->chọn UserForm, trong Form các bạn thiết kế theo cấu trúc tùy ý, còn trong bài toán của mình thì mình thiết kế hình dưới đây, ở đây mình dùng gồm "CheckBook" (để chọn tất cả), "ListBook" (để lấy danh sách) và các "Button" (để thực hiện lệnh).

Sau khi thiết lập xong, các bạn double click vào Button "Chọn" rồi copy code dưới đây vào trong thủ tục bạn vừa tạo.

    If cbAll.Value = True Then

        For i = 0 To lbDanhMuc.ListCount - 1

                    s = s & "','" & lbDanhMuc.List(i, 0)

        For i = 0 To lbDanhMuc.ListCount - 1

            If lbDanhMuc.Selected(i) = True Then

                    s = s & "','" & lbDanhMuc.List(i, 0)

    ThisWorkbook.Sheets("Report").Range("C1").Value = s

Các bạn chú ý dòng : ThisWorkbook.Sheets("Report").Range("C1").Value = s, chữ "Report" chính là tên sheet báo cáo mình đã thiết lập.

Tương tự với nút "Đóng", các bạn chỉ cần gõ vào dòng lệnh "Unload Me" mục đích để thoát Form như hình trên.

Bước 3: Sau khi hoàn thành các bước trên, các bạn Insert một module (vào Insert -> Module) để Show UserForm như hình dướ đây:

Bước 4: Gán thủ tục Runform vào Shap đã thiết kế ở trong SheetReport như sau:

Các bạn chuột phải vào "Shap", chọn tới phần "Assign Macro".

Bước 5: Các bạn lập công thức bằng SQL Buider (tìm hiểu các video về báo cáo động trong Excel và A-Tools) hoặc copy công thức dưới đây vào trong ô đầu tiên của bảng kết quả (ô D4).

Để chạy được phần này, các bạn cài cho mình phần mềm A-Tools Free: Download

Các bạn có thể tự tìm hiểu để tự gõ hoặc dùng chức năng SQL Builder để tạo câu lệnh, các bạn xem video hướng dẫn về công thức động tại đây: Link video

WHERE DATA.MA_VLSPHH in " & "('" &C1 &")","HR=NO;INSERT=YES;")

Bước 6: Thay đổi điều kiện và nhận kết quả:

Các bạn click vào "Chọn mã hàng", sẽ hiển thị Form mà mình đã thiết kế, các bạn tick chọn vào những mã hàng cần lọc (1 hoặc nhiều mã, để chọn tất cả các bạn tick vào chọn tất cả ở bên trên, nó sẽ ngầm định là chọn tất cả dù các mã hàng không tích trên Form).

Sau khi chọn xong, các bạn click vào nút chọn để nhận kết quả:

Tiếp tục các bạn chọn nhiều mã khác cũng bằng cách click vào "Chọn mã hàng" để chọn những mã theo điều kiện cần hiển thị.

Sau đó các bạn click vào nút chọn để nhận kết quả:

Đây là một bài toán cơ bản có kết hợp VBA và công thức tạo báo cáo động bằng A-Tools, sau khi các bạn tìm hiểu kỹ về cách làm báo cáo động bằng A-Tools kết hợp kiến thức VBA cơ bản, các bạn có thể thiết kế các file Excel nhanh gọn, đơn giản, giống như các phần mềm chuyên nghiệp trên Excel.