Right Click Menu not working when sheet is in Page Break Preview m

I have a routine which adds two additional items to my right click menu. It
works really well. Except today I went to use it while my spreadsheet was in
"Page Break Preview" (i.e. View/Page Break Preview) instead of "Normal" and
the routine falied to work. Why would this matter? It works with he
following code in Normal view.

Thisworkbook module:
**********************************************8
Private AppClass As EventClass
Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Excel.Application
End Sub
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
Call DeleteCustomMenu
End Sub

Class Module
*************************************************
Option Explicit
Public WithEvents App As Excel.Application
Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
Call DeleteCustomMenu 'remove possible duplicates
Call BuildCustomMenu 'build new menu
End Sub

Regular Module
***************************************************
Option Explicit
Dim MyDataObj As New DataObject 'Note Reference to Microsoft Forms 2.0
needed under Tools/References
Sub BuildCustomMenu()
Dim ctrl As CommandBarControl
Dim btn As CommandBarControl
Dim i As Integer
'add a 'popup' control to the cell commandbar (menu)
Set ctrl = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlPopup, Before:=5)
ctrl.Caption = "Menu1..."
ctrl.BeginGroup = True

'add a 'popup' control to the cell commandbar (menu)
Set ctrl = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlPopup, Before:=6)
ctrl.Caption = "Menu2..."
'add the submenus
Set btn = ctrl.Controls.Add
btn.Caption = "List Correct?" 'give them a name
btn.OnAction = "ValidationCheck" 'the routine called by the control
End Sub

Sub DeleteCustomMenu()
Dim ctrl As CommandBarControl

'This line of code wil reset menu if you run into trouble.
'Comment-out if code is working
'If not working Comment in and then comment out
'the items in the For Each stmt below
'Then comment this back out and comment in
'items in for Each loop.
'Application.CommandBars("Cell").Reset

'go thru all the cell commandbar controls and delete our menu item
For Each ctrl In Application.CommandBars("Cell").Controls
If ctrl.Caption = "Menu1..." Then
ctrl.Delete
ElseIf ctrl.Caption = "Menu2..." Then
ctrl.Delete
End If
Next

End Sub

sorry I forgot a few line of code for the first menu item but they are not
really relevant for the question. Here is the revised code. Note I have
left out the routines that actually get called which you click on the buttons
associated with the menu items.

> Thisworkbook module:
> **********************************************8
> Private AppClass As EventClass
> Private Sub Workbook_Open()
> Set AppClass = New EventClass
> Set AppClass.App = Excel.Application
> End Sub
> Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
> Call DeleteCustomMenu
> End Sub
>
> Class Module
> *************************************************
> Option Explicit
> Public WithEvents App As Excel.Application
> Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
> Range, Cancel As Boolean)
> Call DeleteCustomMenu 'remove possible duplicates
> Call BuildCustomMenu 'build new menu
> End Sub
>
> Regular Module
> ***************************************************
> Option Explicit
> Dim MyDataObj As New DataObject 'Note Reference to Microsoft Forms 2.0
> needed under Tools/References
> Sub BuildCustomMenu()
> Dim ctrl As CommandBarControl
> Dim btn As CommandBarControl
> Dim i As Integer
> 'add a 'popup' control to the cell commandbar (menu)
> Set ctrl = Application.CommandBars("Cell").Controls.Add _
> (Type:=msoControlPopup, Before:=5)
> ctrl.Caption = "Menu1..."
> ctrl.BeginGroup = True
> 'add the submenus
> Set btn = ctrl.Controls.Add
> btn.Caption = "Copy Cell Value" 'give them a name
> btn.OnAction = "CopyValue" 'the routine called by the control
>
> 'add a 'popup' control to the cell commandbar (menu)
> Set ctrl = Application.CommandBars("Cell").Controls.Add _
> (Type:=msoControlPopup, Before:=6)
> ctrl.Caption = "Menu2..."
> 'add the submenus
> Set btn = ctrl.Controls.Add
> btn.Caption = "List Correct?" 'give them a name
> btn.OnAction = "ValidationCheck" 'the routine called by the control
> End Sub
>
> Sub DeleteCustomMenu()
> Dim ctrl As CommandBarControl
>
> 'This line of code wil reset menu if you run into trouble.
> 'Comment-out if code is working
> 'If not working Comment in and then comment out
> 'the items in the For Each stmt below
> 'Then comment this back out and comment in
> 'items in for Each loop.
> 'Application.CommandBars("Cell").Reset
>
> 'go thru all the cell commandbar controls and delete our menu item
> For Each ctrl In Application.CommandBars("Cell").Controls
> If ctrl.Caption = "Menu1..." Then
> ctrl.Delete
> ElseIf ctrl.Caption = "Menu2..." Then
> ctrl.Delete
> End If
> Next
>
> End Sub

"ExcelMonkey" wrote:

> I have a routine which adds two additional items to my right click menu. It
> works really well. Except today I went to use it while my spreadsheet was in
> "Page Break Preview" (i.e. View/Page Break Preview) instead of "Normal" and
> the routine falied to work. Why would this matter? It works with he
> following code in Normal view.
>
> Thisworkbook module:
> **********************************************8
> Private AppClass As EventClass
> Private Sub Workbook_Open()
> Set AppClass = New EventClass
> Set AppClass.App = Excel.Application
> End Sub
> Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
> Call DeleteCustomMenu
> End Sub
>
> Class Module
> *************************************************
> Option Explicit
> Public WithEvents App As Excel.Application
> Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As
> Range, Cancel As Boolean)
> Call DeleteCustomMenu 'remove possible duplicates
> Call BuildCustomMenu 'build new menu
> End Sub
>
> Regular Module
> ***************************************************
> Option Explicit
> Dim MyDataObj As New DataObject 'Note Reference to Microsoft Forms 2.0
> needed under Tools/References
> Sub BuildCustomMenu()
> Dim ctrl As CommandBarControl
> Dim btn As CommandBarControl
> Dim i As Integer
> 'add a 'popup' control to the cell commandbar (menu)
> Set ctrl = Application.CommandBars("Cell").Controls.Add _
> (Type:=msoControlPopup, Before:=5)
> ctrl.Caption = "Menu1..."
> ctrl.BeginGroup = True
>
> 'add a 'popup' control to the cell commandbar (menu)
> Set ctrl = Application.CommandBars("Cell").Controls.Add _
> (Type:=msoControlPopup, Before:=6)
> ctrl.Caption = "Menu2..."
> 'add the submenus
> Set btn = ctrl.Controls.Add
> btn.Caption = "List Correct?" 'give them a name
> btn.OnAction = "ValidationCheck" 'the routine called by the control
> End Sub
>
> Sub DeleteCustomMenu()
> Dim ctrl As CommandBarControl
>
> 'This line of code wil reset menu if you run into trouble.
> 'Comment-out if code is working
> 'If not working Comment in and then comment out
> 'the items in the For Each stmt below
> 'Then comment this back out and comment in
> 'items in for Each loop.
> 'Application.CommandBars("Cell").Reset
>
> 'go thru all the cell commandbar controls and delete our menu item
> For Each ctrl In Application.CommandBars("Cell").Controls
> If ctrl.Caption = "Menu1..." Then
> ctrl.Delete
> ElseIf ctrl.Caption = "Menu2..." Then
> ctrl.Delete
> End If
> Next
>
> End Sub

The problem seems to be with Application.CommandBars("Cell").

I have a sheet too were I replaced this right-click menu, and indeed it
didn't work in page break view. I suspect that the name for the
commandbar in this view mode is not Cell, but something else.

Continuing to search...

http://www.mrexcel.com/archive2/34200/39271.htm

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:
code:
Sub ListShortCutMenus()
Dim r As Long
Dim cBar As CommandBar
Dim c As Integer
r = 1
For Each cBar In CommandBars
If cBar.Type = msoBarTypePopup Then
Cells(r, 1) = cBar.Index
Cells(r, 2) = cBar.Name
For c = 1 To cBar.Controls.Count
Cells(r, c + 2) = cBar.Controls(c).Caption
Next c
r = r + 1
End If
Next cBar
End Sub

Haven't tested yet, gonna do it now, but it should answer your (and
mine) question.

Ok, this is my final answer :-)

I found out that in Excel 2003, commandbars("Cell") refers to
commandbars(29). You need to run the same routine to alter the menu
for commandbars(32) (which is stupidly enough also named "cell").

Because they have the same name, but id 29 is above id 32 in the array,
when modifying "cell", you only modify the 'normal view' context menu,
being 29.

Beware: apparently, the id's differ between various Excel versions (I
found id 25 and 28 when googling). So you'll have to introduce some
check on the version of excel, and then assign the correct id numbers.
An idea: you take ...("cell") and add 3 to it's index (that offset
seems to be the same in the different Excel versions).

More info >>> shout

Jake Marx posted this a while back which also lists the menus. So what I
really want is a reference for all menus. According to this, the menu index
for the normal view is 36.

So if I do not use "Cell" and I want to ensure that my items always appear,
what reference do I use?


Sub EnumCommandBars()
Dim cmd As CommandBar
Dim ctl As CommandBarControl

For Each cmd In Application.CommandBars
Cells(1, cmd.Index).Value = cmd.Index
Cells(2, cmd.Index).Value = cmd.Name
For Each ctl In cmd.Controls
Cells(ctl.Index + 2, cmd.Index).Value _
= ctl.Caption
Next ctl
Next cmd
End Sub

"[email protected]" wrote:

> http://www.mrexcel.com/archive2/34200/39271.htm
>
> 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:
> code:
> Sub ListShortCutMenus()
> Dim r As Long
> Dim cBar As CommandBar
> Dim c As Integer
> r = 1
> For Each cBar In CommandBars
> If cBar.Type = msoBarTypePopup Then
> Cells(r, 1) = cBar.Index
> Cells(r, 2) = cBar.Name
> For c = 1 To cBar.Controls.Count
> Cells(r, c + 2) = cBar.Controls(c).Caption
> Next c
> r = r + 1
> End If
> Next cBar
> End Sub
>
> Haven't tested yet, gonna do it now, but it should answer your (and
> mine) question.
>
>

I'm not sure I understand your last reply...

What version of Excel are you using? As I said, the id's for the popup
menus may vary from version to version. In my xl2003, it's 29 for
normal view and 32 for page break preview. If yours is 36 for normal
view, I guess you use a different Excel version.

To find out which id your Excel uses for the page break view, execute
the code you posted (sub EnumCommandBars) and check the resulting list.
There should be 2 commandbars named "Cell" - one with id 36 then, and
the other one is for the page break. Just check the id that is in the
corresponding cell.

It appears (through current versions of excel) that they always differ by 3.

So you could find the first and add 3 to get the second:

Option Explicit
Sub testme01()
dim CellIndex(1 to 2) as long
dim iCtr as long

CellIndex(1) = Application.CommandBars("Cell").Index
CellIndex(2) = cellindex(1) + 3

.....

And adjust both commandbars with the same code through their indices.

for ictr = 1 to 2
Set ctrl = Application.CommandBars(cellindex(ictr)).Controls.Add _
....
next ictr




ExcelMonkey wrote:
>
> Jake Marx posted this a while back which also lists the menus. So what I
> really want is a reference for all menus. According to this, the menu index
> for the normal view is 36.
>
> So if I do not use "Cell" and I want to ensure that my items always appear,
> what reference do I use?
>
> Sub EnumCommandBars()
> Dim cmd As CommandBar
> Dim ctl As CommandBarControl
>
> For Each cmd In Application.CommandBars
> Cells(1, cmd.Index).Value = cmd.Index
> Cells(2, cmd.Index).Value = cmd.Name
> For Each ctl In cmd.Controls
> Cells(ctl.Index + 2, cmd.Index).Value _
> = ctl.Caption
> Next ctl
> Next cmd
> End Sub
>
> "[email protected]" wrote:
>
> > http://www.mrexcel.com/archive2/34200/39271.htm
> >
> > 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:
> > code:
> > Sub ListShortCutMenus()
> > Dim r As Long
> > Dim cBar As CommandBar
> > Dim c As Integer
> > r = 1
> > For Each cBar In CommandBars
> > If cBar.Type = msoBarTypePopup Then
> > Cells(r, 1) = cBar.Index
> > Cells(r, 2) = cBar.Name
> > For c = 1 To cBar.Controls.Count
> > Cells(r, c + 2) = cBar.Controls(c).Caption
> > Next c
> > r = r + 1
> > End If
> > Next cBar
> > End Sub
> >
> > Haven't tested yet, gonna do it now, but it should answer your (and
> > mine) question.
> >
> >

--

Dave Peterson