Help With Right Click

[Dazzawm]

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Long
Cancel = True
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
Dim Rng As Range, Dn As Range
 Set Rng = Range(Range("C" & Target.Row), Cells(Target.Row, Columns.Count).End(xlToLeft))
    For Each Dn In Rng
        If Dn = "HP" Then c = c + 1
    Next Dn
End If
    MsgBox Target & " Has " & c & " HP's"
End Sub

I was given this code so that when I click on a name in column B it will give a pop up telling me how many 'HPs' are in that row in total, but all the right click options are disabled when I right click elsewhere on the sheet. Can this be fixed so all the normal options are there like , copy, paste etc apart from when I click on a name in column B. Thanks.

 

[VoG]

Try

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row <> 3 Then Exit Sub
    Cancel = True
    MsgBox Target.Value & " has " & Application.CountIf(Target.EntireRow, "HP") & " days booked so far"
End Sub

 

[Dazzawm]

Thanks, thats working but I did edit my previous post sorry. Its not counting the total amount of HPs in the column for whatever person is in row 3.

 

[VoG]

Try

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row <> 3 Then Exit Sub
    Cancel = True
    MsgBox Target.Value & " has " & Application.CountIf(Target.EntireColumn, "HP") & " days booked so far"
End Sub

 

[Dazzawm]

Thats working, thanks. I need something else on this WB, should I open a new post?

 

[VoG]

I need something else on this WB, should I open a new post?

I think so unless it is a direct follow on from this question.