I'm always rather nervous of disabling standard coomandbar controls in case
anything goes wrong. Maybe try something along the following lines which
avoids that potential problem and gives you a lot more flexibility control,
eg only cancel the action in a given workbook, or say run test2 in the wb's
activate event and no worries about resetting.

' in a normal workbook
Option Explicit
Private mColCls As Collection
Dim cc As CommandBarButton

Sub test2()
Dim sWBname As String
Dim v
Dim cbr As CommandBar
Dim ctr As CommandBarControl
Dim cls As Class1

sWBname = ActiveWorkbook.Name

Set mColCls = New Collection
For Each v In Array("Row", "Column", "Cell")
Set cbr = CommandBars(v)
For Each ctr In cbr.Controls
Select Case ctr.ID
Case 296, 297, 3181, 3183, 3185, 3187
Set cls = New Class1
Set cls.gCtrl = ctr
mColCls.Add cls, v
Debug.Print ctr.ID, ctr.Caption, v
Exit For
End Select
Next
Next

End Sub


'' in a class named Class1

Public WithEvents gCtrl As CommandBarButton
Public gsWBname As String

Private Sub gCtrl_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
If ActiveWorkbook.Name = gsWBname And Ctrl.ID 3183 Then
CancelDefault = True
MsgBox Ctrl.Caption & " won't work in " & gsWBname
End If
End Sub

to clear up simply do
Set mColCls = Nothing

Regards,
Peter T

PS I see a typo in my last post
for each ctr in commandbars("Row")
should of course have read
for each ctr in commandbars("Row").controls

"J Streger" wrote in message
...
3!?! Wow, I guess I probably should loop rahter than brute force it at
that
rate. Thanks for that!

And yes I tried that but too many factors are getting in the way.
Protection
is great but the sheets are shared more often than not. Since you can't
undo
protection while shared it makes writing code difficult and some things
just
aren't possible. I have a ton of code to control Copy, Past, Cut, Insert,
Delete to keep things in order as my user base can be quite...challenging
at
time. We are also using Excel 2003 currently, and are using Outline
levels,
which also don't seem to work under a protected sheet. I know they fixed
that
aspect in 2007, but the protection/shared conflict causes way to many
issues.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003



"Peter T" wrote:

You are on are on the right lines but actually there are three of them
that
appear depending on the scenario, have a look in a new instance. Rather
than
the brute force perhaps look at what controls already exist on the bar.

for each ctr in commandbars("Row")
debug.? ctr.id, ctr.caption
next

If I follow, the objective is to prevent paste formats. What about Ctrl-v
or
shift-insert (onkey ?). Have you looked at protecting the sheet but
allowing most user changes except formats, and maybe "allow edit" on the
whole sheet.

Regards,
Peter T


"J Streger" wrote in message
...
I think I figured out how to latch onto it.

I threw the last line in where my method used to have the 2:

On Error Resume Next
Application.CommandBars("Row").Controls("Insert Copied C&ells") _
.OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _
"OverrideInsertCells"), "")
Application.CommandBars("Row").Controls("Insert") _
.OnAction = IIf(bOn, IIf(Application.CutCopyMode = False, "", _
"OverrideInsertCells"), "")

It seems Insert and Insert Copied C&ells are both linked, as in they
cannot
coexist on the menu, but yet when they switch they share the same
properties.
So you can set the OnAction property while on insert, then when you
copy
and
show the menu, the OnAction method is auto linked to the Insert Copied
cells.
You just need to put a line for both as you never know which is
actively
there.


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003



"Patrick Molloy" wrote:

hmm

10) no. I still have the Insert Copied Cells ... item

i have to reset to clear it.

there's obviously some code in the copy routine that turns this item
on
or
off internally. bummer


"J Streger" wrote in message
...
Ahhh, but the menu option isn't always there for adjust. Try this:

1) Clear the cut Copy Mode.
2) Rt Click on a row number to bring up the menu, and verify that
Insert
Copied Cells isn't present.
3) Try to run the Intercept Code. It should fail.

Then:

4) Copy any cell to enter cutcopymode
5) Try to run the intercept code. It should fail, as the menu option
is
there.
6) Rt click on a row number.
7) Run the intercept code. It should succeed.
8) Set CutCopyMode to false.
9) Run the Intercept Code. It should Succeed.
10) Rt Click on the row number. Insert Copied Cells should be gone.
11) Run the Intercept code. It should fail again.

It's this disappearing menu item that is driving me insane! :P


--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003



"Patrick Molloy" wrote:

how interesting! I've been doing this for 20 years and I've never
used
this
menu in code!

thank you :)

so, you can intercept if you will

eg


Sub Intercept()

With Application.CommandBars("Row").Controls("Insert Copied
C&ells")
.OnAction = "ShowMessage"
End With
End Sub
Sub ShowMessage()
MsgBox "Hello World!"
End Sub

this means you can write your own code that will paste whatever, or
you
can
just delete this menu item


"J Streger" wrote in message
...
Row command Bar:

Application.CommandBars("Row").Controls("Insert Copied C&ells")

Also some more testing revealed that protecting the sheet from
inserting
rows does turn off this menu option, so there should be a way of
affecting
the function either on the fly or accessing it prior to. Still
haven't
found
a way though.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003



"Patrick Molloy" wrote:

this really isn't very clear. what is a Row Command Bar?