Hướng Dẫn Tạo Các Sheet Trong File Excel Theo 1 Sheet Mẫu Bằng Vba

Khi làm việc với những file Excel có nhiều Sheet, chắc hẳn bạn sẽ rất vất vả với việc ẩn / hiện các Sheet. Làm thế nào để ẩn được nhiều Sheet cùng lúc? Làm thế nào để bỏ ẩn (hiện) nhiều Sheet cùng lúc? Hay nói cách khác bạn có muốn 1 bảng điều khiển sẽ giúp bạn tự động ẩn hoặc hiện các Sheet trong file Excel theo ý muốn? Chỉ với 1 chút kiến thức về Excel và VBA, bạn hoàn toàn có thể tự làm được bảng điều khiển này. Hãy cùng Học Excel Online tìm hiểu cách tạo MENU ẩn hiện Sheet theo ý muốn trong Excel bằng VBA nhé.

Đang xem: Hướng dẫn tạo các sheet trong file excel theo 1 sheet mẫu bằng vba

Mục tiêu của bài viết này là bạn có thể tạo được 1 Sheet (MENU) mà tại đó có thể điều khiển ẩn, hiện các Sheet còn lại trong file theo ý muốn như hình dưới đây:

Các bước thực hiện như sau:

Bước 1: Chuẩn bị file Excel

Tại Sheet MENU, bạn tạo 3 cột gồm: Danh sách tên Sheet, Ẩn, Hiện (Có thể trình bày, định dạng 1 chút cho bảng đó đẹp mắt)

Trong file bạn có thể đặt tên các Sheet còn lại, tô màu Sheet… tùy ý.

Sử dụng chức năng Insert > Shapes > Vẽ ra 2 hình chữ nhật, tượng trưng cho 2 nút lệnh:

Ẩn/hiện SheetDanh sách tên sheet

Sau khi vẽ ra vị trí bất kỳ trong Sheet, bạn có thể trình bày, định dạng, viết nội dung vào bên trong hình chữ nhật để trang trí, làm đẹp cho nút lệnh đó theo ý muốn.

Bạn lưu file dưới dạng file là Excel Macro-Enable Workbook ngay nhé, để đảm bảo chúng ta có thể có 1 file cho phép lưu trữ Macro trong VBA.

Vậy là việc chuẩn bị xong rồi. Chúng ta bước vào các nội dung chính thôi.

Bước 2: Viết Macro lấy danh sách tên Sheet

Bắt đầu với Excel Macros và lập trình VBA

Trong Module1 vừa tạo, bạn viết 1 Sub như sau:

View the code on Gist.

(Để copy đoạn code trên, bạn bấm vào mục View raw trong bảng code, trong cửa sổ code bạn có thể copy lại dòng code để dán vào trong Module)

Câu lệnh trên có ý nghĩa: Liệt kê tất cả tên các Sheet có trong File vào bảng Danh sách tên Sheet trong MENU, bắt đầu từ ô A2

Dòng 1: Khởi tạo Sub với tên thủ tục là SheetListDòng 2: Gán biến ws đại diện cho đối tượng SheetDòng 3 và 4: Gán biến lr đại diện cho giá trị dòng bắt đầu, sẽ bắt đầu từ dòng 2Dòng 6: Với mỗi tên Sheet lấy được trong ws.Name, chúng ta sẽ gán tên đó vào vị trí cột A, tại dòng là giá trị của biến lrDòng 7: Biến lr bắt đầu từ giá trị là 2, với mỗi tên Sheet tìm được sẽ cộng tiếp 1 giá trị để điền vào dòng tiếp theo của cột ADòng 8: Thực hiện vòng lặp đến hết các Sheet trong file (Workbook)Dòng 9: Kết thúc Sub

Gán Macro này vào nút lệnh Danh sách tên bằng thủ tục Assign Macro:

Bây giờ chúng ta chỉ cần bấm chuột vào nút lệnh Danh sách tên, Macro sẽ tự động liệt kê toàn bộ tên Sheet vào cột A, bắt đầu từ ô A2 như sau:

Ở ví dụ này có 10 Sheet, trong đó Sheet1 đã được đổi tên thành MENU.

Xem thêm: Chuyên Đề Bất Phương Trình Lớp 10, Phân Dạng Và Bài Tập Chuyên Đề Bất Đẳng Thức

Bước 3: Viết công thức về Logic Ẩn/Hiện

Trong 2 cột B và C, chúng ta có logic là:

Nếu 1 Sheet đang Ẩn thì sẽ không hiện => Ngược lại, nếu 1 Sheet không ẩn thì phải Hiện.

Do đó chúng ta chỉ cần tác động vào cột Ẩn (cột B), còn lại cột C sẽ tự động dùng công thức để lấy kết quả.

Ta có công thức tại cột C là:

Ký tự x đại diện cho việc bạn sẽ đánh dấu ký hiệu cho việc Sheet nào được chọn để ẩn/ hiện.

Công thức =IF(A3=””, “”, IF(B3=”x”, “”, “x”))

Trong đó:

IF(A3=””, “”, nghĩa là nếu giá trị trong ô A3 là rỗng (không có tên Sheet) thì trả về giá trị rỗngIF(B3=”x”, “”, “x”) nghĩa là nếu giá trị trong ô B3 (được đánh dấu x cho việc Ẩn Sheet) thì giá trị trong cột Hiện sẽ là rỗng. Ngược lại nếu không đánh dấu x vào ô Ẩn thì sẽ trả về giá trị x tại ô Hiện

Riêng Sheet MENU chúng ta không đặt công thức mà đặt thẳng giá trị x vào để thể hiện sheet đó luôn Hiện

Bước 4: Viết code ẩn/hiện Sheet

Tiếp theo bạn tạo 1 Sub có tên AnHien_Sheet như sau:

Nội dung của đoạn code trên là (những dòng lệnh chính):

Dòng 2 và 3: Tạo ra 1 biến là lr giúp xác định dòng cuối có dữ liệu trong cột A của Sheet có tên là MENU (Giúp xác định giới hạn các Sheet cần thực hiện)Dòng 5: Tạo ra biến sodong để dùng cho vòng lặpDòng 7: Thực hiện vòng lặp xét các Sheet trong cột A, bắt đầu từ dòng 2 tới dòng cuối có dữ liệuDòng 9: Khi bắt đầu vòng lặp, thực hiện việc hiện Sheet trước (coi như Sheet đó luôn được hiện)Dòng 11: Sau đó mới xét đến điều kiện là Sheet đó có đang ẩn không bằng việc kiểm tra giá trị trong cột B có phải là ký tự x khôngDòng 12: Nếu đúng, thực hiện lệnh ẩn Sheet theo tên Sheet là tên trong cột A tại dòng kiểm traDòng 16: Sau khi hoàn thành hết vòng lặp, thực hiện kích hoạt Sheet MENU để không chọn tới Sheet nào khác (có thể xảy ra khi ẩn / hiện Sheet)

Các bạn có thể tìm hiểu thêm về cấu trúc IF, vòng lặp For trong VBA tại bài viết:

Hướng dẫn cách viết cấu trúc IF THEN ELSE trong VBA Excel

Hướng dẫn sử dụng vòng lặp For.. Next trong VBA

Sau khi viết xong Sub trên, chúng ta gán Sub vào trong nút lệnh Ẩn/hiện Sheet.

Kết luận

Như vậy chúng ta đã hoàn thành được MENU điều khiển việc ẩn, hiện Sheet rồi. Bạn chỉ việc thực hiện thao tác chọn Sheet muốn Ẩn, sau đó bấm nút Ẩn/hiện Sheet và xem kết quả nhé.

Xem thêm: Tải Giải Vở Bài Tập Địa Lý Lớp 4 Bài 10 Ôn Tập Địa Lý Lớp 4, Giải Vbt Địa Lí 4 Bài 10: Ôn Tập

Muốn hiện Sheet đang ẩn, bạn chỉ cần xóa ký tự x trong cột Ẩn với Sheet đó rồi bấm nút Ẩn/hiện Sheet một lần nữa.

Ngoài ra bạn có thể áp dụng LẬP TRÌNH SỰ KIỆN vào trong sheet MENU để mỗi khi đánh dấu x hoặc bỏ dấu x vào ô Ẩn của Sheet bất kỳ, ngay lập tức Sheet đó sẽ ẩn/hiện mà không cần phải bấm nút Ẩn/hiện Sheet. Các bạn tham khảo cách lập trình sự kiện cho Sheet tại bài viết sau đây và thực hành theo nhé: