代码之家 › 专栏 › 技术社区 › Michael O' Driscoll

如何限制用户在单个列中仅输入“Y”或“N”

excel-2016 excel vba

Michael O' Driscoll · 技术社区 · 7 年前

我想限制A列和D列中的用户输入。

在A列中,用户应输入以下值: R00yyyyyy 其中yyyyy是介于000000和999999之间的数字。

y 或 n .

我下面的代码似乎工作不正常。A部分的列工作正常,只是D列有问题。

有人能提出一种方法来限制D列中的条目吗?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rngCell As Range

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each rngCell In Target.Cells
    rngCell = UCase(rngCell)
    If rngCell.Characters.Count > 9 Then
    MsgBox ("Student number too long")
    rngCell.Clear
    End If
   If Not IsEmpty(rngCell.Value) Then
    Dim s As String
    Dim s1 As String
    Dim y As String
    Dim y1 As String
    s = CStr(rngCell.Value)
    s1 = Left(s, 3)
    y = CStr(rngCell.Value)
    y1 = Right(y, 6)
    If s1 <> "R00" Or Not IsNumeric(y1) Then
    MsgBox ("Must be in the form R00yyyyyy, where yyyyyy is a number between 000000 and 999999")
    rngCell.Clear
    End If
   Else
   End If
Next
Application.EnableEvents = True

Dim rngCell2 As Range

If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each rngCell2 In Target.Cells
    rngCell2 = UCase(rngCell2)
    Dim b As String
    b = CStr(rngCell2.Value)
    If b <> "y" Or b <> "n" Then
    MsgBox ("The only allowable entry here is Y or N")
    End If
Next
Application.EnableEvents = True

End Sub

3 回复 | 直到 6 年前

Jiminy Cricket 7 年前

假设其余代码正确,则需要将逻辑测试从或更改为和

If b <> "y" And b <> "n" Then
    MsgBox ("The only allowable entry here is Y or N")
End If

Harassed Dad 7 年前

 If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

因此,当他们更改D列中的任何内容时,它不在A中,代码在到达D的代码之前退出

Robin Mackenzie 7 年前

您可以尝试下面的代码。它检查更改是否在A列中或如果两列都没有更改,则列D和退出。

然后您知道更改在A列或D列中,并准确检查哪一列。然后进行相应的检查,并根据输入显示相应的消息框。

检查一个简单的模式,如 R00yyyyyy 哪里 y 是一个可以使用的数字 Like # 作为“任意数字”的占位符。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCell As Range

    ' exit if change was not in column A and not in column D
    If Intersect(Target, Range("A:A,D:D")) Is Nothing Then Exit Sub

    ' get first cell of change
    Set rngCell = Target.Cells(1, 1)

    ' disable events
    Application.EnableEvents = False

    ' now check if change in column A
    If rngCell.Column = 1 Then
        If Not rngCell.Value Like "R00######" Then
            MsgBox "Must be in the form R00yyyyyy, where yyyyyy is a number between 000000 and 999999"
            rngCell.Clear
        End If
    Else 'must have been in column D
        If rngCell.Value <> "y" And rngCell.Value <> "n" Then
            MsgBox "The only allowable entry here is Y or N"
            rngCell.Clear
        End If
    End If

    ' re-enable events
    Application.EnableEvents = True

End Sub