Question

我有一份清单,每个任务都有一行。我愿把一个囚室与任务放在同一个牢房里,在观众把双筒浮到一个相邻的囚室时记录一个时段。这将显示任务完成或结束的日期。

用户可以通过“a” = 接受、“r” = 拒绝或“p” = 待决的条目,将盒子-细胞进行双立。

当我接受“a”时,我想在同一行的E栏出现一个时段。

C栏=“a”、“r”或“p”,目标时标为同一行的E栏,N。

我没有发现任何错误,但小组中没有任何错误(E,n)。

日期格式应为MM/DD/Y。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("Selection")) Is Nothing Then Exit Sub
    If Target.Value < "a" Then
        Target.Value = "a"  Sets target Value = "a"
        n = Target.Row
        If Excel.Range("E" & n).Value <> "" Then
        Excel.Range("E" & n).Value = Now
        End If
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.Value = "r"
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "r" Then
        Target.Value = "p"
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "p" Then
        Target.Value = "a"
        Cancel = True
        Exit Sub
    End If
    End Sub

Answer 1

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If .Column = 3 And .Row > 1 Then
            If Len(.Value) = 0 Then   if status is blank, populated "a"
                .Value = "a"
            Else
                  populate next status
                Dim aStatus, vVal: aStatus = Split("a r p a")
                vVal = Application.Match(.Value, aStatus, 0)
                If Not IsError(vVal) Then
                    .Value = aStatus(vVal)
                End If
            End If
            If .Value = "a" Then   populated timestamp
                .Offset(, 2).Value = Date
                .Offset(, 2).NumberFormat = "MMM/dd/yy"
            Else   clear timestamp if status = "r" or "p"
                 .Offset(, 2).Value = ""
            End If
            Cancel = True
        End If
    End With
End Sub

友情链接