Đã bao giờ bạn gặp tình trạng Danh sách thả xuống (Dropdown List của chức năng Data Validation) hiển thị không hết nội dung theo tên các đối tượng, hoặc có kích thước nhỏ khó nhìn chưa? Khi gặp tình trạng này thì khắc phục nó như thế nào? Hãy cùng Học Excel Online tìm hiểu cách thay đổi kích thước danh sách thả xuống trong Data Validation bằng VBA Excel nhé.

Đặc điểm của Dropdown List tạo bởi Data Validation

Dropdown List trong Data Validation là một chức năng được sử dụng khá thường xuyên trong Excel. (Nếu bạn chưa biết cách làm có thể xem tại bài viết: Tạo một danh sách tùy chọn trong Excel: tĩnh, động, từ một bảng tính khác). Nhưng danh sách này lại có đặc điểm:

• Độ rộng (bề ngang) của danh sách phụ thuộc vào độ rộng của cột nơi chứa danh sách đó (column width)
• Kích thước của danh sách phụ thuộc vào chế độ hiển thị của Sheet (Zoom) và độ lớn của danh sách.

Do đó có thể xảy ra các trường hợp:

• Độ rộng của cột nơi chứa danh sách nhỏ, trong khi nội dung trong danh sách có số ký tự lớn, dẫn tới danh sách không hiển thị hết nội dung
• Chế độ hiển thị của Sheet nhỏ (hoặc danh sách quá lớn) sẽ dẫn tới nội dung bên trong danh sách rất nhỏ, khó nhìn, dễ chọn sai.

Và bản thân Excel không hỗ trợ trực tiếp người dùng thay đổi đặc điểm này. Do đó nếu muốn thay đổi, bạn phải sử dụng VBA trong Excel.

Cách thay đổi kích thước danh sách thả xuống trong Data Validation bằng VBA Excel

Thay đổi độ rộng của danh sách chọn mỗi khi tác động tới danh sách đó

Nguyên tắc này có thể mô tả như sau:

• Mục tiêu tác động: Thuộc tính Column Width của cột nơi chứa ô áp dụng Data Validation/ List.
• Khi chọn ô chứa List, độ rộng (Column Width của cột đó) sẽ thay đổi
• Khi không chọn ô chứa List nữa, độ rộng của cột sẽ trả về bình thường (trở về trạng thái ban đầu)

=> Dựa trên nguyên tắc này, chúng ta có thể thấy đây là 1 sự kiện Selection_Change, tức là bạn chọn 1 ô nào đó trong Sheet.

(Xem thêm: Lập trình sự kiện trong VBA Excel)

Cách thực hiện như sau:

Ví dụ tại ô B1 ta có áp dụng danh sách chọn tạo bởi Data Validation theo vùng A3:A7, trong đó cột B có Column Width = 8

Khi chọn vào ô B1 (ô chứa danh sách chọn), chúng ta muốn độ rộng cột B thay đổi từ 8 thành 14. Câu lệnh như sau:

• Thiết lập độ rộng

Range(“B1”).ColumnWidth = 8

Range(“B1”).ColumnWidth = 14

• Thiết lập logic khi nào áp dụng các độ rộng trên:

IF mệnh đề logic Then

Range(“B1”).ColumnWidth = 14

Else

Range(“B1”).ColumnWidth = 8

End If

• Mệnh đề logic là: Khi chọn ô B1.

Mệnh đề này liên quan tới việc giới hạn phạm vi trong Worksheet, do đó cú pháp là:

IF Not Application.Intersect(Target, Range(“B1”)) Is Nothing Then

Range(“B1”).ColumnWidth = 14

Else

Range(“B1”).ColumnWidth = 8

End If

Trong toàn bộ cú pháp trên (phần tô màu hồng và đỏ), chú ý vùng Range(“B1”) là phần vùng giới hạn nơi diễn ra sự kiện. Phần này sẽ thay đổi tùy theo yêu cầu, còn phần chữ màu hồng là cú pháp quy định, bắt buộc phải viết lại đúng cú pháp đó.

Đặt toàn bộ cấu trúc này vào trong sự kiện Worksheet_SelectionChange, ta có:

Kết quả khi áp dụng sự kiện này như sau:

Thay đổi chế độ hiển thị của Sheet mỗi khi tác động tới danh sách đó

Nguyên tắc này có thể mô tả như sau:

• Mục tiêu tác động: Kích cỡ Zoom của màn hình Excel
• Logic: Khi chọn ô chứa List, màn hình ứng dụng Excel sẽ tăng kích cỡ Zoom lên
• Khi không chọn ô chứa List nữa, màn hình ứng dụng Excel sẽ trả về kích cỡ Zoom bình thường (trở về trạng thái ban đầu)

Cách thực hiện như sau:

• Sự kiện: Worksheet_SelectionChange
• Đối tượng của sự kiện: ô B1 của Sheet nơi diễn ra sự kiện
• Logic: Nếu ô B1 được chọn, ActiveWindow.Zoom (thuộc tính Zoom của đối tượng ActiveWindow) sẽ bằng 150. Nếu chọn ô khác ô B1, ActiveWindow.Zoom trả về 100 (mặc định là 100%)

Cú pháp như sau:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range(“B1”)) Is Nothing Then

ActiveWindow.Zoom = 150

Else

ActiveWindow.Zoom = 100

End If

End Sub

Kết quả của câu lệnh này như sau:

Các bạn có thể tải về file ví dụ cho nội dung này tại địa chỉ: http://bit.ly/2NqLAHp

Qua bài viết này, các bạn có thể học thêm được kỹ năng lập trình sự kiện trong VBA. Đây là ứng dụng đơn giản những khá hiệu quả giúp bạn tùy biến các chế độ làm việc trong Excel, giúp dễ làm việc hơn, tăng hiệu quả trong công việc.

Ngoài ra các bạn có thể xem thêm một số bài viết sau:

Hướng dẫn cách viết cấu trúc IF THEN ELSE trong VBA Excel

Hướng dẫn cách sử dụng vòng lặp For Each trong VBA Excel

Hướng dẫn cách lập báo cáo chi tiết tự động trong Excel mới nhất 2019