Hello,

I try to add a context menu to an excel sheet. The problem is that I
can not identify the current commandbar as it seems that this
commandbar is different wether you are in "normal view" or - and
that
is the problem here - you are in "page Break Preview". If the sheet
is
in the normal view the following code works fine, but not if I am in
the "page Break Preview" Mode. I assume the commandbar will even
change if I have a comment in the cell or not and so on ... Is there
a
way to identify the commandbar that will pop up after the
"BeforeRightClick"-Event?

At the moment I have to following code in my worksheet and it works
fine during "normal view" but it does not work during "page Break
Preview"

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)
On Error Resume Next
cmdid = Application.CommandBars("Cell").ID
' cmdid = 427
With Application
.CommandBars(cmdid).Controls("Update Work Center in
SAP").Delete
Set cBut
= .CommandBars(cmdid).Controls.Add(Temporary:=True)
End With


With cBut
.Caption = "Update Work Center in SAP"
.Style = msoButtonCaption
.OnAction = "change_workcenter"
.Parameter = Format(Target.value)
End With
On Error GoTo 0
End Sub

It too is called Cell, with an Id of 427 (as against the normal 424).

Do a Findcontrol on both by Id and add to each control.

--
__________________________________
HTH

Bob

<martin.j.jung@googlemail.com> wrote in message
news:42ccfff6-d4e7-4059-bf72-4de5abb92e7c@1g2000pre.googlegroups.com...
> Hello,
>
> I try to add a context menu to an excel sheet. The problem is that I
> can not identify the current commandbar as it seems that this
> commandbar is different wether you are in "normal view" or - and
> that
> is the problem here - you are in "page Break Preview". If the sheet
> is
> in the normal view the following code works fine, but not if I am in
> the "page Break Preview" Mode. I assume the commandbar will even
> change if I have a comment in the cell or not and so on ... Is there
> a
> way to identify the commandbar that will pop up after the
> "BeforeRightClick"-Event?
>
> At the moment I have to following code in my worksheet and it works
> fine during "normal view" but it does not work during "page Break
> Preview"
>
> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
> As Boolean)
> On Error Resume Next
> cmdid = Application.CommandBars("Cell").ID
> ' cmdid = 427
> With Application
> .CommandBars(cmdid).Controls("Update Work Center in
> SAP").Delete
> Set cBut
> = .CommandBars(cmdid).Controls.Add(Temporary:=True)
> End With
>
>
> With cBut
> .Caption = "Update Work Center in SAP"
> .Style = msoButtonCaption
> .OnAction = "change_workcenter"
> .Parameter = Format(Target.value)
> End With
> On Error GoTo 0
> End Sub
>
>

I think that id number varied in different versions of excel.

But (so far!), the pagebreak cell id was 3 more than the normal cell id.



Bob Phillips wrote:
>
> It too is called Cell, with an Id of 427 (as against the normal 424).
>
> Do a Findcontrol on both by Id and add to each control.
>
> --
> __________________________________
> HTH
>
> Bob
>
> <martin.j.jung@googlemail.com> wrote in message
> news:42ccfff6-d4e7-4059-bf72-4de5abb92e7c@1g2000pre.googlegroups.com...
> > Hello,
> >
> > I try to add a context menu to an excel sheet. The problem is that I
> > can not identify the current commandbar as it seems that this
> > commandbar is different wether you are in "normal view" or - and
> > that
> > is the problem here - you are in "page Break Preview". If the sheet
> > is
> > in the normal view the following code works fine, but not if I am in
> > the "page Break Preview" Mode. I assume the commandbar will even
> > change if I have a comment in the cell or not and so on ... Is there
> > a
> > way to identify the commandbar that will pop up after the
> > "BeforeRightClick"-Event?
> >
> > At the moment I have to following code in my worksheet and it works
> > fine during "normal view" but it does not work during "page Break
> > Preview"
> >
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
> > As Boolean)
> > On Error Resume Next
> > cmdid = Application.CommandBars("Cell").ID
> > ' cmdid = 427
> > With Application
> > .CommandBars(cmdid).Controls("Update Work Center in
> > SAP").Delete
> > Set cBut
> > = .CommandBars(cmdid).Controls.Add(Temporary:=True)
> > End With
> >
> >
> > With cBut
> > .Caption = "Update Work Center in SAP"
> > .Style = msoButtonCaption
> > .OnAction = "change_workcenter"
> > .Parameter = Format(Target.value)
> > End With
> > On Error GoTo 0
> > End Sub
> >
> >

--

Dave Peterson

On Sep 4, 6:54*pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I think that id number varied in different versions of excel.
>
> But (so far!), the pagebreak cell id was 3 more than the normal cell id.
>
>
>
>
>
> Bob Phillips wrote:
>
> > It too is called Cell, with an Id of 427 (as against the normal 424).
>
> > Do a Findcontrol on both by Id and add to each control.
>
> > --
> > __________________________________
> > HTH
>
> > Bob
>
> > <martin.j.j...@googlemail.com> wrote in message
> >news:42ccfff6-d4e7-4059-bf72-4de5abb92e7c@1g2000pre.googlegroups.com...
> > > Hello,
>
> > > I try to add a context menu to an excel sheet. The problem is that I
> > > can not identify the current commandbar as it seems that this
> > > commandbar is different wether you are in "normal view" or *- and
> > > that
> > > is the problem here - you are in "page Break Preview". If the sheet
> > > is
> > > in the normal view the following code works fine, but not if I am in
> > > the "page Break Preview" Mode. I assume the commandbar will even
> > > change if I have a comment in the cell or not and so on ... Is there
> > > a
> > > way to identify the commandbar that will pop up after the
> > > "BeforeRightClick"-Event?
>
> > > At the moment I have to following code in my worksheet and it works
> > > fine during "normal view" but it does not work during "page Break
> > > Preview"
>
> > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
> > > As Boolean)
> > > On Error Resume Next
> > > * * * *cmdid = Application.CommandBars("Cell").ID
> > > ' * * * *cmdid = 427
> > > * * * *With Application
> > > * * * * * *.CommandBars(cmdid).Controls("Update Work Center in
> > > SAP").Delete
> > > * * * * * *Set cBut
> > > = .CommandBars(cmdid).Controls.Add(Temporary:=True)
> > > * * * *End With
>
> > > * * * *With cBut
> > > * * * * * .Caption = "Update Work Center in SAP"
> > > * * * * * .Style = msoButtonCaption
> > > * * * * * .OnAction = "change_workcenter"
> > > * * * * * .Parameter = Format(Target.value)
> > > * * * *End With
> > > * *On Error GoTo 0
> > > End Sub
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Thanks for your answer ...

I tried it and it worked but is there a way to get the current
commandbar, irrespective what view I have?
Meanwhile I have learned that I have to use even another commandbar,
if in the cell that is under the cursor is attached to a query from a
database...

Personally, I'd just add the options I want to each of those Cell commandbars.

But if you want, you could look at the activewindow.

if ActiveWindow.View = xlNormalView then
'use the normal ID
else
'use the pagebreak ID
end if

And if you use pivottables, you may have another commandbar to update, too.



martin.j.jung@googlemail.com wrote:
>
<<snipped>>
>
> Thanks for your answer ...
>
> I tried it and it worked but is there a way to get the current
> commandbar, irrespective what view I have?
> Meanwhile I have learned that I have to use even another commandbar,
> if in the cell that is under the cursor is attached to a query from a
> database...

--

Dave Peterson

You have to modify each commandbar that could popup in any context you want
to cover.

And I suggest that you do it when the workbook opens or is activated rather
than at the right-click event. The reason is that your modifications will
occur only if the user makes the menu popup by a right-click. But if he
uses some other means, like Shift-F10 or the dedicated context menu key that
some newer keyboards have, the right-click code will not be called and your
changes will not be there.

--
Jim
<martin.j.jung@googlemail.com> wrote in message
news:233aeb36-14c1-4ec3-a86b-2d8e081e2218@r15g2000prd.googlegroups.com...
On Sep 4, 6:54 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I think that id number varied in different versions of excel.
>
> But (so far!), the pagebreak cell id was 3 more than the normal cell id.
>
>
>
>
>
> Bob Phillips wrote:
>
> > It too is called Cell, with an Id of 427 (as against the normal 424).
>
> > Do a Findcontrol on both by Id and add to each control.
>
> > --
> > __________________________________
> > HTH
>
> > Bob
>
> > <martin.j.j...@googlemail.com> wrote in message
> >news:42ccfff6-d4e7-4059-bf72-4de5abb92e7c@1g2000pre.googlegroups.com...
> > > Hello,
>
> > > I try to add a context menu to an excel sheet. The problem is that I
> > > can not identify the current commandbar as it seems that this
> > > commandbar is different wether you are in "normal view" or - and
> > > that
> > > is the problem here - you are in "page Break Preview". If the sheet
> > > is
> > > in the normal view the following code works fine, but not if I am in
> > > the "page Break Preview" Mode. I assume the commandbar will even
> > > change if I have a comment in the cell or not and so on ... Is there
> > > a
> > > way to identify the commandbar that will pop up after the
> > > "BeforeRightClick"-Event?
>
> > > At the moment I have to following code in my worksheet and it works
> > > fine during "normal view" but it does not work during "page Break
> > > Preview"
>
> > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
> > > As Boolean)
> > > On Error Resume Next
> > > cmdid = Application.CommandBars("Cell").ID
> > > ' cmdid = 427
> > > With Application
> > > .CommandBars(cmdid).Controls("Update Work Center in
> > > SAP").Delete
> > > Set cBut
> > > = .CommandBars(cmdid).Controls.Add(Temporary:=True)
> > > End With
>
> > > With cBut
> > > .Caption = "Update Work Center in SAP"
> > > .Style = msoButtonCaption
> > > .OnAction = "change_workcenter"
> > > .Parameter = Format(Target.value)
> > > End With
> > > On Error GoTo 0
> > > End Sub
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Thanks for your answer ...

I tried it and it worked but is there a way to get the current
commandbar, irrespective what view I have?
Meanwhile I have learned that I have to use even another commandbar,
if in the cell that is under the cursor is attached to a query from a
database...