Tìm sheet trong excel vba

Dear all,
--------
Vấn đề này tôi thấy đơn giản, có thể chỉ dẫn tại đây, đâu cần file minh hoạ cho thêm phức tạp như thế nhỉ:
Nếu bạn có quá nhiều sheet trong một Workbook thì, có nhiều cách để chọn nhanh một sheet như sau:
Cách 1: Phổ thông nhất Right Click vào bộ nút Navigative nằm bên trái dưới, chọn sheet muốn mở. Nếu không thấy có thể chọn
Cách 2: Cách khác là dùng tổ hợp phím Ctrl + G để "gâu tu" đến một tham chiếu do bạn đưa vào: !
Cách 3: Nâng cao hơn là viết một thủ tục VBA chọn sheet, ví dụ:

Public Sub Goto_Worksheet[rgeGotoRange as Range]
On Error Resume Next
rgeGotoRange.Activate
End Sub

Nhấn để mở rộng...

Cách 4: Chia nhỏ workbook quá nhiều sheet thành các workbook bao gồm chỉ một số sheet có nội dung và chức năng tương tự nhau. Cách này được khuyến khích nhất vì nó sẽ tối ưu công việc của bạn: dữ liệu không dư thừa, công việc chuyên môn hoá hơn, tốc độ xử lý và tính toán nhanh hơn.

 

Dear HanDung107,
-----------------
Em đã định không trả lời vì em thấy đây là một trong các kiến thức cơ bản nhất của MS Excel. Bạn diepthanh sẽ có thể nhận được câu trả lời thích đáng! Hơn nữa, với tinh thần giúp cho thành viên giải quyết nhanh nhất công việc của mình thông qua WKT, em đã trả lời theo hướng trên. Nếu chỉ với thắc mắc nhỏ của diepthanh mà phải hướng dẫn cả một thủ tục addin như vậy, em e rằng vấn đề sẽ trở lên phức tạp cho yêu cầu công việc.
Còn về các tiện ích và công cụ, em hoàn toàn không có ý kiến phản đối mà ngược lại còn cổ vũ nhiệt tình. Việc xây dựng một addin tập hợp toàn bộ các tiện ích đã được đề cập, theo em, là cũng hết sức cần thiết. Và làm sao hướng các thành viên tìm đến công cụ này trước khi đưa ra những thắc mắc như kiểu của diepthanh sẽ giảm bớt các chủ đề không mấy cần thiết phải thảo luận.
Vài ý kiến mong tránh sự hiểu lầm và mong các anh chị ủng hộ!

 

Có khá nhiều thủ thuật để ứng dụng cho việc di chuyển đến một địa chỉ trong Excel. Di chuyển đến 1 sheet có thể xem là một địa chỉ, cách thông thường là bạn nhấp chuột phải vào thanh Navigation Bar hoặc tạo 1 sheet liệt kê cách tên sheet và dẫn Link tới sheet đó.
Dùng VBA thì cực kỳ dễ và gọn, đoạn Code sau đây xem như 1 Tip để di chuyển tới 1 sheet có tên chứa đoạn Text nhập vào:

Sub GotoSheet[]
Dim sSheet As Worksheet, FindText As String
Dim FshCount As Byte
FindText = InputBox["Nhap vao ten sheet can tim:"]
If Trim[FindText] = Space[0] Then
MsgBox ["Ban da khong nhap vao ten sheet nao!"]
Exit Sub
End If
FshCount = 0
For Each sSheet In ActiveWorkbook.Sheets
If InStr[1, sSheet.Name, FindText] > 0 Then
sSheet.Activate
If MsgBox["Ban muon tiep tuc tim khong?", vbYesNo] = vbNo Then Exit For
FshCoun = FshCoun + 1
End If
Next
If FshCoun = 0 Then MsgBox ["Khong tim thay sheet nao co chua ten " & FindText & "!"]
End Sub

Nhấn để mở rộng...

Để thuận tiện hơn bạn có thể gán phím tắt cho Macro trên.

 

adam_tran nói:

Có khá nhiều thủ thuật để ứng dụng cho việc di chuyển đến một địa chỉ trong Excel. Di chuyển đến 1 sheet có thể xem là một địa chỉ, cách thông thường là bạn nhấp chuột phải vào thanh Navigation Bar hoặc tạo 1 sheet liệt kê cách tên sheet và dẫn Link tới sheet đó.
Dùng VBA thì cực kỳ dễ và gọn, đoạn Code sau đây xem như 1 Tip để di chuyển tới 1 sheet có tên chứa đoạn Text nhập vào:


Để thuận tiện hơn bạn có thể gán phím tắt cho Macro trên.

Nhấn để mở rộng...

diepthanh nói:

Chào các anh chị
Năm mới xin gửi lời chúc sức khoẻ và hạnh phúc đến diễn đàn.
Các anh chị cho em hỏi :
Em mở khoảng 60 sheet trong 1 workbook và đặt tên cho từng sheet.
Thế nhưng bây giờ thì tìm tên sheet bằng cách bấm mũi tên ở thanh dưới cùng của bảng tính lâu quá.
Mong các anh chị chỉ giùm cách đi tới sheet cần tìm 1 cách mau chóng.
Cảm ơn nhiều lắm.

Nhấn để mở rộng...

 

VBA hỗ trợ nhiều sự kiện cho Worksheet như có thể bắt được thao tác khi sửa/ xóa dữ liệu, khi Click chuột, chuyển Sheet, xóa Sheet… Và dựa vào các sự kiện này khi thực thi, bạn có thể lồng vào các đoạn lệnh VBA để tùy biến thêm các tác vụ cho riêng mình.

Các sự kiện khi thao tác với WorkSheets trong VBA

Trong VBA, bạn có thể xem và tạo nhanh các sự kiện bằng cách chọn Worksheet và chọn mở trong danh sách các sự kiện.

• Code Name là phần bên trái, không nằm trong ngoặc. Code Name chỉ thay đổi được khi đổi tên mục [Name] ở cửa sổ Properties trong VBA

• Name là phần trong dấu ngoặc đơn. Đây chính là tên sheet được thể hiện trong thanh SheetTab trong Excel. Khi đổi tên Sheet trong SheetTab thì phần tên này sẽ thay đổi theo.

Thứ tự của Sheet trong thư mục Microsoft Excel Objects của VBA được sắp xếp theo thứ tự của Code Name, không phải theo Name hay theo vị trí của Sheet trên thanh Sheet Tab

Cách viết đối tượng Worksheet trong VBA

Đối tượng Worksheets hay Sheets

Chú ý trong đối tượng này, chữ Sheets có kèm theo chữ “S“. Đây là sự khác biệt rất lớn, bởi việc viết có S hay không có S sẽ ảnh hưởng tới cấu trúc của câu lệnh code trong VBA.

Khi sử dụng đối tượng này, chúng ta phải nhập theo tên [Name] chứ không phải nhập theo CodeName.

Ví dụ:

Sheets[“01”].Select => Viết đúng

Sheets[“ABC_01”].Select => Viết sai => Báo lỗi tô màu vàng tại dòng lệnh này.

Cách viết đúng [1 trong 2 cách]:

Cách 1: Sheets[“01”].Select

Cách 2: ABC_01.Select   [vì Sheet1 đã đổi thành ABC_01 nên ký tự ABC_01 chính là CodeName của Sheet]

Ngoài ra có thêm 1 cách viết khác là dùng số thứ tự của Sheet đó:

• Thứ tự của Sheet chính là thứ tự của Sheet trên thẻ Sheet Tab của Excel theo chiều từ trái sang phải.
• Thứ tự này không phụ thuộc vào tên Sheet, Code Name của Sheet hay thời gian khởi tạo Sheet.
• Cách này thường áp dụng khi bạn biết rõ vị trí của Sheet cần làm việc trên thanh SheetTab mà không quan tâm tới tên Sheet.

Đối tượng Sheet

Khi viết đối tượng Sheet [không có chữ “S“], chúng ta chỉ được phép sử dụng CodeName mà không được sử dụng Name.

Chúng ta chỉ cần gọi ra đúng tên Code name của Sheet là có thể sử dụng được.

Ngoài ra chúng ta có thể sử dụng thêm 1 đối tượng để gọi riêng Sheet đang hoạt động [đang được chọn, đang làm việc] là ActiveSheet [không có chữ S]

Như vậy chúng ta cần hết sức chú ý: Khi dùng cách gọi nào phải viết đúng theo cách viết của đối tượng đó. Nếu viết sai thì VBA sẽ báo lỗi không xác định được đối tượng [object]

Các phương thức của đối tượng Worksheet

Vì VBA là ngôn ngữ lập trình theo hướng đối tượng, do đó trong đối tượng Worksheet chúng ta có các phương thức sau:

Activate: Để kích hoạt một sheet

Ví dụ:

Sheets[“ABC”].Active    ‘Kích hoạt Sheet có tên là ABC

Sheet1.Active                   ‘Kích hoạt Sheet có CodeName là Sheet1

Kích hoạt tức là chọn tới Sheet đó, đồng thời con trỏ chuột sẽ đặt tại 1 ô trong Sheet được kích hoạt.

Calculate: thực hiện việc tính toán tất cả các công thức có trong Sheet

Ví dụ:

Sheets[“ABC”].Calculate       ‘Thực hiện tính toán các công thức trong Sheet có tên là ABC

Sheet1.Calculate                      ‘Thực hiện tính toán các công thức trong Sheet có CodeName là Sheet1

Copy: Sao chép 1 sheet

Việc copy 1 sheet có thể sao chép sheet đó thành 1 sheet mới nằm trong cùng 1 Workbook [tạo ra phiên bản copy] hoặc Copy ra 1 Workbook khác. Kèm theo đó là vị trí đặt Worksheet mới được copy đó. Nếu không quy định cụ thể ở vị trí nào thì sẽ mặc định là vị trí cuối cùng.

Việc gán vị trí sẽ quy định bởi tham số Before [ở trước] hoặc After [ở sau]

Sheets[“Tên sheet”].Copy [[Before], [After]]

Sheets[Số thứ tự sheet].Copy [[Before], [After]]

Ví dụ:

Worksheets[“Sheet2”].Copy Before:=Worksheets[1]       ‘Sao chép sheet có tên là Sheet2 tới vị trí trước Sheet đầu tiên trong danh sách các Sheet

Delete: Xóa 1 sheet

Ví dụ:

Sheets[“MENU”].Delete   ‘Xóa Sheet có tên là MENU

Khi xóa 1 Sheet, chúng ta có thể thấy Excel xuất hiện 1 thông báo yêu cầu xác nhận lại việc xóa này có được làm hay không. Nếu xác nhận lại là có thì Excel mới thực hiện việc Xóa Sheet.

Để bỏ thông báo đó mà luôn luôn cho phép xóa Sheet, chúng ta có thể kết hợp với câu lệnh không hiển thị thông báo của Excel như sau:

Sub XoaSheet[]

Application.DisplayAlerts = False
Sheet2.Delete
Application.DisplayAlerts = True

End Sub

Câu lệnh trên sẽ xóa Sheet có CodeName là Sheet2 mà không có bất kỳ thông báo nào.

Move: Di chuyển 1 sheet

Việc di chuyển Sheet cũng giống như khi chúng ta Copy, đó là phải chỉ định vị trí Sheet đó sẽ đi tới đâu, trước Sheet nào, hay sau Sheet nào, tại Workbook nào

Sheets[“Tên sheet”].Move [[Before], [After]]

Select: Lựa chọn 1 sheet

Thao tác Select giống như Active, đó là lựa chọn sheet để kích hoạt Sheet.

Sự khác nhau cơ bản giữa Select với Active đó chính là: Chúng ta có thể lựa chọn [Select] cùng lúc nhiều Sheet, nhưng chỉ có duy nhất 1 Sheet được kích hoạt [Active]

Cách tham chiếu tên sheet trong VBA

Tham chiếu tên Sheet [Sheets Name – thường gặp qua Record Macro]

Khi sử dụng chức năng Record Macro để tự học VBA qua các code tự động tạo qua quá trình ghi lại thao tác người dùng, chúng ta có thể thấy cách tham chiếu tên Sheet qua thao tác này như sau:

Chúng ta thấy ở đây Excel đã chọn đúng tên Sheet theo SheetTab trong Excel:

Sheet1 có tên là Vidu1 => Khi chọn Sheet này thì VBA sẽ ghi nhận là Sheets[‘Tên sheet”].Select

* Ưu điểm của cách này là chúng ta có thể dễ dàng xác định vị trí các Sheet thông qua việc căn cứ trực tiếp vào tên Sheet đang có tại Sheet Tab.

* Nhược điểm là nếu có sự thay đổi tên sheet tại Sheet Tab thì không tự thay đổi tên tham chiếu trong VBA => Gây lỗi => Phải sửa lại trong VBA

Tham chiếu Tên Code của Sheets [Sheets Code Name]

Kỹ thuật này thường gặp bởi người đã hiểu rõ về lập trình trong VBA và đã nắm được các ưu nhược điểm của tham chiếu Tên Sheet, từ đó chuyển sang tham chiếu tới Code Name của Sheet

Mỗi Sheet trong Workbook được mã hóa bởi 1 CodeName duy nhất, ít thay đổi, chỉ thay đổi được trong môi trường VBA, còn việc thay đổi tại SheetTab không làm ảnh hưởng tới CodeName này

Trong hình trên, chúng ta thấy phần Code Name của Sheet là phần bên trái, còn phần tên Sheet trong Sheet tab là phần trong dấu ngoặc.

Khi tham chiếu tới Sheet trong VBA, chúng ta nên sử dụng phần Code Name để tham chiếu.

Cách tham chiếu như sau:

Sheet1.Select     ‘Tham chiếu trực tiếp tới CodeName của Sheet

hoặc Sheets[1].Select => Tham chiếu tới số trong CodeName [Index Number]

* Sheets Code Name có thể thay đổi được ở mục Properties/[Name] của Sheet đó

Sau khi đổi tên Sheet chúng ta có thể sử dụng tham chiếu như sau:

Đến đây bạn đã có thể bắt đầu tìm hiểu những điều thú vị của VBA được rồi. VBA giúp ích rất nhiều trong công việc: giúp bạn tăng tốc trong quá trình xử lý trên Excel, giúp lập các báo cáo tự động, điều khiển các chương trình khác như Word, Power Point, Outlook … giúp biến file Excel của bạn trở thành 1 phần mềm chuyên nghiệp …

Cách khai báo và sử dụng Sheets, Range và Cells trong Excel bằng VBA

Cách sử dụng Sheets, Range và Cells bằng VBA

Để dễ hình dùng hơn về cách khai báo 1 File Excel bằng VBA thì File Excel gọi là Workbook, tiếp đến là Worksheet, và còn lại là Range và Cells.

Khi sử dụng dữ liệu trong bảng tính Excel bằng VBA, bạn sẽ có các ràng buộc về Sheets và Workbook như: Range và Cells thuộc Sheets và Sheets đó thuộc Workbook nào?

Để hiểu rõ hơn các bạn đi tiếp từng phần nhỏ nhé.

1] Workbooks

Thông thường khi làm việc với một Workbook thì bạn không cần khai báo trong VBA, nhưng khi truy vấn dữ liệu từ các File Excel khác thì các bạn có thể khai báo như sau:

'khai báo workbook biến wb
Dim wb As Workbook
'gán workbook hiện tại vào biến wb
Set wb = ThisWorkbook

hoặc khai báo cho một Workbook khác:

Dim wb As Workbook
'ví dụ: đường dẫn D:\Desktop\2018-calendar.xlsx
Set wb = Workbooks.Open["đường dẫn"]

2] Worksheet

Sheets trong VBA sẽ được tự động tạo ra khi bạn tạo Sheets từ File Excel. Sheet có nhiều kiểu khai báo như khai báo theo tên, khai báo theo số thức tự Index trong VBAProject.

• Cách khai báo theo số Index Sheets của Excel:

Số Index của Sheets được gán theo thứ tự tạo Sheets. Khi bạn tạo thêm Sheets thứ 2 thì số Index của Sheets đó là 2 và sẽ không thay đổi khi bạn thay đổi vị trí sắp xếp của Sheets.

Bạn đang xem bài viết tại: //bostonenglish.edu.vn/

Private Sub Worksheet_Activate[]
End Sub

0

• Hoặc khai báo theo tên của Sheet

Private Sub Worksheet_Activate[]
End Sub

1

• Cách lấy Sheets đang sử dụng

Private Sub Worksheet_Activate[]
End Sub

2

• Cách chuyển Sheets đang sử dụng

Ví dụ: Khi bạn đang ở Sheet1 thì khi thực hiện câu lệnh dưới đây thì sẽ tự động chuyển sang Sheet2.

Private Sub Worksheet_Activate[]
End Sub

3

hoặc:

Private Sub Worksheet_Activate[]
End Sub

4

3] Range và Cells

Khi truy xuất Range và Cells các bạn có một số lưu ý sau:

• Truy xuất dạng tên đại diện hàng và cột. Ví dụ: A1, B1, C1 ….
• Truy xuất theo vị trí hàng và cột. Ví dụ: Cells A1 sẽ có vị trí hàng 1 và cột 1, tương tự Cells B1 sẽ có bị trí là hàng 1 và cột 2.
• Cột sẽ có vị trí theo số thứ tự như cột bên trái của bảng tính Excel, còn hàng sẽ qui ước theo thứ tự của chữ cái. Ví dụ: A sẽ có vị trí là 1, B là 2 và C là 3…

Cách khai báo Cells:

Bạn đang xem bài viết tại: //bostonenglish.edu.vn/

Private Sub Worksheet_Activate[]
End Sub

5

hoặc

Private Sub Worksheet_Activate[]
End Sub

6Cách khai báo Range:

• Dạng tên đại diện:

Private Sub Worksheet_Activate[]
End Sub

7

• Khai báo dạng vị trí:

Private Sub Worksheet_Activate[]
End Sub

8Khai váo Range và Cells với một Sheets xác định trước.

Private Sub Worksheet_Activate[]
End Sub

9Cách lấy giá trị và gán vào Cells

• Cách lấy giá trị từ một Cells vào biến

Private Sub Worksheet_Deactivate[]
End Sub

0

🔺Nếu bạn không chỉ đích danh Sheets thì VBA sẽ mặc định trỏ đến ActiveSheet.

• Cách gán giá trị một biến vào một Cells:

Private Sub Worksheet_Deactivate[]
End Sub

1

💡 Lưu ý: Khi sử dụng Cells thay cho Range khi gán hoặc lấy giá trị thì các bạn sử dụng vị trí. Ví dụ: Sheets[“ten-sheet1”].Cells[2, 1].Value = giatriA2

Cách chọn 1 Cells hoặc 1 Range

Private Sub Worksheet_Deactivate[]
End Sub

2

💡 Lưu ý: Khi bạn thực hiện câu lệnh Select 1 Cells hay Range thì bạn phải Activate Sheets trước.

4] Một số ví dụ về Sheets, Range và Cells

Trước khi thực hiện các vị dụ dưới đây bạn hãy tạo 1 File Excel với thành phần sau:

• Tạo 3 Sheet có tên lần lượt là: ten-sheet1, ten-sheet2, ten-sheet3.
• Tạo 1 Module trong VBA.

💡 Nếu muốn truy vấn Sheets theo tên thì bạn không nên đặt tên Sheets dài và có dấu.

Ví dụ 1: Đọc tên Workbook và Sheet1

Private Sub Worksheet_Deactivate[]
End Sub

3

Khi tạo hàm trong Module xong, bạn có thể nhấn F5 để chạy lệnh hoặc Click vào ▶ Run Macro.

• Nếu bạn đang đặt con trỏ vào hàm thì Run Macro sẽ thực hiện tại hàm đó.
• Khi con trỏ chuột đang trỏ ở vị trí ngoài bảng soạn lệnh thì VBA sẽ hiện 1 bảng chọn hàm, bạn chọn vào hàm muốn thực hiện và Run để chạy lệnh.

Kết quả: Một cửa sổ thông báo hiển thị lên tên Workbook và tên Worksheet.

Ví dụ 2: Gán tên Workbook vào A1 và tên Sheet vào Cell A2 ở mỗi Sheets.

Tiếp theo ví dụ 1, sau khi gán tên Workbook và Worksheet vào biến thì bạn có thể ghi vào A1 và A2 của Sheet1.

Qua Sheet2 và Sheet3, vì tên Worbook không thay đổi nên bạn chỉ cần gán lại Worksheet vào biến và ghi vào Sheets tương ứng.

Private Sub Worksheet_Deactivate[]
End Sub

4

Kết quả: Ở cả 3 Sheets, tên Workbook sẽ được ghi vào Cells a1, tên Sheets được ghi vào Cell A2.

Ví dụ 3: Đọc giá trị Cell A1 và A2 ở Sheet2 và ghi sang Cells B1 và B2 của Sheet1

Private Sub Worksheet_Deactivate[]
End Sub

5

Kết quả: Giá trị của Cells A1 và A2 của Sheet2 sẽ được ghi vào Cells B1 và B2 của Sheet1.

Bạn đang xem bài viết tại: //bostonenglish.edu.vn/

Bạn đang xem bài viết tại: //bostonenglish.edu.vn/

Tổng hợp mã VBA Excel thao tác với trang tính Excel cực nhanh

Tìm hiểu về worksheet trong VBA Excel

Nếu bạn còn cảm thấy mơ hồ về đối tượng worksheet trong VBA Excel thì trước tiên, chúng ta hãy cùng giải đáp các thắc mắc thường gặp về đối tượng này nhé.

Worksheet là gì?

Worksheet Excel, hay còn được gọi với cái tên trang tính Excel, là giao diện làm việc chính của Excel, bao gồm các hàng và cột giao nhau tạo thành các ô tính. Mỗi ô tính trong trang tính Excel có thể hiển thị các giá trị văn bản, giá trị số, hoặc kết quả của các công thức hàm được nhập vào trong ô.

Trang tính Excel chính là những gì bạn nhìn thấy ngay sau khi mở phần mềm.

Phân biệt Workbook và Worksheet trong Excel

Sau khi nghe khái niệm worksheet, có thể bạn sẽ nghĩ đến một khái niệm trong Excel tương tự mang tên workbook. Liệu bạn có nghĩ rằng 2 khái niệm này đều được dùng để chỉ một đối tượng?

Trên thực tế, workbook và worksheet hoàn toàn khác nhau. Do đó, trong hầu hết các trường hợp, chúng ta không thể dùng chúng thay cho nhau.

Workbook là một file Excel, trong khi đó, worksheet chỉ là một trang tính Excel nằm trong file đó. Nói cách khác, một workbook có thể bao gồm nhiều worksheet. Bạn có thể hiểu workbook như một quyển sách mà các trang của nó được gọi là worksheet.

Các cách gọi sheet trong VBA Excel

Đặt trong một mã VBA Excel cụ thể, có 4 phương pháp bạn có thể sử dụng để gọi sheet. Cụ thể là các lệnh dưới đây:

• ActiveSheet – Lệnh gọi trang tính Excel đang được kích hoạt.
• Sheet Tab Name – Lệnh gọi trang tính Excel dựa theo tên.

Ví dụ: Sheets[“Data”] là trang tính Excel có tên Data.

• Sheet Index Name – Lệnh gọi trang tính Excel dựa theo số thứ tự trong file Excel.

Ví dụ: Sheets[2] là trang tính thứ hai trên thanh sheet tab tính từ trái sang.

• Sheet Code Name – Lệnh gọi trang tính Excel có dựa theo code name.

Ví dụ: Sheet3 là trang tính Excel có code name trong thẻ Properties VBA Excel là Sheet3.

Phân biệt lệnh VBA Excel Sheet.Activate và Sheet.Select

Một lưu ý nữa khi sử dụng VBA Excel với đối tượng worksheet là sự khác nhau giữa 2 lệnh trong VBA Excel mang tên Sheet.Activate và Sheet.Select.

Sheet.Activate là lệnh kích hoạt một sheet. Điều này nghĩa là chỉ có duy nhất một trang tính Excel được kích hoạt tại một thời điểm. Trang tính được kích hoạt với VBA Excel sẽ trở thành ActiveSheet.

Sheet.Select là lệnh chọn một sheet. Tuy nhiên, lệnh VBA Excel này có thể chọn nhiều trang tính Excel cùng một lúc.

• Nếu chỉ chọn một trang tính Excel duy nhất, trang tính này sẽ trở thành ActiveSheet.
• Nếu chọn nhiều trang tính Excel cùng một lúc, chỉ có trang tính đầu tiên tính từ trái sang trong sheet tab được coi là ActiveSheet.

Để chọn nhiều sheet với VBA Excel, chúng ta sẽ sử dụng lệnh viết theo Sheet Tab Name như sau:

Worksheets[Array[“Sheet2”, “Sheet3”]].Select

Xem thêm: Giới thiệu đặc điểm file VBA Excel và các tính năng VBA cơ bản

Các mã VBA Excel thao tác với trang tính Excel

Vậy là bạn đã sẵn sàng khám phá VBA Excel để thao tác với đối tượng trang tính Excel rồi. Chúng ta hãy cùng tìm hiểu ngay các mã VBA Excel thần kỳ có thể giúp bạn rút ngắn thời gian làm việc nhé!

Với từng lệnh VBA Excel, mình sẽ đưa ra mục đích thực hiện thao tác để dựa vào đó, chúng ta viết ra lệnh phù hợp.

Mã VBA Excel ẩn/hiện trang tính Excel

Mã VBA Excel ẩn/hiện toàn bộ các trang tính Excel

1. Mục đích: Ẩn hoặc hiện toàn bộ các trang tính Excel trong file hiện tại, không quan tâm đến tên hay số thứ tự của trang tính.
2. VBA Excel

Sub MoAn_ToanBo_Sheet[]
‘Khai báo các biến sử dụng
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub

Trong đó:

• ws.Visible = xlSheetVisible – Lệnh VBA Excel bỏ ẩn trang tính Excel.

• ws.Visible = xlSheetHidden – Lệnh VBA Excel ẩn trang tính Excel cấp độ 1 [cấp thông thường], mở ẩn trong sheet tab.

• ws.Visible – xlSheetVeryHidden – Lệnh VBA Excel ẩn trang tính Excel cấp độ 2, chỉ bỏ ẩn được trong VBA Excel.

Mã VBA Excel ẩn một trang tính Excel nhất định

1. Mục đích

• Ẩn một trang tính Excel cụ thể dựa theo tên hoặc số thứ tự, code name của trang.
• Kích hoạt trang tính MENU [trang tính mục lục có tính chất điều hướng đến các trang tính khác].

1. VBA Excel

Sub An_Mot_Sheet[]
Sheets[“MENU”].Visible = xlSheetVisible
Sheets[“Data”].Visible = xlSheetHidden   ‘Ẩn Sheet có tên là Data
Sheets[“MENU”].Activate
End Sub

Lưu ý:

File Excel luôn phải có ít nhất một trang tính được hiển thị nên chúng ta không được ẩn tất cả các trang tính. Do đó, khi làm việc trên VBA Excel, code hiển thị Sheets[“MENU”] phải luôn viết trước code ẩn Sheets[“Data”].

Nếu bạn chưa biết cách sử dụng VBA Excel để tạo một trang tính Menu [mục lục] để liên kết đến tất cả các trang tính trong file Excel thì hãy tìm hiểu ngay cách làm trong bài viết dưới đây nhé.

Xem thêm: Hướng dẫn sử dụng VBA để tạo menu Excel liên kết nhiều chức năng

Mã VBA Excel hiện một trang tính Excel nhất định

1. Mục đích

• Hiện một trang tính Excel cụ thể dựa theo tên hoặc số thứ tự, code name của trang.
• Kích hoạt trang tính Excel vừa được mở ẩn.

1. VBA Excel

Sub An_Sheet[]
With Sheet_01
.Visible = xlSheetVisible
.Activate
End With
End Sub

Nếu thao tác ẩn sheet trong Excel của bạn với VBA Excel yêu cầu cao hơn như ẩn một số sheet nhất định hay ẩn tất cả các sheet trừ sheet cuối cùng, hãy tham khảo các mã VBA Excel trong bài viết dưới đây.

Xem thêm: Hướng dẫn thao tác ẩn Sheet trong Excel bằng lệnh VBA

Mã VBA Excel thêm mới một trang tính

1. Mục tiêu:Tạo một trang tính Excel mới dựa vào các yếu tố dưới đây.

• Tên trang tính mới không được trùng với tên các trang tính đã có sẵn trong file Excel.
• Vị trí đặt trang tính trên thanh sheet tab được gán biến trong VBA Excel để tiện theo dõi và quản lý.

1. VBA Excel

Sub ThemMoi_Sheet[]
Dim NewWS As Worksheet, ws As Worksheet
Set NewWS = ThisWorkbook.Worksheets.Add   ‘Thêm mới vào vị trí mặc định
Dim TenSheet As String
TenSheet =  “Data”       ‘Tên Sheet muốn tạo
For Each ws In ThisWorkbook.Worksheets    ‘Vòng lặp xét tên các Sheet đã có
If ws.Name = TenSheet Then
TenSheet = TenSheet & “[1]”   ‘Nếu trùng với tên đã có thì thêm ký tự [1]
End If
Next ws
NewWS.Name = TenSheet     ‘Đặt tên cho Sheet mới tạo
End Sub

Tại dòng 3 SetNewWS:

• Có thể viết ThisWorkbook.Worksheets gọn lại là Worksheets hoặc Sheets.
• Với vị trí trang tính Excel được thêm mới, bạn có thể chọn một trong các lệnh dưới đây để điền vào VBA Excel:
  • Sheets.Add[After:=Sheets[“Sheet1”]] – Lệnh VBA Excel thêm trang tính Excel mới vào sau Sheet1.
  • Sheets.Add[Before:=Sheets[“Sheet1”]] – Lệnh VBA Excel thêm trang tính Excel mới vào trước Sheet1.
  • Sheets.Add[After:=Sheets[Sheets.Count]] – Lệnh VBA Excel thêm trang tính Excel mới vào vị trí cuối cùng trên thanh sheet tab.

Mã VBA Excel khóa/mở khóa một trang tính

Mã VBA Excel khóa trang tính

1. Mục đích:Khóa trang tính Excel hiện tại.
2. VBA Excel

Dưới đây là đoạn mã bạn có thể đưa vào trong module VBA Excel của mình, kết hợp với các dòng lệnh khác để tạo nên một lệnh VBA Excel hoàn chỉnh.

Worksheets[“Sheet1″].Protect _
Password:=”nội dung mã”, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFormattingCells:=False, _
AllowFormattingColumns:=False, _
AllowFormattingRows:=False, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=False, _
AllowInsertingHyperlinks:=False, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=False, _
AllowSorting:=False, _
AllowFiltering:=False, _
AllowUsingPivotTables:=False

Lưu ý rằng lệnh UserInterfaceOnly:=True:

• Không có sẵn trong code Record Macro, do đó bạn phải viết thủ công đoạn mã VBA Excel này.
• Không lưu trữ khi đóng file Excel, do đó bạn phải kích hoạt lại mỗi khi mở file.

Mã VBA Excel mở khóa trang tính

1. Mục đích: Mở khóa trang tính Excel dựa theo phương pháp khóa đã thiết lập cho trang tính.
2. VBA Excel

Dưới đây là lệnh mở khóa trang tính Excel bạn có thể đưa vào trong VBA Excel:

Worksheets[“Sheet1”].Unprotect

Lệnh VBA Excel này sẽ mở khóa trang tính Excel dựa vào đúng phương pháp bạn đã sử dụng để khóa trang tính.

Còn dưới đây là một lệnh hoàn chỉnh trong VBA Excel được dùng để mở khóa trang tính:

Private Sub Workbook_Open[]
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect UserInterfaceOnly:=True
Next ws
End Sub

Lưu ý: Lệnh VBA Excel này sử dụng cấu trúc vòng lặp để áp dụng lên toàn bộ các trang tính trong file Excel. Nếu bạn chỉ dùng lệnh cho một trang tính Excel thì không cần dùng cấu trúc vòng lặp này.

Hy vọng rằng qua bài viết này các bạn đã có thể biết thêm nhiều kiến thức khi lập trình với đối tượng Sheet trong VBA.