Trong một trang tính, Carol có một ô được định dạng thành “Kế toán”. Nếu ai đó vô tình nhập ngày (mm / dd / yy) vào ô đó, Excel sẽ tự động thay đổi định dạng của ô để hiển thị ngày chính xác. Tuy nhiên, nếu cô ấy cố gắng nhập lại số tiền vào ô đó, nó sẽ không quay lại định dạng “Kế toán”; ô vẫn ở định dạng ngày tháng.

Điều này là tốt nếu người dùng nhìn thấy lỗi và sửa nó, nhưng nhiều khi điều này xảy ra trong mẫu có văn bản “bản mẫu” và mẫu được bảo vệ không có quyền truy cập vào các ô định dạng. Carol tự hỏi liệu có ai có bất kỳ ý tưởng nào về lý do tại sao điều này xảy ra và cách sửa nó không.

Nếu bạn muốn một cách nhanh chóng để ngăn chặn sự thay đổi định dạng trong các trang tính đơn giản, hãy làm theo các bước sau:

  1. Hiển thị hộp thoại Tùy chọn Excel. (Trong Excel 2007, hãy nhấp vào nút Office và sau đó nhấp vào Tùy chọn Excel. Trong Excel 2010 hoặc phiên bản mới hơn, hiển thị tab Tệp của dải băng và sau đó nhấp vào Tùy chọn.)

  2. Ở bên trái của hộp thoại, nhấp vào Nâng cao.

  3. Cuộn xuống dưới cùng của các tùy chọn nâng cao. (Xem Hình 1.)

  4. Đảm bảo rằng tùy chọn Nhập Công thức Chuyển đổi được chọn.

  5. Bấm OK.

Tùy chọn cụ thể này khiến Excel đánh giá (phân tích cú pháp) thông tin đã nhập giống như cách mà Lotus 1-2-3 đã làm. Điều này có nghĩa là ngày tháng không còn được phân tích cú pháp thành ngày tháng nữa mà là một công thức. Do đó, nếu ai đó nhập 11-16-13 vào một ô, thì nó được phân tích cú pháp là “mười một trừ mười sáu trừ mười ba” và hiển thị trong ô là -18. Bởi vì nó không được phân tích cú pháp như một ngày, nên định dạng Kế toán vẫn được liên kết với ô, như mong muốn.

Tuy nhiên, có những hạn chế đối với cách tiếp cận này. Bởi vì Excel sau đó phân tích cú pháp bất kỳ mục nhập nào theo quy tắc Lotus, người dùng của bạn có thể kết luận rằng trang tính của bạn không hoạt động đúng cách vì nó không tuân theo các quy tắc giống như các trang tính Excel khác. Đây là lý do tại sao tôi đã đề cập rằng cách tiếp cận này có thể được chấp nhận đối với các trang tính đơn giản; bạn sẽ cần phải xác định xem trang tính của bạn có đủ điều kiện hay không.

Nếu bạn không muốn thay đổi cách phân tích cú pháp được thực hiện, cách tốt nhất có thể là thêm một số trình xử lý sự kiện vào trang tính của bạn. Ví dụ: bạn có thể bao gồm một trình xử lý sự kiện xem xét nơi dữ liệu được nhập và đảm bảo rằng bất kỳ thay đổi nào đối với các ô đó vẫn giữ nguyên định dạng mong muốn.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range     Set rngToCheck = Range("E2")

If Intersect(Target, rngToCheck) Then         rngToCheck.NumberFormat = _           "_($ #,##0.00_);_($ (#,##0.00);_($* "" - ""??_);_(@_)"

End If End Sub

Trong ví dụ này, ô mà bạn muốn giữ lại Định dạng kế toán là E2, như được gán cho biến rngToCheck. Nếu bạn muốn buộc định dạng trên một phạm vi ô khác, thì chỉ cần thay đổi dòng gán.

Nếu bạn muốn linh hoạt hơn một chút, thì bạn có thể sử dụng một bộ trình xử lý sự kiện khác. Ví dụ: các ví dụ sau sử dụng cả sự kiện SelectionChange và Change của đối tượng Worksheet. Chúng dẫn đến một cái gì đó không quá ép buộc một định dạng cụ thể, nhưng nó ngăn định dạng của một ô không bị thay đổi so với bất kỳ định dạng nào trước đó. Do đó, cách tiếp cận này bảo vệ mọi định dạng, không chỉ thực thi một định dạng Kế toán.

Dim nFormat As String

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range     Set rngToCheck = Range("E2")

If Intersect(Target, rngToCheck) Then         rngToCheck.NumberFormat = nFormat     End If End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

nFormat = Target.NumberFormat End Sub

Trình xử lý sự kiện SelectionChange kích hoạt đầu tiên, đặt định dạng hiện có thành biến nFormat. Sau đó, trình xử lý sự kiện Thay đổi kích hoạt và đặt định dạng trở lại ban đầu.

Một cách tiếp cận khác mà bạn có thể thử là sử dụng xác thực dữ liệu. Cách tiếp cận này không yêu cầu bất kỳ macro nào và do đó phù hợp nếu sổ làm việc của bạn sẽ được sử dụng bởi những người có thể đã tắt macro trên hệ thống của họ.

Làm theo các bước chung sau:

  1. Chọn ô hoặc các ô có định dạng bạn muốn thực thi.

  2. Hiển thị tab Dữ liệu của dải băng.

  3. Bấm vào công cụ Xác thực Dữ liệu trong nhóm Công cụ Dữ liệu. Excel sẽ hiển thị hộp thoại Xác thực Dữ liệu.

  4. Sử dụng danh sách thả xuống Cho phép, chọn Tùy chỉnh. (Xem Hình 2.)

  5. Trong hộp Công thức, hãy nhập như sau: = CELL (“format”, B2) = “C2”. Đặt bất kỳ cài đặt xác thực dữ liệu nào khác như mong muốn.

  6. Bấm OK.

Công thức (bước 5) kiểm tra định dạng của ô và cho phép hoặc không cho phép nhập dựa trên định dạng đó. Trong công thức như đã trích dẫn, định dạng C2 là tên nội bộ của định dạng Kế toán. Bạn có thể dễ dàng thay đổi mã trong công thức sang một số định dạng khác, chẳng hạn như “, 2”, “C2”, “C0”, “C2-” hoặc “C0-” tùy thuộc vào sở thích của bạn. Cách dễ nhất để tìm ra định dạng bạn nên sử dụng là định dạng một ô, như mong muốn, trước khi áp dụng quy tắc xác thực dữ liệu. (Ví dụ: giả sử bạn áp dụng định dạng cho ô L13.) Sau đó, bạn có thể sử dụng công thức này trong một ô khác để xem định dạng mà Excel tin rằng bạn đã áp dụng:

=CELL("format",L13)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (12729) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.