vba 如何创建宏以对特定单元格执行右键单击并从右键单击菜单中选择一个选项？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27838681/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create a macro to perform a right click on a particular cell and choose an option from the right click menu?
提问by Janson A.J
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.
我认为这个问题很清楚。我想在 Excel 2007 中设置一个宏来执行以下任务。
(1) 右键单击特定单元格，例如 A1。
(2) 从右键菜单中选择（左键单击）一个选项。
What I actually need is to assign this macro to a command button.
我真正需要的是将此宏分配给命令按钮。
回答by Maciej Los
Go to VBA code editor (ALT+F11) and add new module (Insert->Module) then paste below code:
转到 VBA 代码编辑器 (ALT+F11) 并添加新模块 (Insert->Module)，然后粘贴以下代码：
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:
现在，双击 Sheet1 模块并添加以下代码：
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
有关更多信息，请参阅：
在所有版本的 Excel 中创建弹出菜单在所有版本的 Microsoft Excel 中
自定义上下文菜单
回答by Patrick Lepelletier
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):
所以也许通过一个简单的测试（假设你知道 Option 会做什么）：
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...
编辑：如果选项 2 不是好的选项，您可以用 Long 变量替换 2 并循环遍历选项。编辑2：我想，你知道如何选择一个特定的单元格，这实际上是最简单的部分......
