Page Break Preview - context menu

The ID for the Cells shortcut menu is 25 in normal view and 28 in Page Break Preview.

As an aside this code will list them all:

Just ran your code on my Office 97 test box and me Office 2k test box and we get different results it would appear that MS added in some new menus between the two, and no doubt it will be different again in XP, so what I need to use is the name of the menu or something else that is the same.

Am I right in thinking that the two are both called Cell?

Is there anyway this can be acheived?

Steve.

Yes they are both called Cell - a bit careless that. And I remember reading that the index numbers changed between Excel 97 and Excel 2000.

I can't find another property to use so I guess the only way is to loop around the collection to find the ones named Cell.

I suppose we should consider ourselves lucky that we can customise the Shortcut menus and live with any shortcomings.

ok sorted it now, here is my solution

Private Sub GetCellCommandBarIndexes()
ReDim iCellMenuPos(CommandBars.Count) As Integer 'just in case MS add some more.
Dim iCount As Integer: iCount = 0

Dim cBar As CommandBar

For Each cBar In CommandBars
If cBar.Name = "Cell" Then
iCellMenuPos(iCount) = cBar.Index
iCount = iCount + 1
End If
Next cBar

ReDim Preserve iCellMenuPos(iCount - 1) 'ok so it should be 2 integers big, but just in case.

End Sub


and then just iterate around the array like this

For i = LBound(iCellMenuPos) To UBound(iCellMenuPos)
Set cbReporting = Application.CommandBars(iCellMenuPos(i))
Set cbcReporting = cbReporting.FindControl(msoControlPopup, , gsAppName)

If Not (cbcReporting Is Nothing) Then cbcReporting.DELETE

'Now the cell popup
Set cbcReporting = Application.CommandBars(iCellMenuPos(i)).Controls.Add(msoControlPopup)
cbcReporting.Caption = gsAppName
cbcReporting.Tag = gsAppName
cbcReporting.BeginGroup = True

Set cbReporting = cbcReporting.CommandBar
cbReporting.Controls.Add msoControlButton, , , 1, True
cbReporting.Controls(1).Caption = "menuOption"
cbReporting.Controls(1).OnAction = "menuoption"
cbReporting.Controls(1).FaceId = 462
Next


But when you exit the app you must remove the menus otherwise they appear next time you open the app, this might be a good or a bad thing depending on your view point.

Public Sub TidyUpMenus()
'remove the context menus
Dim cbcReporting As CommandBarControl
Dim i As Integer

Call GetCellCommandBarIndexes

For i = LBound(iCellMenuPos) To UBound(iCellMenuPos)

Set cbcReporting = Application.CommandBars(iCellMenuPos(i)).FindControl(msoControlPopup, , gsAppName)
If Not (cbcReporting Is Nothing) Then cbcReporting.DELETE

Next i
End Sub

Hope this helps some other people

Steve.