Doubleclick Yes, No and Blank - VBA

[Garri]

Hello everyone,

I found a Code for VBA to use double click in certain cell to activate Yes and No. However, I cannot to add to that code that it also show blank (or returns to the default result aka do not show anything).

Can anyone help me please?

P.s I am newbie.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address <> "C1" Then Exit Sub
Cancel = True
With Target
If .Value = "Yes" Then
.Value = "No"
Else
.Value = "Yes"
End If
End With
End Sub

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Address(0, 0) <> "C1" Then Exit Sub
      Cancel = True
      With Target
      If .Value = "Yes" Then
         .Value = "No"
      ElseIf .Value = "No" Then
         .Value = ""
      Else
         .Value = "Yes"
      End If
   End With
End Sub

 

[Eric W]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Address <> "$C$1" Then Exit Sub
    Cancel = True
    
    Select Case Target.Value
        Case "Yes": Target.Value = "No"
        Case "No": Target.Value = ""
        Case Else: Target.Value = "Yes"
    End Select
    
End Sub

 

[Garri]

Dear Fluff and Eric W,

Thank you so much!!!

Best regards,
Garri

 

[Eric W]

Glad we could help.

 

[Rick Rothstein]

I'm late to the thread (as usual), but here is one more way to do this...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Address(0, 0) = "C1" Then
     Cancel = True
     Target.Value = Choose(1 + InStr(" YN", Left(Target.Value, 1)), "", "Yes", "No")
   End If
End Sub