Multiple before right click options

[andrewb90]

Hello all,

I have a custom right click for a series of ranges on on of my sheets. But I want to have a different range of cells with different right click options. is that possible?

here's a shortened version of my code:

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

Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl


Application.CommandBars("Cell").Reset


If Intersect(Target, Range("E89:R106,E108:R125,E127:R144,E146:R163")) Is Nothing Then
    Application.CommandBars("Cell").Reset
    Exit Sub
Else


    Set ContextMenu = Application.CommandBars("Cell")


    For Each ctrl In ContextMenu.Controls
            ctrl.Delete
    Next ctrl
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO"
        .FaceId = 2113
        .Caption = Sheets("Settings").Range("K16") & " " & Sheets("Settings").Range("L16")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_OTHER"
        .FaceId = 1845
        .Caption = Sheets("Settings").Range("K17") & " " & Sheets("Settings").Range("L17")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=3)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_NOTE"
        .FaceId = 916
        .Caption = "CUSTOM NOTES"
        '.Font.Color = vbWhite
        '.Interior.Color = vbBlack
    End With
    


End If


End Sub

 

[FormR]

Hi, have you considered something a structure similar to this:

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


If Not Intersect(Target, Range("E89:R106")) Is Nothing Then
    'Do stuff for range E89:R106
    Exit Sub
End If


If Not Intersect(Target, Range("E108:R125")) Is Nothing Then
    'Do stuff for range E108:R125
    Exit Sub
End If


'etc
'etc


Application.CommandBars("Cell").Reset


End Sub

 

[andrewb90]

Here's my revised code: It's only using the first part though

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

Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl


Application.CommandBars("Cell").Reset


If Intersect(Target, Range("E89:R89")) Is Nothing Then
    'Application.CommandBars("Cell").Reset
    Exit Sub
Else


    Set ContextMenu = Application.CommandBars("Cell")


    For Each ctrl In ContextMenu.Controls
            ctrl.Delete
    Next ctrl
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO"
        .FaceId = 2113
        .Caption = Sheets("Settings").Range("K16") & " " & Sheets("Settings").Range("L16")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_OTHER"
        .FaceId = 1845
        .Caption = Sheets("Settings").Range("K17") & " " & Sheets("Settings").Range("L17")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=3)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_NOTE"
        .FaceId = 916
        .Caption = "CUSTOM NOTES"
        '.Font.Color = vbWhite
        '.Interior.Color = vbBlack
    End With
    


End If


If Intersect(Target, Range("D89:D92")) Is Nothing Then
    'Application.CommandBars("Cell").Reset
    Exit Sub
Else


    Set ContextMenu = Application.CommandBars("Cell")


    For Each ctrl In ContextMenu.Controls
            ctrl.Delete
    Next ctrl
    


    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_CUSTOM"
        .FaceId = 484
        .Caption = Sheets("Settings").Range("K21") & " " & Sheets("Settings").Range("L21")
    End With
    
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "CLEAR"
        .FaceId = 2087
        .Caption = "*!CLEAR REQUESTS!*"
    End With
End If
Application.CommandBars("Cell").Reset
End Sub

 

[FormR]

Here's my revised code:

Hi, that's not quite what I suggested.

 

[andrewb90]

I see it... "Not" That completely reversed the entire thing and made it work....

Thank you!