vba Excel：输入数据验证只允许数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20844055/
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
Excel: input data validation to allow only numbers
提问by Uder Moreira
Is there any way to not allow users enter alpha characters in a cell that I need only numbers?
有什么方法可以不允许用户在我只需要数字的单元格中输入字母字符？
I would greatly appreciate any help.
我将不胜感激任何帮助。
回答by
As already mentioned it can be achieved by Data Validation... but there is a catch.
正如已经提到的，它可以通过数据验证来实现......但有一个问题。
First method: Data validation
第一种方法：数据验证
- ALT+D+L
- Allow : Whole Number
- Data : greater than (select what you want)
- Minimum : 0
- ALT+D+L
- 允许：整数
- 数据：大于（选择你想要的）
- 最低：0
CATCH :But Data Validation doesn't check the value if it is copied from some other cell. That is, if I copy alpha value from a different cell & paste / paste special (value) it over the cell, it will accept it. It will not restrict me.
CATCH ：但如果值是从其他单元格复制的，则数据验证不会检查该值。也就是说，如果我从不同的单元格复制 alpha 值并在单元格上粘贴/粘贴特殊（值），它会接受它。它不会限制我。
Hence Second Method: VBA
因此第二种方法：VBA
Private Sub Worksheet_Change(ByVal Target As Range)
Const CELL_ADDRESS = "$A" 'change cell
If Target.Address = CELL_ADDRESS Then
If Not IsNumeric(Target.Value) Then
MsgBox "Wrong value", vbCritical, "666bytes"
Target.Value = vbNullString
End If
End If
End Sub
Edit: For a range :
编辑：对于范围：
Private Sub Worksheet_Change(ByVal Target As Range)
Const CELL_ADDRESS = "$D:$E00" 'change range
If Not Application.Intersect(Target, Range(CELL_ADDRESS)) Is Nothing Then
If Not IsNumeric(Target.Value) Then
MsgBox "Wrong value", vbCritical, "666bytes"
Target.Value = vbNullString
End If
End If
End Sub
Insert this VBA code in the sheet module where you want to implement this and change CELL_ADDRESS. If you want you can do a lot of stuff using the Worksheet_Change event, so please play as you like with this code. Hope this helps! :)
将此 VBA 代码插入到要实现此功能并更改 CELL_ADDRESS 的工作表模块中。如果您愿意，您可以使用 Worksheet_Change 事件做很多事情，所以请随意使用此代码。希望这可以帮助！:)
回答by Phillip Wong
Assuming input cell is A1
假设输入单元格是 A1
In the Data Validation menu
在数据验证菜单中
Allow: Custom
允许：自定义
Formula: =isnumber(A1)
公式：=isnumber(A1)
