Adding Context Menu (right click) Options

[martin.j.jung]

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

 

[Bob Phillips]

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.

 

[Dave Peterson]

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.

 

[martin.j.jung]

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.








--

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...

 

[Dave Peterson]

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.

 

[Jim Rech]

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

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.








--

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...