Sorry to see my solution didn't work out as you needed. I still think the strenght lies in that every sheet has its own customized menu and creating the buttons code is replaced in a way that you don't have to copy them over and over, and can be manged by using arrays to define the menu. So when additions have to be made it can be controled in one place, the arrays.

It also set the same menu to the cell, row and column popup menu. And every cell,row, column menu can be different on you specific sheets.

Jeroen

Wayne,

That was awesome!  I made some modifications, here is what I have so far:









Option Explicit
Option Compare Text

Dim CustomMenuSheets As Object
Dim CellMenu As CommandBar
Dim RowMenu As CommandBar
Dim ColumnMenu As CommandBar
Dim MenuColl As Collection

Private Sub Workbook_Open()

    Dim Counter As Long
    Dim DayName As String
   
    Set CustomMenuSheets = CreateObject("Scripting.Dictionary")
    With CustomMenuSheets
        For Counter = 1 To 7
            DayName = Format(DateSerial(2007, 1, Counter), "dddd")
            .Add DayName, DayName
        Next
    End With
   
    Set MenuColl = New Collection
    Set CellMenu = Application.CommandBars("Cell")
    MenuColl.Add CellMenu
    Set RowMenu = Application.CommandBars("Row")
    MenuColl.Add RowMenu
    Set ColumnMenu = Application.CommandBars("Column")
    MenuColl.Add ColumnMenu

End Sub

Private Sub Workbook_Activate()
   
    If CustomMenuSheets.Exists(ActiveSheet.Name) Then
        NewCellMenu
    Else
        ResetMenus
    End If

End Sub

Private Sub Workbook_Deactivate()
   
    ResetMenus

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
    If CustomMenuSheets.Exists(Sh.Name) Then
        NewCellMenu
    Else
        ResetMenus
    End If
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    If CustomMenuSheets.Exists(Sh.Name) Then
        If Intersect(Target, Sh.[b23:bc36]) Is Nothing Then Cancel = True
    End If

End Sub

Private Sub NewCellMenu()
   
    Dim i As Long
    Dim x As CommandBar
    Dim cb As CommandBarControl
       
    For Each x In MenuColl
        'clear the controls from the menu
        For Each cb In x.Controls
            cb.Delete
        Next

        'add your new controls
        Dim cb1 As CommandBarButton
        Set cb1 = x.Controls.Add(msoControlButton)
        With cb1
            .Caption = "Control1"
            .OnAction = "Macro1"
            .Style = msoButtonCaption
        End With
   
        Dim cb2 As CommandBarButton
        Set cb2 = x.Controls.Add(msoControlButton)
        With cb2
            .Caption = "Control2"
            .OnAction = "Macro2"
            .Style = msoButtonCaption
        End With
   
        Dim cb3 As CommandBarButton
        Set cb3 = x.Controls.Add(msoControlButton)
        With cb3
            .Caption = "Control3"
            .OnAction = "Macro3"
            .Style = msoButtonCaption
        End With
   
        Dim cb4 As CommandBarButton
        Set cb4 = x.Controls.Add(msoControlButton)
        With cb4
            .Caption = "Control4"
            .OnAction = "Macro4"
            .Style = msoButtonCaption
        End With
   
        Dim cb5 As CommandBarButton
        Set cb5 = x.Controls.Add(msoControlButton)
        With cb5
            .Caption = "Control5"
            .OnAction = "Macro5"
            .Style = msoButtonCaption
        End With
    Next
       
    ' disable shortcut keys
    Application.OnKey "^c", "NoShortcut"
    Application.OnKey "^v", "NoShortcut"
    Application.OnKey "^x", "NoShortcut"
    Application.OnKey "^z", "NoShortcut"
    Application.OnKey "^y", "NoShortcut"
   
    ' disable edit menu
    Application.CommandBars("Worksheet Menu Bar").Controls("&Edit").Enabled = False
   
End Sub

Private Sub ResetMenus()

    CellMenu.Reset
    RowMenu.Reset
    ColumnMenu.Reset

    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "^x"
    Application.OnKey "^y"
    Application.OnKey "^z"

    Application.CommandBars("Worksheet Menu Bar").Controls("&Edit").Enabled = True

End Sub






That NoShortcut sub just shows a MsgBox telling the user those items are disabled.  Once I figure
out what goes on my customized context menu, I'll change that part of the code :)

Regards,

Patrick

Jeroen,

Perhaps I overlooked your suggestion unfairly.  As it happens, my immediate need was to have
a single menu for all of those worksheets, but in the future things may change.  I will ask to have
this Q reopened so that I can do a split, if Wayne has no objection...

Regards,

Patrick