Làm thế nào để bắt buộc nhập dữ liệu vào một cột trong Excel dựa trên giá trị của một cột khác?

Tôi có một bảng tính Excel và muốn thiết lập một cột bắt buộc dựa trên giá trị của một cột khác. Ví dụ, bảng tính của tôi có các cột ngày, giờ, số vùng, trạng thái "đã đưa vào sử dụng" và ghi chú. Tôi muốn cột "đã đưa vào sử dụng" là bắt buộc nếu giá trị trong cột số vùng lớn hơn 0, và không thể lưu bảng tính dựa trên tiêu chí này.

Trả lời:
Xem đoạn mã bên dưới. Đoạn mã này được đặt trong mô-đun ThisWorkbook. Hãy xem bạn cần chỉnh sửa mã ở đâu với các ID cột cần thiết.

Tôi không chắc bạn có đủ chuyên môn về vấn đề này hay không, vì vậy tôi sẽ cung cấp hướng dẫn đầy đủ để cài đặt mã.

Hướng dẫn cài đặt mã VBA sự kiện:

1. Nhấp chuột phải vào tên tab trang tính.

2. Chọn "Xem mã" (sẽ mở cửa sổ trình soạn thảo VBA tại mô-đun của trang tính).

3. Nhấp đúp vào tên mô-đun "ThisWorkbook" trong Trình khám phá dự án (cột bên trái).

4. Sao chép đoạn mã VBA bên dưới và dán vào trình soạn thảo VBA.

5. KHÔNG đổi tên subreddit ở giai đoạn này (Sẽ cần đổi tên sau)

6. Đóng trình soạn thảo VBA (nhấn vào dấu X ở góc trên bên phải cửa sổ trình soạn thảo VBA).

7. Sử dụng chức năng "Lưu thành" để lưu bảng tính dưới dạng có hỗ trợ macro.

Hãy đảm bảo rằng macro đã được bật như sau.

1. Nếu có dải băng Nhà phát triển, hãy chuyển sang bước 3. Nếu không, hãy nhấp chuột phải vào bất kỳ đâu trên một trong các dải băng và chọn "Tùy chỉnh dải băng".

2. Ở phía bên phải của hộp thoại, đánh dấu vào ô "Nhà phát triển" và nhấn OK. (Hãy chắc chắn rằng bạn nhấn OK chứ không chỉ nhấn vào dấu X ở góc trên bên phải của hộp thoại để đóng nó, nếu không thay đổi sẽ không được lưu).

3. Chọn thanh công cụ Phát triển.

4. Chọn Bảo mật Macro (trong khối Mã ở cuối bên trái của dải băng Nhà phát triển).

5. Hộp thoại hiển thị mặc định sẽ là Macro ở cột bên trái.

6. Chọn nút tùy chọn bảo mật cần thiết. (Tùy chọn "Vô hiệu hóa tất cả macro kèm thông báo" là được.)

7. Nhấp vào OK để đóng hộp thoại.

Bây giờ bạn cần mở lại trình soạn thảo VBA và đổi tên hàm con thành một hàm con sự kiện hợp lệ . Lý do là vì bạn sẽ không thể lưu sổ làm việc nếu thiếu bất kỳ dữ liệu nào và bạn chưa sẵn sàng để sửa chữa.

• Tổ hợp phím Alt và F11 sẽ chuyển đổi giữa trình soạn thảo VBA và trang tính, vì vậy hãy mở lại trình soạn thảo VBA.

• Đổi tên hàm con bằng cách xóa từ khóa STOP , sao cho tên hàm con bây giờ sẽ như sau. (Tôi đã in đậm các ký tự này trong đoạn mã ví dụ bên dưới)

• Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

• Nếu thiếu dữ liệu cần thiết, quá trình lưu sẽ bị hủy, một hộp thoại thông báo sẽ hiển thị và con trỏ sẽ tự động di chuyển đến ô cần điền. Điều này có thể xảy ra nhiều lần nếu có nhiều ô thiếu dữ liệu.

Nếu có bất kỳ vấn đề gì, đừng ngần ngại liên hệ lại với tôi.

 Private Sub STOPWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

 Dim wsToTest As Worksheet

 Dim rngToTest As Range

 Dim rCel As Range

 Set wsToTest = Worksheets("Sheet1") 'Edit "Sheet1" to your worksheet name with required data

 With wsToTest

 Set rngToTest = .Range(.Cells(2, "C"), .Cells(.Rows.Count, "C").End(xlUp)) 'Edit "C" to column with "Zone" (2 instances)

 For Each rCel In rngToTest

 If rCel <> 0 Then

 If .Cells(rCel.Row, "D") = "" Then 'Edit "D" to your column for "Returned to service"

 MsgBox "Cannot save while cell " & .Cells(rCel.Row, "D").Address(0, 0) & " is not populated." & vbCrLf & _

 "Please enter data and save again."

 Cancel = True

 Application.Goto .Cells(rCel.Row, "D")

 Exit For

 End If

 End If

 Next rCel

 End With

End Sub

Trả lời:
Cảm ơn bạn đã trả lời! Mọi thứ dường như hoạt động tốt ngoại trừ dòng bắt đầu bằng...

MsgBox "Không thể lưu khi ô ", v.v. Tôi nhận được lỗi "Lỗi biên dịch. Dự kiến: Biểu thức". Tôi đã sao chép và dán, nhưng có lẽ tôi đã làm sai ở đâu đó.

Trả lời:
Đây là đoạn mã trông như thế nào.

Trả lời:
Hãy xóa dòng trống được thêm vào giữa hai dòng màu đỏ. Dòng này do người biên tập diễn đàn thêm vào.

Dấu cách và dấu gạch dưới ở cuối dòng mã thực chất là một dấu xuống dòng trong một dòng mã duy nhất và chúng ta không thể có một dòng trống giữa chúng.

Tôi sẽ chỉnh sửa bài đăng chứa mã nguồn và định dạng lại để hy vọng loại bỏ được dòng trống được thêm vào. Việc định dạng lại trong diễn đàn này được thực hiện bằng cách chọn toàn bộ văn bản trong mã nguồn, sau đó nhấp vào menu thả xuống có chữ "Standard" và chọn "Code block". Tôi xin lỗi vì đã không làm điều này ngay từ đầu.

P.S. Cảm ơn bạn đã đăng ảnh chụp màn hình hiển thị lỗi. Hãy tiếp tục làm như vậy vì một bức ảnh đáng giá ngàn lời nói và nhờ đó tôi đã có thể xác định được vấn đề ngay lập tức.

Trả lời:
Cách đó hiệu quả tuyệt vời! Cảm ơn bạn rất nhiều! Tôi còn một câu hỏi nữa (hy vọng là được giải đáp). Tôi cần thêm gì để có thêm 5 cột nữa? Ví dụ, trong hình bên dưới, tôi muốn thêm mỗi cột có nhãn số vùng và mỗi cột có nhãn "đã đưa vào sử dụng" nhưng được chia nhỏ theo tháng. Vì vậy, số lượng xe đã đưa vào sử dụng trong tháng Giêng phụ thuộc vào số vùng của tháng Giêng, nếu bạn hiểu ý tôi. Và tôi sẽ cần làm điều đó cho tổng cộng 6 tháng.

Cảm ơn bạn rất nhiều vì sự giúp đỡ.

Sarah

Trả lời:
Chào Sarah,

Tôi rất vui khi biết rằng chúng ta đang đi đúng hướng. Tuy nhiên, tôi vẫn còn một vài câu hỏi nữa.

Định dạng nào được sử dụng để nhập nhãn tháng? Chúng có thể chỉ đơn giản là văn bản như "Tháng Giêng", "Tháng Hai", v.v. hoặc chúng có thể là ngày tháng với định dạng số được đặt là "mmmm").

Bình luận của bạn: "Và tôi sẽ cần làm việc đó trong tổng cộng 6 tháng." Tôi nghĩ rằng nếu dữ liệu được hoàn thành cho mỗi tháng thì dữ liệu tổng cộng sẽ tự động được điền bằng công thức SUM và do đó không cần phải kiểm tra, hay tôi đang hiểu sai điều gì?

Mặc dù tôi đã nói " một bức ảnh đáng giá ngàn lời nói" , nhưng một ví dụ cụ thể trong bảng tính thậm chí còn tốt hơn. Sẽ rất hữu ích nếu bạn có thể tải lên một bảng tính ví dụ với một vài hàng dữ liệu giả, bao gồm cả các công thức trong phần tổng cộng.

>>Tôi không chắc bạn có cần hỗ trợ để tải tệp lên để chia sẻ trên OneDrive hay không, nhưng phòng trường hợp cần, đây là hướng dẫn để thực hiện. Xin lưu ý rằng việc chia sẻ liên kết đến một tệp cụ thể trên OneDrive không cho phép tôi truy cập vào bất kỳ tệp nào khác trên OneDrive của bạn và trên thực tế, tôi thậm chí không thể xem danh sách tệp. (Nếu bạn đã sử dụng OneDrive và quy trình lưu tệp của bạn khác thì bạn có thể bắt đầu từ bước 7 để lấy liên kết, nhưng vui lòng nén tệp trước khi tải lên .)

Việc chia sẻ liên kết lên OneDrive của doanh nghiệp thường không hoạt động vì doanh nghiệp đã áp dụng các biện pháp bảo mật ngăn chặn điều này. Một số người tải lên từ OneDrive cá nhân của họ.

1. Hãy nén các tệp bảng tính của bạn. Đừng chỉ lưu tệp bảng tính chưa nén vào OneDrive vì các tệp bảng tính sẽ mở bằng Excel trực tuyến và chức năng hạn chế của phiên bản trực tuyến có thể gây ra sự cố.

2. Để nén tệp: Trong Windows Explorer, nhấp chuột phải vào tệp đã chọn và chọn Gửi đến -> Thư mục nén (zipped). Bằng cách giữ phím Ctrl và nhấp chuột trái một lần vào mỗi tệp, bạn có thể chọn nhiều sổ làm việc trước khi nhấp chuột phải vào một trong các lựa chọn để gửi vào một tệp nén và tất cả chúng sẽ được bao gồm trong một tệp Zip duy nhất.

3. Không sử dụng các ứng dụng nén của bên thứ ba vì tôi không thể giải nén chúng.

4. Hãy truy cập vào đường dẫn này: https://onedrive.live.com . Sử dụng cùng tên đăng nhập và mật khẩu mà bạn dùng cho diễn đàn này.

5. Chọn Tải lên bên dưới thanh màu xanh ở phía trên và duyệt đến thư mục nén cần tải lên.

6. Chọn Mở (hoặc chỉ cần nhấp đúp). (Hãy kiên nhẫn và đợi một chút để tệp hiển thị sau khi bạn thấy cửa sổ bật lên thông báo hoàn tất.)

7. Nhấp chuột phải vào tên tệp trong OneDrive.

8. Chọn Chia sẻ.

9. Nhấp vào biểu tượng liên kết (trông giống như các mắt xích) ở phía dưới bên trái của hộp thoại (ngay phía trên "Sao chép liên kết").

10. Nhấp vào nút Sao chép.

11. Hãy quay lại diễn đàn này và dán đường dẫn vào đây.

Tôi rất mong nhận được bản sao của cuốn sách bài tập và tôi sẽ cố gắng hết sức để đáp ứng yêu cầu của bạn.

Trả lời:
Hãy thử sử dụng tập tin Excel mà tôi đã tải lên dưới dạng tệp nén tại liên kết sau. Nếu nó đáp ứng được yêu cầu của bạn thì không cần tải lên tập tin Excel mà tôi đã yêu cầu nữa.

Tải xuống tập tin và giải nén bảng tính.

Tôi cho rằng các tháng được lưu trong các ô đã hợp nhất, và nếu vậy, tôi không cần định dạng của tháng. Tuy nhiên, nếu bạn đã sử dụng chức năng "Căn giữa trong phạm vi đã chọn" thì nó sẽ không hoạt động, vì vậy hãy liên hệ lại với tôi.

https://1drv.ms/u/s!ArAXPS2RpafCtS4vLms3_1Xd2kBy?e=jn2Q2d

Mã này nằm trong mô-đun ThisWorkbook.

Kiểm tra thao tác lưu bằng cách xóa một số nội dung trong cột "Đã đưa vào sử dụng trở lại".

Tôi đã cung cấp tùy chọn tiếp tục lưu trong trường hợp người dùng không thể nhập một số dữ liệu vì bất kỳ lý do nào nhưng đã nhập các dữ liệu khác. Nếu bạn không cung cấp tùy chọn này cho người dùng, bạn sẽ gặp vấn đề với người dùng, họ có thể chỉ đơn giản là đóng Excel bằng tổ hợp phím Ctrl/Alt/Delete và có thể làm hỏng bảng tính.

P.S. Bổ sung sau bài đăng ban đầu. Tôi chưa xử lý bất kỳ dữ liệu nào trong cột Tổng cộng. Tôi tin rằng chúng sẽ tự động được điền nếu các cột khác được điền chính xác. Tuy nhiên, nếu tôi cần xử lý chúng thì tôi cần biết các công thức, v.v. có trong cột Tổng cộng để tôi có thể quyết định cách xử lý.

Tôi cũng vừa nhận ra rằng mình đã đặt tên tệp là "Validate Date Before Save". Đáng lẽ ra phải là "Data" nhưng điều đó sẽ không ảnh hưởng đến việc bạn thử nghiệm.

Hãy cho tôi biết kết quả thế nào và đừng ngần ngại liên lạc lại với tôi.

Trả lời:
Tôi đã thử tệp bảng tính bạn đính kèm nhưng không được. Vì vậy, đây là liên kết đến tệp ví dụ mà tôi đã tạo, có công thức và bố cục giống hệt với tệp gốc mà tôi đang sử dụng.

https://1drv.ms/u/s!Apiu-MZbJNXEhkcq7oCqEADUqojw?e=ptVsrF

Tôi chỉ muốn bảng tính hoạt động theo cách mà khi tháng Giêng kết thúc và đến lúc chuyển sang tháng Hai (và cứ thế tiếp tục), quy tắc tương tự vẫn được áp dụng - nếu có một con số trong cột khu vực, tài liệu không thể được lưu trừ khi có giá trị tương ứng là Có/Không trong cột "đã đưa vào sử dụng". Nếu điều đó khả thi.

Tôi rất cảm kích sự giúp đỡ của bạn.

Trả lời:
Đoạn mã trước của tôi sẽ không hoạt động được vì tên tiêu đề cột đã bị thay đổi, nhưng giờ tôi đã chỉnh sửa lại mã để phù hợp với các tiêu đề cột.

Tải xuống tệp tin nén (xem liên kết bên dưới), giải nén và kiểm tra.

Tôi đã loại bỏ tùy chọn Lưu nếu dữ liệu "Đã đưa vào sử dụng lại (Có/Không)" chưa được điền đầy đủ vì người dùng có thể nhập Có hoặc Không và hệ thống sẽ lưu lại, hoặc nếu nhập thì xóa dữ liệu Vùng và bỏ qua dữ liệu "Đã đưa vào sử dụng lại (Có/Không)" bị thiếu.

Ở đầu đoạn mã có 2 hằng số mà bạn có thể chỉnh sửa nếu thay đổi tên tiêu đề cột. Phương pháp này được sử dụng để bạn không phải thay đổi mã ở nhiều nơi nếu tiêu đề cột bị thay đổi.

Đoạn mã dựa vào khả năng xác định các nhóm ô đã được hợp nhất trong hàng 1. Do đó, điều cần thiết là không thêm bất kỳ nhóm ô đã hợp nhất nào vào hàng 1 mà không cần được đưa vào quá trình kiểm tra tính hợp lệ. Có thể sử dụng chức năng "Căn giữa vùng chọn" thay cho các ô đã hợp nhất và đạt được kết quả tương tự mà không bị mã VBA nhận dạng là các ô đã hợp nhất.

Như tôi đã nêu trong các bài đăng trước, tôi không nghĩ rằng cần phải xác thực tổng cộng vì chúng sẽ tự động được điền nếu tất cả các dữ liệu khác được điền đầy đủ. Tuy nhiên, tốt hơn hết là không nên sử dụng "Ghép và Căn giữa" ở hàng 1 của tiêu đề cột "Tổng cộng" mà có thể sử dụng "Căn giữa theo vùng chọn" thay thế.

Tôi không chắc về chuyên môn của bạn trong lĩnh vực này, vì vậy đây là hướng dẫn sử dụng "Tập trung vào vùng chọn" trong trường hợp cần thiết.

1. Nhập "Tổng cộng" vào ô AQ1 (Có thể chỉnh sửa "Tổng cộng" thành tên tiêu đề mong muốn)

2. Chọn phạm vi AQ1:AV1 (Chỉnh sửa số cột cần thiết cho Tổng cộng)

3. Nhấp chuột phải vào vùng chọn

4. Chọn Định dạng ô

5. Chọn tab Căn chỉnh

6. Nhấp vào mũi tên thả xuống trong trường bên dưới Horizontal

7. Chọn "Căn giữa theo vùng chọn"

8. Nhấp OK

Đoạn mã trên hiển thị tiêu đề giống như các ô đã được hợp nhất và căn giữa, nhưng phạm vi ô không được xác định là ô đã hợp nhất, do đó nó sẽ KHÔNG được bao gồm trong phạm vi cần thiết để xác thực trong mã VBA.

Để bỏ chọn "Căn giữa vùng chọn", hãy lặp lại quy trình trên và chọn "Chung" cho trường Ngang hoặc chọn biểu tượng Xóa trong khối Chỉnh sửa ở phía ngoài cùng bên phải của dải băng Trang chủ, sau đó chọn Xóa tất cả hoặc Xóa định dạng.

Tệp bảng tính mẫu đã được tải lên tại liên kết sau, hãy kiểm tra xem nó có đáp ứng được yêu cầu của bạn hay không.

https://1drv.ms/u/s!ArAXPS2RpafCtTCr1S77D5y9LsMa?e=cMF2pd

Một lần nữa, xin đừng ngần ngại liên hệ lại với tôi nếu có bất kỳ vấn đề/thắc mắc nào.

Trả lời:
Tôi xin lỗi vì đã trả lời bạn muộn. Tôi nghĩ tôi hiểu ý bạn; nếu tôi hiểu sai, xin hãy sửa giúp tôi. Tôi cần thay đổi định dạng ở hàng 1 thành "căn giữa theo vùng chọn", nếu không mã sẽ không hoạt động. Và tôi hiểu ý bạn về việc có thể bỏ qua thông báo lỗi nếu cột "đã đưa vào sử dụng" để trống, nhưng tôi vẫn muốn thông báo lỗi đó hiện lên, nếu có thể.

Sự giúp đỡ của bạn vô cùng to lớn và tôi rất biết ơn!