Excel VBA double click function that alternates two texts

Question

I am trying to create Excel VBA code in a cell that alternates 'IN' and 'OUT' by double clicking the cell. It looks like I have to use: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean) in the Sheet1 rather than in a module.

In the same Sheet1, I already have same function in another cell that alternates 'In Operation', 'Failing', and 'Not Operational', which works great.

When I try to copy the code and execute with different range name, I get the compile error message of 'ambiguous name detected.'

How can I go about to avoid the error message and make this work?

Any help wile very appreciated.

You dont need to copy the function, but to test the Target range that comes in as parameter and act accordingly — A.S.H

Unknown Unknown · Accepted Answer · 2016-12-13T14:00:32

The Workbook Codemodule receives events for all the Worksheets

Workbook Codemodule

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    If Sh Is Sheet1 Then
        If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
            Target.Value = IIf(Target.Value = "OUT", "IN", "OUT")
            Cancel = True
        End If
    End If
End Sub

Alternatively, you could just pass the DoubleCLick event parameters to a sub. Make sure that you pass Cancel ByRef so that you can stop the cell from being edited.

Sheet1 Codemodule

Sub Sheet1BeforeDoubleClick(ByVal Target As Range, ByRef Cancel As Boolean)

If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
    Target.Value = IIf(Target.Value = "OUT", "IN", "OUT")
    Cancel = True
End If

End Sub

Standard Codemodule