vba 如何使 Excel 2010 中的特定单元格成为必需
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8255222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Make a particular Cell Required in Excel 2010
提问by Developer
Hi all i will give an Excel Documentfor the user with some basic information as follows
大家好，我将为Excel Document用户提供一些基本信息，如下所示
Now if the user leaves a cell which is required i would like to prompt him a message saying that it is a required value with in the excel.
现在，如果用户离开一个必需的单元格，我想提示他一条消息，说它是 excel 中的必需值。
I have referred to some articles
我参考了一些文章
But i am unable to achieve what I required so can any one help. Also I would like to know is it possible to apply Regular expression validatorswith in the Excel. Something like Date formatshould be mm/dd/yyyyand SSNshould be 9 digitedlike that..
但是我无法实现我的要求，所以任何人都可以提供帮助。另外我想知道是否可以Regular expression validators在Excel. 喜欢的东西Date format应该是mm/dd/yyyy和SSN应该是9 digited这样..
I tried some thing like but this didn't prompt me any error or Dialog
我尝试了一些类似的事情，但这并没有提示我任何 error or Dialog
Private Sub Worksheet_BeforeSave(Cancel As Boolean)
If Sheet1.Range("A3:B3").Value = "" Then
Application.EnableEvents = True
MsgBox "Cannot print until required cells have been completed!"
Cancel = True
End If
End Sub
回答by brettdj
Part 1
第1部分
That code has a few issues
该代码有几个问题
- You need a
Workbookevent - there is no WorkSheet_BeforeSave event - You cant test for two blank cells with
Sheet1.Range("A3:B3").Value = "" - If the code is running then events are already enabled, so this line
Application.EnableEvents = Trueis redundant
- 您需要一个
Workbook事件 - 没有 WorkSheet_BeforeSave 事件 - 你不能测试两个空白单元格
Sheet1.Range("A3:B3").Value = "" - 如果代码正在运行，则事件已启用，因此此行
Application.EnableEvents = True是多余的
Something like this test for both A3 and B3 being non blank on the leftmost sheet
像这样的测试 A3 和 B3 在最左边的纸上都是非空白的
{code goes in the ThisWorkbookmodule}
{代码进入ThisWorkbook模块}
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.WorksheetFunction.CountA(Sheets(1).Range("a3:b3")) <> 2 Then
MsgBox "Cannot print until required cells have been completed!"
Cancel = True
End If
End Sub
While you can use a Regex for part 2, plain data validation should work fine,ie
虽然您可以在第 2 部分使用正则表达式，但纯数据验证应该可以正常工作，即
a) You can use an "allow" Date in Data Validation
a) 您可以在数据验证中使用“允许”日期
b) You could use a Whole Number between 100,000,000 and 999,999,999 for a 9 digit number
b) 您可以使用 100,000,000 和 999,999,999 之间的整数作为 9 位数字
回答by Jean-Fran?ois Corbett
Answer to Part 2 (Regular Expression thing)
第 2 部分的答案（正则表达式）
You can write a user-defined function in VBA and use it in Excel. Here's an examplethat extracts parts of a string using RegEx, but with a bit of tweaking you can use it to do validation.
您可以在 VBA 中编写用户定义的函数并在 Excel 中使用它。这是一个使用 RegEx 提取部分字符串的示例，但稍加调整，您就可以使用它进行验证。
