Add simple condition to code that prevents code running

[Ironman]

Hi

The code below runs when any cell in column H row 12 downwards is double-clicked

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

    ' check Target is in column H
    If Target.Column = 8 And Target.Row >= 12 Then
        Cancel = True
        TopCell = Cells(12, 3).Address 'Row 12, Column 3 i.e. mileage
        BottomCell = Cells(Target.Row, 3).Address

        TotalCalc = Application.WorksheetFunction.Sum(Range(TopCell, BottomCell))

        MsgBox "Total miles run to " & Format(Cells(ActiveCell.Row, 1), "dd mmmm yyyy: ") & _
                     Format((CLng(10720.31 + TotalCalc)), "#,##0") & "   ", vbOKOnly, "Lifetime Mileage"
    End If

I'm looking for an additional row of code so the above code does not run if Column B in the same row contains the word 'OTHER' (without the commas).

Many thanks!

 

[Michael M]

Try

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

    ' check Target is in column H
    
If Cells(Target.Row, "B").Value = "OTHER" Then Exit Sub
If Target.Column = 8 And Target.Row >= 12 Then
        Cancel = True
        TopCell = Cells(12, 3).Address 'Row 12, Column 3 i.e. mileage
        BottomCell = Cells(Target.Row, 3).Address

        TotalCalc = Application.WorksheetFunction.Sum(Range(TopCell, BottomCell))

        MsgBox "Total miles run to " & Format(Cells(ActiveCell.Row, 1), "dd mmmm yyyy: ") & _
                     Format((CLng(10720.31 + TotalCalc)), "#,##0") & "   ", vbOKOnly, "Lifetime Mileage"
    End If
End Sub

 

[Ironman]

Many thanks Michael, that works perfectly!

Could I just ask you how the code would need to look if I wanted to also exclude the word 'REST' please?

Thanks again!

 

[Michael M]

get you explain a bit more please on that request

 

[Ironman]

Sure! So the above code does not run if Column B in the same row contains either the word 'OTHER' or 'REST'.

Thanks again.

 

[Michael M]

If Cells(Target.Row, "B").Value = "OTHER" OR Cells(Target.Row, "B").Value = "REST" Then Exit Sub

 

[Ironman]

Perfect! Thank you so much Michael!

 

[Michael M]

Glad to help...