vba 如何限制单元格只能输入数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11133771/
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 restrict a cell to input only numbers
提问by user1049518
I have an excel file, validating one column to enter numbers only but it is formatted as if the number is less than 18 the leading zeros will be added. But now the number after 15th digit it will be converting to 0 ex: "002130021300020789", the nine is changed as 0. After converting the column to text, its accepting but i am not able to add the leading zeros and not able to restrict to input onlu numbers.
我有一个 excel 文件，验证一列仅输入数字，但它的格式设置为数字小于 18，将添加前导零。但是现在第 15 位之后的数字将转换为 0 例如：“002130021300020789”，九位更改为 0。将列转换为文本后，它接受但我无法添加前导零并且无法限制输入onlu数字。
Appreciate any help.. thanks in advance.
感谢任何帮助.. 提前致谢。
采纳答案by Siddharth Rout
MS Article: Excel follows the IEEE 754 specificationon how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.
MS 文章：Excel 遵循有关如何存储和计算浮点数的IEEE 754 规范。因此，Excel 仅在数字中存储 15 位有效数字，并将第 15 位之后的数字更改为零。
To get your number formatting and to also ensure that the user enters only numbers you can do this. I am assuming that you are validating the text in Range A1. Please amend as applicable.
要获得您的数字格式并确保用户只输入数字，您可以这样做。我假设您正在验证范围 A1 中的文本。请酌情修改。
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
Application.EnableEvents = False
If Not Intersect(Target, Range("A1")) Is Nothing Then
'~~> If entered text is not a number then erase input
If Not IsNumeric(Range("A1").Value) Then
MsgBox "invalid Input"
Application.Undo
GoTo LetsContinue
End If
Range("A1").Value = "'" & Format(Range("A1").Value, "000000000000000000")
End If
LetsContinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
FOLLOWUP
跟进
If you are copying and pasting then you will have to first format the Range G11:G65536 manually as TEXTand then use this code
如果您要复制和粘贴，则必须首先手动将范围 G11:G65536 格式化为 TEXT，然后使用此代码
SNAPSHOT (When Pasting Numeric Values)
SNAPSHOT（粘贴数值时）
SNAPSHOT (When Pasting Non Numeric Values)
SNAPSHOT（粘贴非数字值时）
CODE
代码
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
Dim cl As Range
Application.EnableEvents = False
If Not Intersect(Target, Range("G11:G" & Rows.Count)) Is Nothing Then
For Each cl In Target.Cells
'~~> If entered text is not a number then erase input
If Not IsNumeric(cl.Value) Then
MsgBox "invalid Input"
Application.Undo
GoTo LetsContinue
End If
cl.Value = "'" & Format(cl.Value, "000000000000000000")
Next
End If
LetsContinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
回答by AurA
First you can change the format of the cell to text
首先，您可以将单元格的格式更改为文本
Range("A1").NumberFormat = "@"
Then you can add zeroes
然后你可以添加零
cellvalue2 = Format(cellvalue1, "000000000000000") // for numeric fields
or
= TEXT(cellvalue1,"000000000000000") // for textfields
