Delete temporary option from right click menu

user_4758444 0
Viewed 48

The following code is sitting in a worksheet called "SPG Summary"

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
      Dim cmdBtn As CommandBarButton, param As String
      
      Set cmdBtn = Application.CommandBars("Cell").FindControl(, , "testBt")
      If Intersect(Target, Range("C21:C42")) Is Nothing Then
        If Not cmdBtn Is Nothing Then cmdBtn.Delete
        Exit Sub
      End If
        
        If Not cmdBtn Is Nothing Then Exit Sub
        Set cmdBtn = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
        param = "test param"
        With cmdBtn
            .Tag = "testBt"
            .Caption = "MyOption"
            .Style = msoButtonCaption
            .OnAction = "TestMacro"
        End With
    End Sub

Private Sub Worksheet_Deactivate()
    Dim cmdBtn As CommandBarButton
    Set cmdBtn = Application.CommandBars("Cell").FindControl(, , "testBt")
    If Not cmdBtn Is Nothing Then cmdBtn.Delete
End Sub

The TestMacro code is as follows:

Sub TestMacro()
    
    Sheets("UPC Summary").Range("A21").Value = ActiveCell.Value
    Application.Goto Sheets("UPC Summary").Range("A1"), True

End Sub

If I remove the "Sub Worksheet_Deactivate" the code will work but it means the right click option is still available on the worksheet we have gone to which I would like to stop as that right click menu option should only be available on the "SPG Summary" sheet.

If I try to run it as is I get the following error:

enter image description here

and errors on the line with the bold italic being what excel colours yellow as the issue "If Not cmdBtn Is Nothing Then cmdBtn.Delete"

Many thanks in advance, Alan.

vba excel
1 Answers

Seems like you can't remove the right-click option while its code is still executing.

You could use something like this instead, in the ThisWorkbook module:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    Dim cmdBtn As CommandBarButton, param As String
    
    Set cmdBtn = Application.CommandBars("Cell").FindControl(, , "testBt")
    
    'adjust sheet name/range to suit
    If Sh.Name = "Data" And Not Intersect(Target, Sh.Range("C21:C42")) Is Nothing Then
        If cmdBtn Is Nothing Then
            Set cmdBtn = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
            param = "test param"
            With cmdBtn
                .Tag = "testBt"
                .Caption = "MyOption"
                .Style = msoButtonCaption
                .OnAction = "TestMacro"
            End With
        End If
    Else
        If Not cmdBtn Is Nothing Then cmdBtn.Delete
    End If
      
End Sub
user_4788840
Related