VBA Help with custom right click menu.

[decadence]

Hi, Is it possible to enable a right click custom context menu by Selection?. I can grey out the menus/buttons on workbook open, I have a feeling selection change event will be needed. Can anyone help me with this

 

[VBA Geek]

you could use the Before_right_click event?

 

[decadence]

This is what I have so far but not sure how to make it work,

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

    Dim cbar As CommandBar
    Dim ctrl As CommandBarControl

    If Target.Address.CutCopyMode = xlCopy Or xlCut = True Then
        With cbar
            If .Type = msoBarTypePopup Then
                    If ctrl.Tag = "Custom" Then
    Cancel = True
    Else
    End If
    Cancel False
End Sub

 

[VBA Geek]

what are you trying to achieve?

This is what I have so far but not sure how to make it work,

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

    Dim cbar As CommandBar
    Dim ctrl As CommandBarControl

    If Target.Address.CutCopyMode = xlCopy Or xlCut = True Then
        With cbar
            If .Type = msoBarTypePopup Then
                    If ctrl.Tag = "Custom" Then
    Cancel = True
    Else
    End If
    Cancel False
End Sub

 

[decadence]

I am trying to enable a custom right click context menu if the cut or copy functions are used, if not then keep disabled

 

[VBA Geek]

I am trying to enable a custom right click context menu if the cut or copy functions are used, if not then keep disabled

you need to change the name of myTempCBar to the name of your command bar, from your code above I assume it is a msoBarPopUp type

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.CutCopyMode = False Then
    Cancel = True
    Application.CommandBars("myTempCBar").ShowPopup
End If
End Sub

 

[decadence]

you need to change the name of myTempCBar to the name of your command bar

Where would I find my command bar name?

 

[VBA Geek]

Where would I find my command bar name?

are you not adding a custom command bar?

 

[decadence]

Yes I am but a bit confused on what you mean by command bar name. My Apologies as I am still learning VBA. I am trying to disable the menu in the code below if the cut or copy functions are used

This Workbook

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Application.CutCopyMode = False Then
    Cancel = True
    Application.CommandBars("myTempCBar").ShowPopup
End If
End Sub

Module

Option Explicit

Public Const Mname As String = "CustomClearClick"

Sub CustomClearClick()

    Call KillCustomClearClick
    
    Dim ContextMenu1 As CommandBar
    Dim MySubMenu1 As CommandBarControl
    Set ContextMenu1 = Application.CommandBars("Cell")
    
    Set MySubMenu1 = ContextMenu1.Controls.Add(Type:=msoControlPopup, Before:=9)
    

        With MySubMenu1
             .Caption = "&Clear..."
             .Tag = "CustomClear"

        With .Controls.Add(Type:=msoControlButton)
             .OnAction = "ClearAll"
             .Caption = "&Clear All"
             .FaceId = 2060
        End With
        
        With .Controls.Add(Type:=msoControlButton)
             .BeginGroup = True
             .OnAction = "ClearContents"
             .Caption = "&Clear Contents"
             .FaceId = 1964
        End With
        
        With .Controls.Add(Type:=msoControlButton)
             .OnAction = "ClearFormats"
             .Caption = "&Clear Formats"
             .FaceId = 872
        End With
    End With

    ContextMenu1.Controls(9).BeginGroup = True
    
End Sub

Sub KillCustomClearClick()
    On Error Resume Next
    Application.CommandBars(Mname).Delete
    On Error GoTo 0
End Sub

Function ClearAll()
    Selection.Clear
End Function

Function ClearContents()
    Selection.ClearContents
End Function

Function ClearFormats()
    Selection.ClearFormats
End Function

 

[VBA Geek]

I am trying to disable the menu in the code below if the cut or copy functions are used

Are you trying to disable the entire right click menu if the you are in cutCopyMode?