CODEWITHSUNDEEP
vbaexcel
Janson A.J
Janson A.J

Reputation: 111

How do I create a macro to perform a right click on a particular cell and choose an option from the right click menu?

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.

Upvotes: 0

Views: 15026

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1652

just some advice, not complete answer, but maybe a good start for you:

?application.commandbars("Cell").Controls(2).caption 'this is your 2nd action in right clic menu, can set it to a variable of type commandbarControl

&Copy

commandbars("Cell").Controls(2).execute 'will perform the action of what it is suposed to do when you clic it.

so maybe with a simple test (assuming you know what the Option will do):

with application.commandbars("Cell").Controls(2)
    if instr(1,.caption,"Copy")>0 then .execute
end with

EDIT : if option 2 is not the good one , you can replace 2 with a Long variable and loop throug the options. EDIT 2 : i figured, you know how to select a particular cell , wich is actually the easy part...

Upvotes: 0

Maciej Los
Maciej Los

Reputation: 8591

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

Upvotes: 1

Related Questions