英文:
InputBox Date Validation
问题
在代码中,您遇到了一个问题,当输入无效日期时,它会导致运行时错误 13 类型不匹配。问题在于您的日期验证逻辑,以下是已翻译的部分代码:
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
iRow = ActiveCell.Row
lastRow = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow '隔离记录,获取数据并移动
wo = Cells(i, 1).Value
pn = Cells(i, 2).Value
sn = Cells(i, 3).Value
n = Cells(i, 6).Value
If Me.txt_WN.Value = wo Then
If Me.txt_pn.Value = pn Then
If Me.txt_sn.Value = sn Then
If n = "Yes" Then
dd = MsgBox("这是正确的交货日期吗? " & Curr, vbYesNo)
If dd = vbYes Then
dd = Curr
Else
Line1: '问题出现在这里
Dim dateInput As String
dateInput = InputBox("输入正确的交货日期", "交货日期")
If IsDate(dateInput) Then
dd = Format(dateInput, "mm/dd/yyyy")
Else
MsgBox "日期格式不正确,请重新检查输入"
GoTo Line1
End If
End If
GoTo Update
Else
MsgBox "此轮胎 S/N 未标记为需要 NDT"
Exit Sub
End If
End If
End If
End If
Next i
在这段代码中,我对日期输入进行了一些修改,以确保它在用户输入无效日期时能够处理错误。主要的更改是将日期输入存储在变量 dateInput
中,然后使用 IsDate
函数来验证日期的有效性。如果日期无效,它将显示消息框,要求用户重新输入。这应该解决您遇到的问题。
英文:
I have an update form. When a button is clicked, a Yes or No message box pops up asking the user to verify the delivery date.
Upon clicking No, an InputBox appears to enter the appropriate date.
I am attempting to verify that the user entered a valid date. In the code this function begins at "Line1".
The problem occurs with the Else statement below "Line1".
If I enter a proper date, the code skips to the Msgbox and then returns me to "Line1".
If I enter a string of alpha characters, instead of the Message box prompting me to correct the date, I get
>Runtime Error 13 Type Mismatch
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
iRow = ActiveCell.Row
lastRow = ws3.Cells(ws3.Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow 'Isolate the Record, Get data, and move
wo = Cells(i, 1).Value
pn = Cells(i, 2).Value
sn = Cells(i, 3).Value
n = Cells(i, 6).Value
If Me.txt_WN.Value = wo Then
If Me.txt_pn.Value = pn Then
If Me.txt_sn.Value = sn Then
If n = "Yes" Then
dd = MsgBox("Is this the correct delivery date? " & Curr, vbYesNo)
If dd = vbYes Then
dd = Curr
Else
Line1: 'Problem exists here
If IsDate(dd = InputBox("Enter the Correct Delivery Date", "Deliver to Stores Date")) Then
dd = Format(dd, "mm/dd/yyyy")
Else
MsgBox "The date is formatted incorrectly, please recheck entry"
GoTo Line1
End If
'If IsDate(dd) Then
' dd = Format(dd, "mm/dd/yyyy")
'Else
' MsgBox "The date is formatted incorrectly, please correct"
' GoTo Line1
'End If
End If
GoTo Update
Else
MsgBox "This Wheel S/N was not marked as Due for NDT"
Exit Sub
End If
End If
End If
End If
Next i
Below the first If Statement after "Line1" a second if statement that has been commented out. In that instance, if I clicked the No button on the message box, it would enter "1/7/1900", and esentially bypass the code for validation (I think).
I used similar validation code in other subroutines and it worked.
答案1
得分: 0
通过使用工作表引用来限定Cells()
,可以避免潜在问题,否则它将默认使用活动工作表。
With ws3
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 3 To lastrow '隔离记录,获取数据并移动
wo = .Cells(i, 1).Value
pn = .Cells(i, 2).Value
sn = .Cells(i, 3).Value
n = .Cells(i, 6).Value
If Me.txt_WN.Value = wo _
And Me.txt_pn.Value = pn _
And Me.txt_sn.Value = sn Then
If n = "Yes" Then
If vbYes = MsgBox("这是正确的交付日期吗? " & curr, vbYesNo) Then
dd = curr
Else
Line1: dd = InputBox("输入正确的交付日期", "Deliver to Stores Date")
If Len(dd) = 0 Then ' 取消
Exit Sub
ElseIf IsDate(dd) Then
dd = Format(dd, "mm/dd/yyyy")
Else
MsgBox "'" & dd & "' 不是有效的日期,请重新检查输入", vbExclamation
GoTo Line1
End If
End If
GoTo update
Else
MsgBox "这个轮子S/N " & sn & " 没有标记为需要进行NDT检测", vbCritical
Exit Sub
End If
End If
Next i
End With
英文:
Avoid potential problems by qualifying the Cells()
with a worksheet reference otherwise it defaults to the active sheet.
With ws3
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 3 To lastrow 'Isolate the Record, Get data, and move
wo = .Cells(i, 1).Value
pn = .Cells(i, 2).Value
sn = .Cells(i, 3).Value
n = .Cells(i, 6).Value
If Me.txt_WN.Value = wo _
And Me.txt_pn.Value = pn _
And Me.txt_sn.Value = sn Then
If n = "Yes" Then
If vbYes = MsgBox("Is this the correct Delivery Date? " & curr, vbYesNo) Then
dd = curr
Else
Line1: dd = InputBox("Enter the correct Delivery Date", "Deliver to Stores Date")
If Len(dd) = 0 Then ' cancel
Exit Sub
ElseIf IsDate(dd) Then
dd = Format(dd, "mm/dd/yyyy")
Else
MsgBox "'" & dd & "' is not a valid date, please recheck entry", vbExclamation
GoTo Line1
End If
End If
GoTo update
Else
MsgBox "This Wheel S/N " & sn & " was not marked as due for NDT", vbCritical
Exit Sub
End If
End If
Next i
End With
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论