VBA Excel (Bài 21): Hướng dẫn cách tạo menu ẩn hiện Sheet theo ý muốn trong Excel 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é.

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

Bạn có thể lấy bất kỳ file Excel nào cũng được, trong đó bạn sẽ dành riêng ra 1 Sheet đặt tên là MENU. Hoặc bạn có thể tải về và sử dụng file mẫu theo bài viết này tại địa chỉ: http://bit.ly/2CvJh1y (Nếu bạn sử dụng file này thì có thể bỏ qua bước chuẩn bị)

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 Sheet
• Danh 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

Đầu tiên bạn tạo 1 Module trong cửa sổ VBA để có thể bắt đầu với việc viết Macro trong VBA.

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

(Để 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à SheetList
• Dòng 2: Gán biến ws đại diện cho đối tượng Sheet
• Dò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 2
• Dòng 5: Thực hiện vòng lặp For Each với đối tượng ws trên tất cả các Sheet trong Workbook chứa code
• Dò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 lr
• Dò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 A
• Dò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.

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ỗng
• IF(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ặp
• Dò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ệu
• Dò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ông
• Dò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 tra
• Dò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)

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é.

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.