vba Excel 2007 宏验证输入到单元格中的数据，如果不正确则显示 msgbox
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14627816/
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
excell 2007 macro validate data entered into cell and show msgbox if incorrect
提问by user1955214
Please can someone help with the following code. it gives me an error at the following line:
请有人帮忙提供以下代码。它在以下行给我一个错误：
Set range = "C5:L14"
This is the complete code:
这是完整的代码：
Private Sub Worksheet_Change(ByVal Target As Excel.range)
Dim ws As Worksheet
Dim range As Worksheet
Set ws = Application.ActiveSheet
Set range = "C5:L14"
If Not Application.Intersect(Target, range("C5:L14")) Is Nothing Then
If range("C5:L14").Value = "" Then Exit Sub
If range("C5:L14").Date = "< today()" Then Exit Sub
If range("C5:L14").Date = "> today()" Then MsgBox ("Future dates not allowed!")
Else
MsgBox ("Please enter date as follows yyyy-mm")
End If
End Sub
The date is formatted to "2013 Jan" on the cells. Future dates are not allowed and the user should only type in the date as "2013-01". The format should change it correctly. If they type in "2013 Jan" the Conditional formatting does not pick it up. Have tried DATA VALIDATION but it only limits me to one.
单元格上的日期格式为“2013 Jan”。不允许将来的日期，用户只能输入“2013-01”作为日期。格式应该正确更改它。如果他们输入“2013 Jan”，则条件格式不会选择它。已经尝试过数据验证，但它只将我限制为一个。
I need the macro to make sure a user doesn't enter an incorrect date in the cells specified.
我需要宏来确保用户不会在指定的单元格中输入错误的日期。
回答by Siddharth Rout
What you are trying can be solved without VBA as well. However I am showing you both the methods. Take your pick
没有 VBA 也可以解决您正在尝试的问题。但是，我向您展示了这两种方法。随你挑
NON VBA
非 VBA
Select the cell where you want to apply Data Validation and then follow these steps.
选择要应用数据验证的单元格，然后按照以下步骤操作。
Step 1
第1步
Step 2
第2步
Step 3
第 3 步
Step 4
第四步
In Action
在行动
VBA
VBA
I have commented the code so you will not have any problem in understanding it
我已经对代码进行了注释，因此您在理解它时不会有任何问题
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Dim aCell As Range
'~~> The below two lines are required. Read up more on
'~~> http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640
On Error GoTo Whoa
Application.EnableEvents = False
'~~> Set your range
Set rng = Range("C5:L14")
If Not Application.Intersect(Target, rng) Is Nothing Then
'~~> Loop through all cells in the range
For Each aCell In rng
If aCell.Value <> "" Then
If aCell.Value > Date Then
aCell.ClearContents
MsgBox "Future date not allowed in cell " & aCell.Address
ElseIf IsDate(aCell.Value) = False Then
aCell.ClearContents
MsgBox "Incorrect date in cell " & aCell.Address
Else
aCell.Value = Format(aCell.Value, "yyyy-mm")
End If
End If
Next
End If
Letscontinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume Letscontinue
End Sub
Hope this helps?
希望这可以帮助？
EDIT:
编辑：
A slight change. In the Step 4 of the Non VBA Method, I typed "yyyy mm" by mistake. Change that to "yyyy-mm"
一点点变化。在非 VBA 方法的第 4 步中，我错误地输入了“yyyy mm”。将其更改为“yyyy-mm”
