Posted to microsoft.public.excel.programming
|
|
How to disable the "Insert Copied Cells" context menu item
Hi Ron and Peter,
I agree, and I have decided to focus on the real problem. Disabling this
menuitem won't be a succesfull solution. Thanks for all the help.
Regards Coen.
"Peter T" wrote:
Hi Coen,
I've noticed a number of issues in this ng concerning paste & XL2003. By
coincidence someone has just tagged similar on to the end of this Sept 2003
thread, after a short comment of mine.
Subject: XL2003 "paste method of worksheet class failed"
http://tinyurl.com/7xp7e
Rather than disabling menu items, it might be worth getting to the route of
the problem and warrent a new post. Fully describe your "nasty problem" and
under what circumstances it occurs.
Regards,
Peter T
"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
snip<
|