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.

 

[Jon von der Heyden]

Try:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 2 Then Exit Sub
    Cancel = True
    MsgBox Application.CountIf(Target.EntireRow, "HP") & " HP's in row"
End Sub

 

[VoG]

Try

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim c As Long
Dim Rng As Range, Dn As Range
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
    Cancel = True
    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
    MsgBox Target & " Has " & c & " HP's"
End If
End Sub

 

[MickG]

Change the code roung like this:-

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim num As Long
Dim Rng As Range, Dn As Range
If Not Intersect(Target, Columns("B:B")) Is Nothing Then
  Cancel = True
  Set Rng = Range("C" & Target.Row).Resize(, 230)
     num = Application.CountIf(Rng, "HP")
     MsgBox Target & " Has " & num & " HP's"
End If
End Sub

 

[Dazzawm]

Thanks all, used Jon Von Der Heydens and that worked.

 

[Dazzawm]

I have changed the layout of the worksheet because there are not enough columns. I need the code to work for all the names in row 3 rather than column B please.

 

[VoG]

Try

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row <> 3 Then Exit Sub
    Cancel = True
    MsgBox Application.CountIf(Target.EntireRow, "HP") & " HP's in row"
End Sub

 

[Dazzawm]

Nothings happening.

 

[VoG]

Working here. Make sure that events are enabled. Press CTRL + G to open the Immediate Window, type

Application.EnableEvents=True

and press Enter. Then try again.

 

[Dazzawm]

Got that to work thanks, but it is not countiing how HPs are in that particular column. Also how do I do it so it puts whatever name is in the cell I right click on in row 3 in the MsgBox? eg. "Fred Smith has 8 days booked of so far"