vba 限制 Excel 单元格输入值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25416176/
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
Restrict Excel Cell Input Value
提问by Keyur Shah
I have Excel Sheet. I have locked and Protect other cells. I have 2 cells that required user input and both are currency Datatype. If I input text in those cells it messes up calculation so I would like to format those cells in a way that if anyone type text or sentence it will give error and do not affect calculation and ask for number input.
我有 Excel 表格。我已锁定并保护其他单元格。我有 2 个需要用户输入的单元格，并且都是货币数据类型。如果我在这些单元格中输入文本，它会扰乱计算，所以我想以一种方式格式化这些单元格，如果有人输入文本或句子，它会给出错误并且不影响计算并要求输入数字。
I am new to Excel programming so it would be hard for me first time.
我是 Excel 编程的新手，所以第一次对我来说很难。
回答by cyboashu
Tip : Data Validation is a very weak control mechanism. When you copy and paste a value in a cell, all data validations are by passed.
提示：数据验证是一种非常弱的控制机制。当您在单元格中复制和粘贴值时，所有数据验证都会通过。
For the sake of a solution, lets assume that the currency cells are A1 and B1 on sheet1.
为了解决问题，我们假设货币单元格是 sheet1 上的 A1 和 B1。
Goto the code of sheet1 in VBE and write a macro, something like this
转到VBE中sheet1的代码，写一个宏，像这样
Dim lOldVal As Long 'Sheet Module level variable
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A" Or Target.Address = "$B" Then
If Not IsNumeric(Target.Value) Then
MsgBox "Only numeric values allowed.", vbInformation, "Foo"
Application.EnableEvents = False
Target.Value = lOldVal
Application.EnableEvents = True
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A" Or Target.Address = "$B" Then
lOldVal = Target.Value
End If
End Sub
change $A$1 and $B$1 with your actual cell address.
使用您的实际单元格地址更改 $A$1 和 $B$1。
回答by PhpLou
I am not an excel programmer, however I have found this a few weeks ago while looking at an excel file
You have to go to datatab then Data Validationthen you put your criteria
You can even put an input message and an error message
我不是 excel 程序员，但是几周前我在查看 excel 文件时发现了这一点
您必须转到data选项卡，然后
输入Data Validation您的条件
您甚至可以输入输入消息和错误消息
