Excel – How to create a macro to perform a right click on a particular cell and choose an option from the right click menu

excelvba

I think the question is clear. I want to set a macro in Excel 2007 which performs the following tasks.
(1) Right clicks on a particular cell, say A1.
(2) Chooses(left clicks) an option from the right click menu.

What I actually need is to assign this macro to a command button.

Best Answer

Go to VBA code editor (ALT+F11) and add new module (Insert->Module) then paste below code:

Option Explicit

Public Const myBar As String = "MyPopupBar"


Sub CreatePopup()
Dim cmb As CommandBar
Dim ctr As CommandBarControl

DeletePopup

Set cmb = Application.CommandBars.Add(myBar, msoBarPopup)
Set ctr = cmb.Controls.Add(msoControlButton)

With ctr
    .Caption = "Click me"
    .OnAction = "ClickMe"
End With

cmb.ShowPopup

Set ctr = Nothing
Set cmb = Nothing

End Sub

Sub ClickMe()

    MsgBox "You clicked me!", vbInformation, "Wow!"

End Sub

Sub DeletePopup()

On Error Resume Next
Application.CommandBars(myBar).Delete

End Sub

Now, double click on Sheet1 module and add this code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
CreatePopup
Cancel = True
End Sub

That's all!

[EDIT]
You're able to call popup menu from commandbutton, this way:

Application.CommandBars("TheNameOfCommandBarPopup").ShowPopup

For further information, please see:
Creating Popup Menus in All Versions of Excel
Customizing Context Menus in All Versions of Microsoft Excel

Related Topic