SOLVED

Multiple Worksheet_BeforeDoubleClick function

Brass Contributor

Hi Everyone,

 

Would like to know, how to work with multiple Worksheet_BeforeDoubleClick function as I got an error as per below. It seems that it doesn't work when you have more than one Worksheet_BeforeDoubleClick  function. Thanks in advance. Attached were the sample file. 

rbalza_1-1626764235889.png

 

 

 

1 Reply
best response confirmed by rbalza (Brass Contributor)
Solution

@rbalza 

There can be only one procedure with the same name. So you must combine them into a single procedure:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim xCellsA As String
    Dim r As Long
    If Not Intersect(Range("B4:B8"), Target) Is Nothing Then
        Cancel = True
        r = 5 * Target.Row - 14
        xCellsA = r & ":" & r + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    If Not Intersect(Range("I12:I16,I18:I22,I24:I28,I30:I34,I36:I40,I42:I46,I48:I52,I54:I58,I60:I64,I66:I70"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
End Sub
1 best response

Accepted Solutions
best response confirmed by rbalza (Brass Contributor)
Solution

@rbalza 

There can be only one procedure with the same name. So you must combine them into a single procedure:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim xCellsA As String
    Dim r As Long
    If Not Intersect(Range("B4:B8"), Target) Is Nothing Then
        Cancel = True
        r = 5 * Target.Row - 14
        xCellsA = r & ":" & r + 4
        If Target.Value = "+" Then
            Rows(xCellsA).Hidden = False
            Target.Value = "-"
        Else
            Rows(xCellsA).Hidden = True
            Target.Value = "+"
        End If
    End If
    If Not Intersect(Range("I12:I16,I18:I22,I24:I28,I30:I34,I36:I40,I42:I46,I48:I52,I54:I58,I60:I64,I66:I70"), Target) Is Nothing Then
        Cancel = True
        Select Case Target.Value
            Case "X"
                Target.Value = "P"
            Case "O"
                Target.Value = "X"
            Case Else
                Target.Value = "O"
        End Select
    End If
End Sub

View solution in original post