请有人帮忙编写以下代码。它在以下行给我一个错误:
Set range = "C5:L14"
这是完整的代码:
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
单元格上的日期格式为“2013 年 1 月”。不允许未来日期,用户只能输入“2013-01”日期。格式应该正确更改。如果他们输入“2013 Jan”,条件格式不会选择它。已尝试过数据验证,但它仅将我限制为一种。
我需要宏来确保用户不会在指定的单元格中输入错误的日期。
您所尝试的问题也可以在没有 VBA 的情况下解决。不过,我向您展示了这两种方法。随你挑选
NON VBA
选择要应用数据验证的单元格,然后按照以下步骤操作。
Step 1
Step 2
Step 3
Step 4
行动中
VBA
我已经对代码进行了注释,因此您在理解它时不会有任何问题
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
希望这可以帮助?
EDIT:
略有变化。在非VBA方法的第4步中,我错误地输入了“yyyy mm”。将其更改为“yyyy-mm”
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)