vba 数据验证宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27734054/
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
Data validation macro
提问by Juuri Peeter
I'm creating an Excel sheet which different people are going to add to, so am wondering if there's any simple way to check for the row where user starts writing being filled?
我正在创建一个 Excel 表，不同的人要添加到其中，所以想知道是否有任何简单的方法来检查用户开始写入的行是否被填充？
For example, if user starts typing in cell A1, macro checks if the cells are filled on the same row.
例如，如果用户开始在单元格 A1 中键入，宏会检查单元格是否在同一行中填充。
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rsave As Range
Dim cell As Range
Set rsave = Sheet1.Range("a1:i1")
For Each cell In rsave
If cell = "" Then
Dim missdata
missdata = MsgBox("missing data", vbOKOnly, "Missing Data")
Cancel = True
cell.Select
Exit For
End If
Next cell
End Sub
回答by george
to expand on the suggested solution, you can do the following. Instead of looping through each cell, your problem can be solved efficiently with two lines of code:
要扩展建议的解决方案，您可以执行以下操作。无需遍历每个单元格，您的问题可以通过两行代码有效解决：
'get the used range
Set rsave = Sheet1.Range("a1:i1")
'Select all blank(not yet filled) cells
rsave.SpecialCells(xlCellTypeBlanks).Select
This will select all cells which've not been filled in the range a1:i1 of the sheet. Alternatively, you can use some colour to make it more explicit. If it works, don't forget to accept the answer.
这将选择在工作表的 a1:i1 范围内尚未填充的所有单元格。或者，您可以使用一些颜色使其更加明确。如果有效，请不要忘记接受答案。
回答by Maciej Los
If by saying "data validation", you mean check for blanks, you can use this:
如果说“数据验证”是指检查空格，则可以使用以下命令：
Sub Test()
Dim wrng As Range
Set wrng = ActiveSheet.UsedRange
MsgBox "The data in a range: '" & wrng.Address & "' are" & IIf(IsValidData(wrng), "", "n't") & " valid"
Set wrng = Nothing
End Sub
Function IsValidData(rng As Range) As Boolean
IsValidData = rng.SpecialCells(xlCellTypeBlanks).Count = 0
End Function
Note, that the UsedRangemethod returns a range starting from A1cell. So, you need to add extra code to select a range occupied by the data (skip blanks rows and columns).
请注意，UsedRange方法返回从A1单元格开始的范围。因此，您需要添加额外的代码来选择数据占用的范围（跳过空白行和列）。
Sub Test()
Dim wrng As Range
Set wrng = GetDataRange()
MsgBox "The data in a range: '" & wrng.Address & "' are" & IIf(IsValidData(wrng), "", "n't") & " valid"
End Sub
Function GetDataRange() As Range
Dim wrng As Range, c As Range, saddr As String
Dim pos As Integer
'get used range
Set wrng = ActiveSheet.UsedRange
'find first non-empty cell in a used range
saddr = ActiveSheet.Range(wrng.End(xlToLeft).Address, wrng.End(xlUp).Address).Address
pos = InStr(1, saddr, ":")
'skip blanks rows and set new range
Set GetDataRange = ActiveSheet.Range(Mid(saddr, pos + 1, Len(saddr) - pos) & ":" & wrng.SpecialCells(xlCellTypeLastCell).Address)
Set wrng = Nothing
End Function
Good luck!
祝你好运！
