9 phút đọc

Cách tạo bảng điều khiển trong Excel

Khi làm việc với những fileExcel 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
Cách tạo bảng điều khiển trong Excel

Khi làm việc với những fileExcel 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 fileExcel 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ọcExcel Online tìm hiểucá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ịfileExcel

Bạn có thể lấy bất kỳ fileExcel 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/2CvJh2y(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ăngInsert > 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-EnableWorkbook ngay nhé,đểđảm bảo chúng tacó 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. Nếu bạn chưa biết cách tạo Module thì có thể xem thêm tại bài viết:

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:

(Để 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ếnwsđạ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ượngws trên tất cả các Sheet trongWorkbookchứa code
  • Dòng 6: Với mỗi tên Sheet lấyđược trongws.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ụcAssign 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ệchiệ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)

Các bạn có thể tìm hiểu thêm về cấu trúcIF, vòng lặp Fortrong 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é.

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

Tìm hiểu về ý nghĩa và cách dùng các sự kiện trong VBA Excel

· ·  ·        Bài viết liên quan

Cách tạo menu chuyển động đẹp trong Excel sử dụng VBA

Cách lọc lấy số trong ô lẫn cả số và chữ hoặc ngược lại

Cách xóa nội dung trùng lặp trong một ô của Excel

Tải về miễn phí File Excel quản lý kho bằng excel VBA

Xác định ngày bất kỳ là thứ mấy trong tuần

Hướng dẫn cách đếm ký tự, ký tự đặc biệt trong một ô hoặc một dải ô trong Excel


Khóa học liên quan

Khóa học Tự động hóa Excel với lập trình VBA cho người mới bắt đầu Chi tiết khóa học

Khóa học Lập trình VBA nâng cao trong Excel Chi tiết khóa học

Cách tạo menu chuyển động đẹp trong Excel sử dụng VBA

Cách lọc lấy số trong ô lẫn cả số và chữ hoặc ngược lại

Cách xóa nội dung trùng lặp trong một ô của Excel

Tải về miễn phí File Excel quản lý kho bằng excel VBA

Xác định ngày bất kỳ là thứ mấy trong tuần

Hướng dẫn cách đếm ký tự, ký tự đặc biệt trong một ô hoặc một dải ô trong Excel

Đăng ký học qua Email

Hộ kinh doanh Học Excel Online.
Số ĐK: 17A80048102

Khóa học Excel

Khóa học VBA

Khóa học SQL

Khóa học Google Apps Script

Khóa học Power BI

Linkedin

YouTube

Facebook

Instagram

EXCELVBAPOWER BISQLGoogle Apps ScriptGoogle SheetsPower QueryPythonPower Platform

©  Học Excel Online. All rights reserved.

Video liên quan