BeforeOneClick event in Excel VBA

Question

I am looking for something like:
Private Sub Worksheet_BeforeOneClick(ByVal Target As Range, Cancel As Boolean)

Whenever I click once with left button on "A1" cell I want to change its value from 'Yes' to 'No' and from 'No' to 'Yes' (depending on what value is before the click).

The problem is that there is no event in Excel like Worksheet_BeforeOneClick The events in Excel VBA are only for double clicking, right clicking, and selection change (for left button one click). But note that keep clicking once on A1 and I do not change the selection.

I can do this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'do my code here Range("A2").Select 'so I can again click on A1

Is there a better workaround?

Answer 1

Option 4, below, seems to be the best option to satisfy the requirements of the OP.

There is no "Worksheet_BeforeOneClick" event in Excel. However, you could create a label, place it over top of the cell in question and assign a macro to the Label_Click event that changes the caption of the label and/or the value of the cell:

OPTION 1

Step 1: Create an ActiveX label on the worksheet in question.

Step 2: Use the following temporary sub to place the label where you want it to be:

Sub PlaceLabel()
'Use this sub to place "Label1" where you want it. Change the Worksheet
'reference ("Sheet1") and the Range reference ("B2") as necessary.

With Label1
    .Left = Worksheets("Sheet1").Range("B2").Left + 1
    .Top = Worksheets("Sheet1").Range("B2").Top + 1
    .Height = Worksheets("Sheet1").Range("B2").Height - 1
    .Width = Worksheets("Sheet1").Range("B2").Width - 1
    .Caption = "Yes"
    .BackStyle = fmBackStyleTOpaque
End With

End Sub

Step 3: Add the following code to the worksheet in question:

Private Sub Label1_Click()
'Use this sub to change the caption of Label1 as necessary. If you rename
'the label, you will need to replace "Label1" both in the code and in the
'name of the sub with the new label name.

If Label1.Caption = "Yes" Then
    Label1.Caption = "No"
    Worksheets("Sheet1").Range("B2").Value = "No"
    GoTo EndOfSub
ElseIf Label1.Caption = "No" Then
    Label1.Caption = "Yes"
    Worksheets("Sheet1").Range("B2").Value = "Yes"
End If

EndOfSub:

End Sub

As noted in the commented portions of the subs, if the label is renamed, "Label1" will need to be replaced both in the code and in the name of the sub.

This will allow you to reference either the cell's value or the label's caption.

OPTION 2 - Shows cell formatting (if required)

Step 1: Create an ActiveX label on the worksheet in question.

Step 2: Use the following temporary sub to place the label where you want it to be:

Sub PlaceLabel()
'Use this sub to place "Label1" where you want it. Change the Worksheet
'reference ("Sheet1") and the Range reference ("B2") as necessary.

With Label1
    .Left = Worksheets("Sheet1").Range("B2").Left + 1
    .Top = Worksheets("Sheet1").Range("B2").Top + 1
    .Height = Worksheets("Sheet1").Range("B2").Height - 1
    .Width = Worksheets("Sheet1").Range("B2").Width - 1
    .Caption = ""
    .BackStyle = fmBackStyleTransparent
End With

End Sub

Step 3: Add the following code to the worksheet in question:

Private Sub Label1_Click()
'Use this sub to change the value of the cell in question as necessary.
'Rename the Worksheet and Range as necessary.

With Worksheets("Sheet1").Range("B2")
    If .Value = "Yes" Then
        .Value = "No"
        GoTo EndOfSub
    ElseIf .Value = "No" Then
        .Value = "Yes"
    End If
EndOfSub:
End With

End Sub

This option uses a transparent label so that the cell's format will be seen instead of the label. However, you will need to move your cursor off of the label for the change to show.

OPTION 3

You mentioned right clicking in your original post, but didn't say why you wouldn't use that option. Here's an example of how to use the right click event to achieve what you want without opening the right-click menu:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
'This sub changes the value of cell "B2" from No to Yes or from Yes to No as necessary
'on right click. It also disables the right-click menu for that cell only if the value
'of the cell is either Yes or No.

If Target.Column = 2 And Target.Row = 2 Then
    If Target.Value = "No" Then
        Target.Value = "Yes"
        Cancel = True
    ElseIf Target.Value = "Yes" Then
        Target.Value = "No"
        Cancel = True
    End If
End If

End Sub

You could then add an input message under Data Validation to instruct the user to right-click:

OPTION 4

There is no way to set your selection to Nothing using VBA, but there is a workaround that you could use.

Step 1: Create an ActiveX label on the worksheet in question and place it in the top left corner of the worksheet. (It could be placed anywhere, but if you put it in the top left, it will be easier to find if necessary)

Step 2: Set the following properties of the label by either right-clicking on it, or clicking on Properties under the Developer tab while the label is selected:

(Name)        ReadyLabel
BackStyle     0 - fmBackStyleTransparent
Caption                                     '(Set to empty)
Enabled       False
PrintObject   False
Visible       False

Step 3: Add the following code to the worksheet in question:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 And Target.Row = 4 Then
    Application.ScreenUpdating = False
    If Target.Value = "No" Then
        Target.Value = "Yes"
        ActiveSheet.Range("A1").Select
        ReadyLabel.Visible = True
        ReadyLabel.Select
        ReadyLabel.Visible = False
        Application.ScreenUpdating = True
    ElseIf Target.Value = "Yes" Then
        Target.Value = "No"
        ActiveSheet.Range("A1").Select
        ReadyLabel.Visible = True
        ReadyLabel.Select
        ReadyLabel.Visible = False
        Application.ScreenUpdating = True
    End If
End If

End Sub

This option will allow you to click once on the cell in question (the example uses cell "D4") to change the value from "Yes" to "No". It also allows you to click as many times as you want to change the value without having to right click or click somewhere else, and it doesn't show another cell as activated. Hopefully this satisfies your requirements.

If your "Yes/No" cell is in cell "A1", the code will need to be modified slightly to work. Also, you will need to put either "Yes" or "No" in the cell initially.