Replace Right-Click menu

[bstubbs]

Howdy:

I want to be able to give my application menu when the user right-clicks on a specific series of cells. I've already got the code that will limit the right-click options to the cells that I want. How do I then form and execute the menu that I want the user to see - "Archive Data", "Edit Data", "Add Data", for instance, rather than the normal right-click menu?

Any ideas?

TIA.

Bob

[Bob Phillips]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ctl As CommandBarControl

    If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
    
        With CommandBars("Cell")
        
            For Each ctl In .Controls
            
                ctl.Visible = False
            Next ctl
            
            With .Controls.Add(Type:=msoControlButton, temporary:=True)
            
                .Caption = "Archive Data"
                .OnAction = "ArchiveData"
            End With
            
            With .Controls.Add(Type:=msoControlButton, temporary:=True)
            
                .Caption = "Edit Data"
                .OnAction = "EditData"
            End With
            
            With .Controls.Add(Type:=msoControlButton, temporary:=True)
            
                .Caption = "Add Data"
                .OnAction = "AddData"
            End With
        End With
    Else
    
        CommandBars("Cell").Reset
    End If
    
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[bstubbs]

Cool.

Thanks.

Bob