Bạn đang muốn tạo mục lục nội dung bằng VBA trong Excel? Bài viết này sẽ hướng dẫn bạn cách thao tác tạo mục lục và tạo liên kết tương ứng với nội dung bằng Excel VBA giúp bạn tiết kiệm thời gian và tăng năng suất làm việc gấp bội. Hãy cùng UniTrain thực hiện theo các bước dưới đây nhé!
Giả sử người dùng có 4 sheets: Physics, Chemistry, Maths, và Biology trong một cuốn sách bài tập và mỗi cuốn sách có một bản ghi điểm cho các môn học tương ứng. Và bây giờ người dùng sẽ tạo một bảng tóm tắt bằng cách tạo danh sách 4 sheets này với các liên kết tương ứng để có thể click tới nội dung một cách nhanh chóng hơn.
Hãy cùng xem qua 2 ví dụ tạo mục lục nội dung bằng Excel VBA:
Ví dụ 1: Tự động tạo mục lục bằng VBA
Bước 1: Chọn Developer > Code > Visual Basic.
Khi cửa sổ Visual Basic for Applications hiển thị, hãy chọn Insert > Module.
Bằng cách này, bạn sẽ tạo một module mới là Module 1.
Bước 2: Nhập mã dưới đây:
Sub making_list_of_subjects()
Dim subject As Integer
On Error Resume Next
Application.DisplayAlerts = False
Sheets("makinglist").Delete
ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(1)
ActiveSheet.Name = "makinglist"
For subject = 2 To Sheets.Count
ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(subject + 2, 3), _
Address:="", SubAddress:="'" & Sheets(subject).Name & "'!B3", _
TextToDisplay:=Sheets(subject).Name, _
ScreenTip:="contains the record of marks for " & Sheets(subject).Name
With ActiveSheet
.Range("B1") = "Table of Contents"
.Range("B1").Font.Bold = True
.Range("B1").Font.Size = 18
.Range("B3").Value = "Subject"
.Range("B3").Font.Bold = True
.Range("B3").Font.Size = 14
.Range("C3").Value = "Link"
.Range("C3").Font.Bold = True
.Range("C3").Font.Size = 14
.Cells(subject + 2, 2).Value = Sheets(subject).Name
.Range("B4", Range("C" & Rows.Count).End(xlUp)).Font.Size = 12
End With
Next subject
End Sub
Bước 3: Nhấn F5 để chạy mã.
Với thao tác này, người dùng đã tạo một danh sách tạo sheet mới ở vị trí đầu tiên với mục lục của sổ làm việc này (như hình dưới).
Bước 4: Bạn cũng có thể thực hiện một số định dạng để làm cho danh sách này đẹp hơn bằng cách:
1. Tăng chiều rộng cột của Cột B và Cột C.
2. Bạn có thể hợp nhất các ô B1 và C1 để phù hợp với tiêu đề của Table of Contents (mục lục).
3. Chọn nội dung các ô > chọn Home > nhóm Alignment >> chọn Middle Align and Center
4. Sau khi thay đổi căn chỉnh, nếu muốn tạo đường viền cho nội dung, hãy chọn bảng và chuyển đến tab Home > Font > Borders > All Borders.
5. Để thay đổi màu của các tiêu đề cột, hãy chọn các tiêu đề rồi chuyển đến tab Home > Font > Fill Color > Orange, Accent 2, Lighter 80% (hoặc bất kỳ màu nào bạn muốn).
6. Nếu muốn ẩn các đường lưới, hãy bỏ chọn tùy chọn Gridlines trong View.
Cuối cùng, mục lục được định dạng sẽ hiển thị như hình dưới:
Ví dụ 2: Tạo mục lục trên bất kỳ Sheet nào trong Excel
Giả sử, người dùng tạo mục lục trong sheet đã được định dạng sẵn, và sheet này không có ở vị trí đầu tiên. Và bây giờ, người dùng sẽ tạo một danh sách các chủ đề và các liên kết tương ứng bằng mã VBA.
Bước 1: Chọn Developer > Code > Visual Basic.
Khi cửa sổ Visual Basic for Applications hiển thị, hãy chọn Insert > Module.
Bằng cách này, bạn sẽ tạo một module mới là Module 2.
Bước 2: Nhập mã dưới đây:
Option Explicit
Private Sub makinglistofsubjects1()
Dim list_sheet, subject_sheet As Worksheet
Dim subject As Integer
Application.Calculation = xlManual
Set list_sheet = Sheet7
subject = 4
list_sheet.Range("B4:C100").ClearContents
For Each subject_sheet In ActiveWorkbook.Worksheets
If subject_sheet.Name <> list_sheet.Name Then
list_sheet.Range("B" & subject).Value = subject_sheet.Name
list_sheet.Hyperlinks.Add Anchor:=list_sheet.Range("C" & subject), Address:="", _
SubAddress:="'" & subject_sheet.Name & "'!B3", TextToDisplay:=subject_sheet.Name
subject = subject + 1
End If
Next subject_sheet
Range("B4", Range("C" & Rows.Count).End(xlUp)).Font.Size = 12
End Sub
Bước 3: Nhấn F5 để chạy mã.
Sau đó, một sheet-makinglist1 mới ở vị trí thứ hai với mục lục của sổ làm việc này sẽ được tạo (như hình dưới).
Xem thêm
Khóa học Ứng dụng VBA trong Excel
[Tải tài liệu miễn phí] Template Update tỷ giá tự động bằng Power Query, Dashboard và VBA