VBA复选标记 - 如果双击并删除，则返回'Yes'

Question

我使用以下VBA代码在双击单元格时显示复选标记/刻度。有时，需要删除复选标记/勾号。要删除复选标记/勾号，需要再次双击该单元格。双击以删除复选标记/勾号后，单元格将返回为空白。我的问题是;是否可以显示单词"是"而不是将单元格留空？

或者作为一个更好的选择，是否可以在双击时返回单元格中的原始文本(甚至双击单元格之前((以删除复选标记/勾号(而不是返回为空白？

我希望这是有道理的！提前谢谢你！

以下是我用于复选标记/刻度的VBA代码：

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo 1
    If Not Intersect(Target, Range("A2:Z200")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = ChrW(&H2713) Then
            Target.ClearContents
            Cancel = True
        Else
            Target.Value = ChrW(&H2713)
            Cancel = True
        End If
    End If
    On Error GoTo 0
1   Application.EnableEvents = True
End Sub

Answer 1

要显示单词"YES"，请更改行：

Target.ClearContents

跟：

Target.Value = "YES"

完整代码

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo 1
    If Not Intersect(Target, Range("A2:Z200")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = ChrW(&H2713) Then
            Target.Value = "YES"
        Else
            Target.Value = ChrW(&H2713)
        End If
        Cancel = True
    End If
    On Error GoTo 0
1   Application.EnableEvents = True
End Sub

Answer 2

这将用复选标记替换原始值，然后在重新单击时恢复原始值，但它仅适用于文本值，并且仅更改单元格的外观...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A2:Z200")) Is Nothing Then
        On Error GoTo haveError
        Application.EnableEvents = False
        If Target.NumberFormat Like ";;;*" Then
            Target.NumberFormat = "General"
        Else
            Target.NumberFormat = ";;;" & ChrW(&H2713)
        End If
        Cancel = True
    End If
haveError:
    Application.EnableEvents = True
End Sub