Adding Context Menu (right click) Options

  • Thread starter Thread starter martin.j.jung
  • Start date Start date
M

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
 
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.
 
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.
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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