VBA for Right Click Macros

[andrewb90]

Hello All,

I have been searching the web for the code to do what I am looking for, but haven't had much luck, so I'm hoping someone on here will be able to help

I would like to have 2 different macros appear in the right click menu, however I would like to only have this appear on 1 sheet, and only on selected cells on the sheet.
Is this doable?

Thank you,

Andrew

 

[Michael M]

Not sure whether you can restrict it to one sheet, but have a read here


http://www.mrexcel.com/forum/excel-questions/10876-how-add-macro-right-click-menu.html

 

[Alan_P]

Hi Andrew,

This has worked when testing;

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("A1:A5")) Is Nothing Then
    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 & "'!" & "MyMacro1"
        .FaceId = 59
        .Caption = "MyMacro Number 1"
    End With

    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=2)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "MyMacro2"
        .FaceId = 59
        .Caption = "MyMacro Number 2"
    End With
End If

End Sub

Then to make sure the menu resets if you click straight to another sheet

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.CommandBars("Cell").Reset

End Sub

Hope this helps,
Cheers,
Alan.

 

[andrewb90]

Where do I place the second macro? Right below the first or does that need to be somewhere else?

 

[Michael M]

Put it in the "This Workbook" module

 

[andrewb90]

Awesome! thank you both!

1 thing I noticed is the specified range doesn't seem to be affecting which cells have the unique right click menu. Isn't

If Intersect(Target, Range("E89:R106")) Is Nothing Then    Exit Sub
Else

That supposed to determine the range?

 

[Alan_P]

Happy to help

Is it only affecting other cells after the specified range has been right clicked? Probably need to add a reset before exiting the sub...

If Intersect(Target, Range("E89:R106")) Is Nothing Then
    [COLOR="#FF0000"]Application.CommandBars("Cell").Reset[/COLOR]
    Exit Sub
Else

 

[andrewb90]

I did that, but I don't see any change, I also noticed that all my other sheets have that custom right click menu now as well.

 

[andrewb90]

Ok, I feel a little dumb, I looked back at This Workbook sheet and realized I had deleted the other piece of code. Now with the little change everything appears to be working.

 

[Alan_P]

No prob easily done Glad it's working for you