Câu hỏi Excel của tuần này đến từ John đóng quân ở Okinawa.
Tôi đang xây dựng một bảng tính Excel để phản ánh các chuyến khởi hành và điểm đến. Về cơ bản sẽ có ba ô: Thời gian khởi hành thực tế, thời gian dự kiến đi và thời gian đến dự kiến. Tôi muốn người đó chỉ có thể nhập (ví dụ) 2345 và để ô tự động định dạng màn hình hiển thị 23:45. Thay vào đó, những gì tôi nhận được là 0:00, bất kể công thức hoặc định dạng. Và, phép tính sẽ không hiển thị gì ngoài 0:00 nếu người dùng không chuyển phím và dấu hai chấm. Tôi biết làm như vậy có vẻ đơn giản, tuy nhiên, mỗi giây nhỏ được lưu đều có giá trị, đặc biệt là khi nhập nhiều lần dữ liệu tương tự trong Excel.
Để thực hiện việc này, bạn cần sử dụng trình xử lý sự kiện. Các trình xử lý sự kiện là mới trong Excel 97 và đã được thảo luận trở lại trong Chạy Macro Mỗi khi Thay đổi Giá trị Ô trong Excel. Tuy nhiên, quay lại mẹo đó, trình xử lý sự kiện đang áp dụng một định dạng khác cho các ô nhất định. Ứng dụng này hơi khác, vì vậy hãy truy cập lại trình xử lý sự kiện.
Trình xử lý sự kiện là một đoạn mã macro nhỏ được thực thi mỗi khi một sự kiện nhất định xảy ra. Trong trường hợp này, chúng tôi muốn macro chạy bất cứ khi nào bạn thay đổi ô. Để thiết lập trình xử lý sự kiện, hãy làm theo các bước sau:
- Một trình xử lý sự kiện chỉ được liên kết với một trang tính duy nhất. Bắt đầu từ trang tính đó và nhấn alt-F11 để mở trình chỉnh sửa VB.
- Trong cửa sổ phía trên bên trái (Dự án - Dự án VBA), hãy nhấp đúp vào tên trang tính của bạn.
- Trong ngăn bên phải, nhấp vào menu thả xuống bên trái và thay đổi chung thành Trang tính.
- Trong menu thả xuống bên phải, chọn Thay đổi.
Điều này sẽ khiến Excel nhập trước macro shell sau cho bạn:
Private Sub Worksheet_Change(ByVal Target As Range) UserInput = Target.Value If UserInput> 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End SubBất kỳ khi nào một ô được thay đổi, ô đã được thay đổi sẽ được chuyển đến chương trình này trong biến có tên là "Target". Khi ai đó nhập thời gian bằng dấu hai chấm trong trang tính, nó sẽ đánh giá một số nhỏ hơn một. Khối If đảm bảo chỉ thay đổi các ô nếu chúng lớn hơn một. Tôi sử dụng các hàm left () và right () để ngắt dữ liệu nhập của người dùng thành giờ và phút và chèn dấu hai chấm vào giữa.
Bất cứ khi nào người dùng nhập "2345", chương trình sẽ thay đổi mục này thành 23:45.
Cải tiến có thể có
Nếu bạn muốn giới hạn chương trình chỉ hoạt động trên các cột A&B, bạn có thể kiểm tra giá trị của Target.Column và chỉ thực thi khối mã nếu bạn đang ở trong hai cột đầu tiên:
Private Sub Worksheet_Change(ByVal Target As Range) ThisColumn = Target.Column If ThisColumn 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End SubNếu bạn muốn thực hiện các thay đổi đối với trang tính mà không nhập dấu hai chấm (ví dụ: bạn cần thêm công thức hoặc thay đổi tiêu đề, v.v.), bạn có thể chuyển trình xử lý sự kiện bằng macro ngắn này:
Sub TurnEventHanderOff() Application.EnableEvents = False End Sub You can turn event handlers back on with this macro: Sub TurnEventHanderOff() Application.EnableEvents = True End SubNếu bạn tiếp nhận khái niệm này và thay đổi nó, có một khái niệm quan trọng cần phải nhận biết. Khi macro xử lý sự kiện chỉ định một giá trị mới cho ô được Target tham chiếu, Excel sẽ tính đây là một thay đổi trang tính. Nếu bạn không chuyển nhanh các trình xử lý sự kiện, thì Excel sẽ đệ quy bắt đầu gọi trình xử lý sự kiện và bạn nhận được kết quả không mong muốn. Trước khi thực hiện thay đổi đối với trang tính trong trình xử lý sự kiện thay đổi, hãy đảm bảo tạm ngừng xử lý sự kiện với dòng Application.EnableEvents.
