Tạo chỉ mục cho các Sheet trong Workbook

Nếu
bạn đã tốn quá nhiều thời gian trong một workbook (bảng tính) với rất
nhiều worksheet (trang tính), bạn sẽ cảm thông được sự khó khăn trong
việc tìm kiếm một trang tính nào đó. Khi đó, có lẽ ta nên lập một chỉ
mục các trang tính đang có để thuận tiện cho việc điều hướng trong bảng
tính.

Bằng cách sử dụng một chỉ mục các trang tính sẽ cho phép bạn nhanh chóng
và dễ dàng điều hướng trong bảng tính, chỉ bằng một cú nhấp chuột sẽ
đưa bạn đến chính xác nơi bạn muốn đến mà không lo bị nhầm lẫn. Bạn có
thể tạo một chỉ mục trong một vài cách: bằng tay, tự động tạo ra bởi mã
VBA, hoặc là sử dụng trình đơn tùy chọn theo ngữ cảnh (thậm chí có thể
dùng các hàm Macro4 – không trình bày ở đây).


Tạo chỉ mục thủ công

Cách này rất dễ làm,
bạn chỉ cần chèn mới một worksheet và đăt cho nó một cái tên, ví dụ như
tên là Index. Sau đó, bạn nhận vào tên của các worksheet có trong
workbook và tạo các siêu liên kết (hyperlink) đến các worksheet tương
ứng với tên mà bạn nhập.

Để tạo hyperlink bạn chọn tên sheet, sau đó vào Insert | chọn Hyperlink tại nhóm Links [E2003: Insert | Hyperlinks] hoặc nhấn phím tắt là Ctrl+K để mở hộp thoại Insert Hyperlink.


Hộp thoại Insert Hyperlink

Chọn Place in This Document và chọn tên Sheet muốn kết nối tới. Nhấn nút OK để hoàn tất.

Phương pháp này phù hợp
khi bảng tính không có quá nhiều worksheet và tên worksheet không có sự
thay đổi thường xuyên, nếu không sẽ gây ra nhiều khó khăn cho công tác
bảo trì trang chỉ mục.


Tạo chỉ mục tự động bằng cách sử dụng VBA

Một cách thay thế
khác là sử dụng VBA để tự động tạo ra các chỉ mục. Đoạn mã sau đây sẽ tự
động tạo ra một bảng chỉ mục liên kết đến tất cả các worksheet mà bạn
có trong workbook. Bảng chỉ mục này sẽ được tạo lại mỗi khi worksheet
lưu chỉ mục được chọn.

Đoạn mã này phải được đặt
trong private module của Sheet chứa chỉ. Chèn một worksheet mới vào
workbook và đặt tên cho nó là Index chẳng hạn. Nhấp chuột phải vào tên worksheet vừa tạo và chọn ViewCode từ trình đơn ngữ cảnh hoặc nhấn tổ hợp phím Alt+F11.

Nhập đoạn mã VBA sau vào cửa sổ Code:




PHP Code:


Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim lCount As Long
lCount = 1

With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
lCount = lCount + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
"Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(lCount, 1), Address:="", SubAddress:= _
"Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub







Nhấn tổ hợp phím Alt+Q để
thoát VBE và trở về cửa sổ bảng tính, sau đó lưu bảng tính lại. Để chạy
đoạn mã vừa tạo, bạn dùng chuột chọn sang worksheet khác trong workbook
và sau đó chọn lại worksheet Index để kích hoạt sự kiện
Worksheet_Activate.

Lưu ý rằng, đoạn mã sẽ các
đặt tên (Name) cho các ô A1 ở mỗi worksheet kèm theo số chỉ mục của
worksheet trong bảng tính (worksheet đầu tiên có chỉ mục là 1, kế đó là
2, 3…. n). Điều này bảo đảm rằng ô A1 trên mỗi trang tính có một tên
khác nhau. Nếu ô A1 trên worksheet của bạn đã được đặt tên, bạn nên cân
nhắc đến việc thay đổi ô A1 trong đoạn mã sang một địa chỉ khác phù hợp
hơn.


Thêm thuộc tính cho workbook

Lưu ý, nếu bạn có thiết
lập Hyperlink base (siêu liên kết cơ sở) trong workbook thì các
hyperlink được tạo ra từ đoạn mã trên sẽ không thể hoạt động được, do
chúng đã liên kết đến các Name trong workbook hiện hành. Khi thuộc tính
hyperlink base được thiết lập thì các siêu liên kết sẽ trỏ đến hyperlink
base kết hợp với các Name.

Thêm thuộc tính Hyperlink base: Vào Office | Prepare | Properties | Document Properties | chọn Advanced Properties [E2003: File | Properties | Summary] và nhập vào hyperlink base. Ví dụ như chúng ta nhập vào http://www.giaiphapexcel.com/
Siêu liên kết trước và sau khi thêm Hyperlink base


Thêm lệnh gọi chỉ mục vào trình đơn ngữ cảnh

Cách thứ ba sẽ hướng dẫn bạn thêm một lệnh vào trình đơn ngữ cảnh để
gọi danh sách các worksheet trong workbook và bạn có thể chọn lệnh này
bằng cách kích phải chuột vào vùng bảng tính bất kỳ. Đoạn mã sau sẽ gọi
thanh lệnh Tabs của workbook như hình sau:


Thêm lệnh “Sheet Index” vào trình đơn ngữ cảnh

Nhấn Alt+F11 để mở cửa sổ VBE, sau đó nhập đoạn mã sau vào cửa sổ Code của ThisWorkbook:




PHP Code:


Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
On Error Resume Next
Application.CommandBars("Cell").Controls("Sheet Index").Delete
On Error GoTo 0
Set cCont = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlButton, Temporary:=True)
With cCont
.Caption = "Sheet Index"
.OnAction = "IndexCode"
End With
End Sub







Tiếp theo, bạn vào Insert | Module và nhập vào đoạn mã sau:




PHP Code:


Sub IndexCode()
Application.CommandBars("workbook Tabs").ShowPopup
End Sub







Đoạn mã trên sẽ thực thi mỗi khi bạn chọn lệnh “Sheet Index” từ trình
đơn ngữ cảnh và bạn phải đặt nó trong Module thì Excel mới có thể thấy
được thủ tục này.

Nhấn Alt+Q để
đóng cửa sổ VBE trở về bảnh tính Excel. Kể từ lúc này, bạn nhấp phải
chuột tại bất kỳ ô nào trong bất kỳ worksheet nào thì trong trình đơn
ngữ cảnh sẽ có thêm lệnh “Sheet Index” giúp bạn điều hướng đến bất cứ
worksheet nào trong workbook một cách dễ dàng.


















Tập tin đính kèm

• 
  
  H7_Index.zip
  (15.4 KB, 4206 lần tải)