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" wrote in message ...
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.

"Peter T" wrote:

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

"Coen" wrote in message
...
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 de Bruin" wrote:

Hi Peter

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



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



"Peter T" <peter_t@discussions wrote in message
...
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

"Ron de Bruin" wrote in message
...
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


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



"Ron de Bruin" wrote in message
...
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:=31 81).Enabled =
False
'Application.CommandBars("Row").FindControl(ID:=31 81).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:=31 81).Enabled =
True
'Application.CommandBars("Row").FindControl(ID:=31 81).Enabled =
True
Application.CommandBars("Column").FindControl(ID:= 3183).Enabled
=
True
End Sub


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