How to disable the "Insert Copied Cells" context menu item

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

When one or more rows are in copy mode, the row(s) can be pasted by means of
the contextmenu item (right mouse button): Insert Copied Cells. I need to
disable especially this item and also the "Copied Cells" from the Insert
menu. Does any one know how to achieve this?
 
Hi Ron, thanks for your quick response. Unfortunately I don't think this
brings the solution. The menu I'm looking for is a context menu and only
visible during 'copymode'. So if you have any idea's how to disable this
"Insert Copied Cells" item, please.

Again: my largest problem is how to disabe the context menu of "Insert
Copied Cells".

Regards Coen.
 
Hi Coen

You can find the ID numbers for this.(links on the bottom of the page)
I will look them up for you this evening.

But first I put the kids in bed.
 
Ok

Here is a example

Sub TEST()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=3184, Recursive:=True).Enabled = False

Application.CommandBars("Cell").FindControl(ID:=3185).Enabled = False

End Sub
 
Thanks Ron, you helped me a lot.
The only issue yet left is:

This item is in:
CommandBar: Cell ID = 3185
CommandBar: Row ID = 3187
CommandBar: Worksheet Menu Bar(recursive) ID = 3184

At opening a spec. Excel-book I need to disable ALL this three items. But it
looks like they are not always available. It looks like you have to be in a
sort of 'Cell copy mode' or in 'Row copy mode' to be able to disable the
items.

Any ideas about this?

Coen
 
looks like they are not always available
That's correct

If there is something on the Clipboard they are available
But it is working for me with the test macro

I will test more tomorrow(bed time for me now)
 
Hi Coen

It seems that only the Insert menu item is a problem.
I will test more tomorrow after work.
 
Hi Ron (and everyone having suggestions),

Situation is as the following. I'm dealing with an add-in application (.xla
and Office 2003). At startup of this application (preferrably at the
Workbook_Open event) I should disable all three "Insert Copied Cells"
menuitems. This is not as easy as it looks for me because these items are not
available at startup, so any ideas are more then welcome.
 
Hi Coen

I think the best thing that you can do is to disable "Insert"
It will disable Inset copeid cells also.

This seems to work fine, only the row ID for insert is 3181 but this is not working.
I am searching the real number now.(I post back)

Sub falsefalse()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=295, Recursive:=True).Enabled = False

Application.CommandBars("Cell").FindControl(ID:=3181).Enabled = False
'Application.CommandBars("Row").FindControl(ID:=3181).Enabled = False
Application.CommandBars("Column").FindControl(ID:=3183).Enabled = False
End Sub

Sub truetrue()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=295, Recursive:=True).Enabled = True

Application.CommandBars("Cell").FindControl(ID:=3181).Enabled = True
'Application.CommandBars("Row").FindControl(ID:=3181).Enabled = True
Application.CommandBars("Column").FindControl(ID:=3183).Enabled = True
End Sub
 
It is very strange

The row and column "Insert" option use the same ID number
But sometimes it say 3181 and the other time 3183.

I think there is a bug, I will see if I can find a save workaround
 
Hi Ron,

This subject is of interest to me to prevent change in layout of cells. I
can almost cater for inserted/deleted rows/columns but not inserted cells.

The "Insert" ID's change according to:
- State of CutCopyMode if cells in clipboard, and
- Insert ID is only updated AFTER user has right clicked row/column header,
Cell menu, or clicked main Insert menu.

Row/Column
3181 "&Insert..."
3183 "&Insert"
3187 "&Insert Copied Cells..."
Main Insert Menu
295 "C&ells"
3184 "Copied C&ells..."
Cell
3181 "&Insert..."
3185 "Insert Copied C&ells..."

It's a real pain! Problem is only the current item can be changed at any one
time. Trying all with "On Error Resume Next" doesn't help. Only alternative
as far as I can see is to disable the entire respective menus, which I don't
want to do. Unless of course you have any other ideas??

Regards,
Peter T
 
Hi Peter

I will look for a solution when I have time.
If I find something I will post back.
 
Hi guys,

I'm almost completely lost! I only want to disable (or remove, or change the
action etc) the "Insert Copied Cells" from the row and cell menus. It looks
this can only be done (for the rowmenu) when the sheet is in "row" copymode
and the right mouse menu is visible. And this causes an unwanted sideeffect:
when the "Insert Copied Cells" item has been disabled (or deleted etc), the
same effect is there for the "Insert" item (i.e. insert a row) when the sheet
is not anymore in row-copymode. That's not what I want, the Insert item must
be kept enabled.
Is this really not possible?



But this effects also the
 
Ron - my comment "Trying all with On Error Resume Next doesn't help" was
wrong, see modified version of your routine below.

Coen - I don't see any way to selectively disable "Insert Copied Cells" from
Row, Column & Cell menus, and "Copied Cells" from main Insert menu, and not
disable the other related menus. But Ron might !

If you are in a position always to know the state "CutCopyMode" following
should be OK for you, use the "copy mode" version in the Test sub. But I
imagine that's unlikely.

Curiosity - what's the purpose to disable only "Insert Copied Cells" whilst
allowing "Paste" and "Insert".

Sub Test()
'toggle menus
Static bln As Boolean
bln = Not bln
EnableInsertMenus bln

'or according to copy mode
'EnableInsertMenus Not CBool(Application.CutCopyMode)
End Sub

Sub EnableInsertMenus(bEnable As Boolean)
'don't forget to run this with True when done
Dim cb As CommandBar, oCtl As Object
Dim va, i As Long, j As Long, nID As Long

'Debug.Print
'Debug.Print "CutCopyMode: " & CBool(Application.CutCopyMode)
'Debug.Print "Menus Enabled: " & bEnable

va = Array("Insert", "Cell", "Row", "Column")
On Error Resume Next
For i = LBound(va) To UBound(va)
Set cb = Application.CommandBars(va(i))

For j = 3181 To 3187
If j = 3186 Then
nID = 295
Else: nID = j
End If

Set oCtl = cb.FindControl(ID:=nID)
oCtl.Enabled = bEnable

' If Err.Number Then
' Err.Clear
' Else
' Debug.Print va(i), oCtl.ID, oCtl.Caption
' End If
Next
Next

End Sub

I stumbled across ID 3182 "C&ells" in the main Insert menu. Seems ID's 3181
to 3185, 3187 need processing. I've not seen 3186, hence substituted with
295 in the routine, but maybe 3186 should also be included.

Don't think above should cause permanent loss of "inserts", but if necessary
include "cb.Reset" below "Set cb" and comment everything below except the
last Next.

Regards,
Peter T
 
Hi peter, the reason for disabling only the "Insert Copied Cells" item is
that I'm dealing with an vba application (xla) that was build in Excel 97.
Now, after upgrading office to 2003, the Excel 2003 version appears to have a
nasty problem which only occur while using this "Insert Copied Cells" option.
This problem doesn't exist in the '97 version, where "insert copied cells"
worked fine. In 2003 using a) copy a row, b) insert a blanc row, and c)
paste the row is not causing this problem.

Btw: your right, knowing the CutCopyMode status doesn't help me.

Regards, Coen.
 
Hi Coen and Peter

I will see if I have some time this weekend to play with it.

--
Regards Ron de Bruin
http://www.rondebruin.nl



Coen said:
Hi peter, the reason for disabling only the "Insert Copied Cells" item is
that I'm dealing with an vba application (xla) that was build in Excel 97.
Now, after upgrading office to 2003, the Excel 2003 version appears to have a
nasty problem which only occur while using this "Insert Copied Cells" option.
This problem doesn't exist in the '97 version, where "insert copied cells"
worked fine. In 2003 using a) copy a row, b) insert a blanc row, and c)
paste the row is not causing this problem.

Btw: your right, knowing the CutCopyMode status doesn't help me.

Regards, Coen.
Click to expand...
 
Back
Top