Cách Tạo Mục Lục Tự Động Cho Các Sheet Trong Excel 7/2022

Hiển thị thẻ Developer

Thông thường thì thẻ Developer sẽ không mặc định hiển thị ở trên cùng giống như các thẻ thông dụng khác như Home, Insert,… Vậy nên để hiển thị thẻ này, các bạn hãy ấn chuột phải vào thanh ribbon rồi chọn Customize the Ribbon.

Sau đó thì hộp thoại Excel Options sẽ hiện lên. Các bạn nhìn ngay qua bên phải sẽ thấy ô Developer chưa được chọn, các bạn ấn chọn ô đó rồi ấn OK. Lúc này thì các bạn sẽ thấy thẻ Developer ngay trên cùng.

Lấy tên toàn bộ các sheet

Sau khi đã hiển thị xong thẻ Developer, chúng ta sẽ bắt đầu đi vào từng bước để tiến hành tạo mục lục tự động cho các sheet trong Excel. Đầu tiên ở thẻ Developer các bạn hãy chọn Visual Basic ở góc ngoài cùng bên trái.

Lúc này thì hộp thoại Microsoft Visual Basic for Applications sẽ xuất hiện. Các bạn ấn chuột phải vào tên của sheet đầu tiên trong mục Microsoft Excel Objects, chọn Insert rồi chọn Module để tạo ra một module mới. Mục đích của thao tác này là tạo ra một file để viết code.

Tiếp theo, chúng ra sẽ viết đoạn code đầu tiên để lấy số thứ tự cũng như tên của toàn bộ các sheet hiện đang có. Các bạn lưu ý từ bước viết code các bạn nên kết hợp theo dõi video ở phía trên để dễ dàng thao tác hơn. Đầu tiên chúng ta sẽ khai báo các biến cần thiết: wb viết tắt cho WorkBook chính là file Excel mà các bạn đang sử dụng, ws viết tắt cho WorkSheet sẽ là bảng tính hiện tại mà các bạn đang làm việc, isheet là biến đếm và irow là biến chứa giá trị là chỉ số hàng của ô. Sau khi khai báo các biến cần thiết thì tiếp theo các bạn khởi tạo bằng 2 câu lệnh bên dưới.

Sau khi đã khai báo biến và khởi tạo, kế tiếp chúng ta sẽ tạo một vòng lặp để quét qua các sheet đang có. Vòng lặp này sẽ chạy từ 2 (giá trị được gán cho biến isheet) đến tổng số lượng sheet bạn đang có. Đầu tiên với số thứ tự sẽ bằng isheet trừ đi 1 còn tên sheet được lấy bằng câu lệnh wb.Sheets(isheet).Name. Cuối cùng thì các bạn tăng irow lên 1 để ở vòng lặp tiếp theo các giá trị sẽ được áp dụng cho hàng tiếp theo bên dưới. Sau khi xong hết thì các bạn hãy ấn nút mũi tên màu xanh ở phía trên để chạy đoạn code vừa ghi nhé. Lúc này thì tại sheet mục lục của các bạn sẽ có số thứ tự và tên của các sheet rồi.

VD: isheet = 2 irow = 6 For isheet = 2 To wb.Sheets.Count ws.Range("B" & irow).Value = isheet - 1 ws.Range("C" & irow).Value = wb.Sheets(isheet).Name Next

Tạo liên kết đến các sheet

Bước tiếp theo chúng ta sẽ cùng tạo liên kết để dẫn đến các sheet từ mục lục, các bạn nhập vào đoạn code như hình bên dưới. Dòng đầu tiên sẽ chọn worksheet hiện tại, tiếp theo là chọn ô để chèn liên kết tại đó và dòng cuối cùng là để tạo một liên kết đến sheet với nội dung mình nhập là “go to sheet”. Sau khi xong hết thì các bạn hãy ấn nút mũi tên màu xanh ở phía trên để chạy đoạn code vừa ghi nhé.

VD: ws.Select ws.Range("D" & irow).Select ws.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wb.Sheets(isheet).Name & "'!A1", TextToDisplay:="go to sheet"

Tạo nút quay về mục lục từ các sheet

Sau khi đã tạo được liên kết, các bạn nên tạo thêm một nút để quay về mục lục tại các sheet đó để tránh trường hợp các bạn có hàng trăm, hàng ngàn sheet. Lúc này thì các bạn phải mất rất nhiều thời gian để trờ về sheet mục lục đầu tiên. Với nút trở về các bạn tạo khối bằng cách ở thẻ Insert chọn Shapes rồi chọn một khối bất kỳ, vì nút mang ý nghĩa là trờ về nên mình sẽ chọn khối hình mũi tên.

Để nhập nội dung cho khối vừa tạo các bạn nhấp chuột phải vào khối đó, chọn Edit Text rồi nhập nội dung mình mong muốn. Ngoài ra các bạn cũng có thể chỉnh sửa kích cỡ, tô màu, căn hàng cho nội dung này nữa nhé.

Bước tiếp theo khá quan trọng, các bạn cần phải biết được tên của khối vừa tạo là gì bởi vì nó sẽ cần thiết cho phần code mà chúng ta sắp thực hiện. Để xem được tên của khối, các bạn ấn chọn vào khối rồi nhìn vào ô nằm ở góc trên bên trái sheet hiện tại.

Để tạo liên kết cho khối thì đầu tiên các bạn nhấp chuột phải vào khối rồi chọn Link.

Lúc này thì hộp thoại Insert Hyperlink sẽ hiện ra. Các bạn chọn Place in This Document rồi kéo lên đầu để chọn đúng sheet mục lục của mình. Sau khi xong các bạn ấn OK để tạo liên kết.

Xong những bước phía trên thì các bạn đã tạo xong một nút để trở về sheet mục lục, tuy nhiên lúc này ở các sheet khác vẫn chưa có. Vậy nên chúng ta phải sử dụng thêm 1 đoạn code để có thể tạo được những nút tương tự ở các sheet còn lại. Trong đoạn code ở hình bên dưới, dòng đầu tiên là để chọn nút mà ta đã tạo ở những bước trên (lúc này đã có liên kết trở về mục lục), dòng thứ 2 là thực hiện lệnh sao chép, dòng thứ 3 là chọn sheet hiện tại (theo giá trị của isheet trong từng vòng lặp), dòng thứ 4 là chọn ô và dòng cuối cùng là lệnh dán nút vào ô được chọn ở dòng 4. Sau khi xong hết thì các bạn hãy ấn nút mũi tên màu xanh ở phía trên để chạy đoạn code vừa ghi nhé. Chúc các bạn thực hành tốt!

VD: ws.Shapes.Range(Array("Arrow: Left 1")).Select Selection.Copy wb.Sheets(isheet).Select Range("J1").Select ActiveSheet.Paste

Video hướng dẫn