0

The Scenario

Hi guys, I am about to add a few custom controls to the cell context menu (not the ribbon) in my Excel workbook using the instructions found on this MSDN page. The only problem I am having is that I need the items to only be enabled for a specific column/range of cells.

I've looked around, and I've been unable to find any steps for this--there are some for VSTO development (written in C#), but that is not what I need. I plan to write this using the VBA IDE built into Office, and perhaps a bit of XML using the Custom UI Editor.

The Question

So basically, I'm looking for a way to run a function at the time the context menu is called (i.e., upon right-click) that validates the selection to make sure it is in the appropriate column. If it isn't, I would like my custom buttons to be greyed out. NOTE: This may not be done with a validation function at all, I honestly have no clue. If there is a better way, feel free to share it (I'm open to suggestions).

P.S.

Please don't think I am asking you to write my code. Creating these buttons should be very simple, as I have created many before (albeit they were all Ribbon items), and I hope it is okay to ask for some quick assistance on this very specific issue.

Thank you in advance!

Ross Brasseaux
  • 3,879
  • 1
  • 28
  • 48

1 Answers1

1

Try this:

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim rclk As CommandBarControl

If Target.Address(RowAbsolute:=False) = "$A1" Then
    Set rclk = Application.CommandBars("Cell").FindControl(Tag:="RightClickMenu1")
    With rclk
        .Enabled = False
    End With
Else
    Set rclk = Application.CommandBars("Cell").FindControl(Tag:="RightClickMenu1")
    With rclk
        .Enabled = True
    End With
End If

End Sub

"RightClickMenu1" is created in Workbook_Open(), other than that code is self-explanatory I think.

Lukas2
  • 320
  • 3
  • 8
  • Looks do-able. Like I said, I haven't written the code yet, but when I do I'll mark this complete if it works. – Ross Brasseaux Aug 19 '14 at 17:03
  • You can also use `Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)` – Lukas2 Aug 19 '14 at 17:17