在Excel 2010中自定义其他单元格操作

Question

我想通过添加更多的"附加单元格操作"来扩展MS Excel 2010。(可通过单元格右键单击>附加单元格操作访问)。具体来说，我希望Excel:

1. 识别五到八位数字作为部件号，操作:"打开技术文档的URL"
2. 将字符串"OR ## #####"(#表示数字)识别为带有操作的订单引用:"打开规格文件"one_answers"打开材料文件"(两个Excel文件都位于内网的指定路径上)

现在，我不知道如何编程。我怀疑需要一些XML片段，可能还需要一些VB代码。VB代码不会是一个问题-我有宏做这些功能为Excel 2003 -但我不知道把它放在哪里。

请给我一些指点，我已经问过谷歌，但不能得到答案，似乎"额外的行动"是相当常见的短语:)

Answer 1

这可以通过向工作簿

添加右键事件处理程序来实现。

在Workbook模块中添加以下代码

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim cBut As CommandBarButton
    Dim v As Variant
    On Error Resume Next
    v = Target
    ' Remove any previously added menu items
    Application.CommandBars("Cell").Controls("Open URL to technical docs").Delete
    Application.CommandBars("Cell").Controls("Open material file").Delete
    ' save cell value for use by called macro
    CellValue = v
    ' If cell matches criteria add menu item and set macro to call on click
    If IsNumeric(v) Then
        If v >= 10000 And v <= 99999999 Then
            Set cBut = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
            With cBut
                .Caption = "Open URL to technical docs"
                .Style = msoButtonCaption
                .OnAction = "OpenRef"
            End With
        End If
    ElseIf v Like "OR ## #####" Then
        Set cBut = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
        With cBut
            .Caption = "Open material file"
            .Style = msoButtonCaption
            .OnAction = "OpenMat"
        End With
    End If
End Sub

在标准模块中添加以下代码

Public CellValue As Variant
' replace MsgBox code with your logic to open files
Sub OpenRef()
    MsgBox "Open Reference Doc code here for " & CellValue
End Sub
Sub OpenMat()
    MsgBox "Open Material File code here for " & CellValue
End Sub