I read the long discussion that Ron and Peter were trying to answer back in
2005, but another solution was found prior to answering question in the OP.

I am trying to take control of the Inser Copied Cells button on the Row
CommandBar. It is not cooperating very well, as the button appears and
disappears, thus clearing any changes I make to it. So far I know:

1) The option will only appear after a CutCopyMode is not longer false
2) The option will only appear once the user activates the Row Menu.

So I cannot seem to access this command until the menu becomes visible, but
I can't seem to do anything to menu while it's visible. I tried adding an
Application.OnTime event, but that won't fire if the menu is visible.
Essentially I can take over the menu if the user shows it and then selects
elsewhere, but if the show it for the first time, I cannot control that
option.

I'm trying to take it over so that users do not have the ability to copy
formatting from another location into a specific sheet. They need to have the
ability to copy rows or insert rows, but I jusst don't want the formatting to
come with it. I've hijacked all other means, just this one seems impossible
to control. Any idea?

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

User of MS Office 2003

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



"J Streger" <(E-Mail Removed)> wrote in message
news6CB2EF9-BC00-4C90-855B-(E-Mail Removed)...
> I read the long discussion that Ron and Peter were trying to answer back
> in
> 2005, but another solution was found prior to answering question in the
> OP.
>
> I am trying to take control of the Inser Copied Cells button on the Row
> CommandBar. It is not cooperating very well, as the button appears and
> disappears, thus clearing any changes I make to it. So far I know:
>
> 1) The option will only appear after a CutCopyMode is not longer false
> 2) The option will only appear once the user activates the Row Menu.
>
> So I cannot seem to access this command until the menu becomes visible,
> but
> I can't seem to do anything to menu while it's visible. I tried adding an
> Application.OnTime event, but that won't fire if the menu is visible.
> Essentially I can take over the menu if the user shows it and then selects
> elsewhere, but if the show it for the first time, I cannot control that
> option.
>
> I'm trying to take it over so that users do not have the ability to copy
> formatting from another location into a specific sheet. They need to have
> the
> ability to copy rows or insert rows, but I jusst don't want the formatting
> to
> come with it. I've hijacked all other means, just this one seems
> impossible
> to control. Any idea?
>
> --
> *********************
> J Streger
> MS Office Master 2000 ed.
> MS Project White Belt 2003
>
> User of MS Office 2003
>

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?
>

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" <(E-Mail Removed)> wrote in message
news:F1D85DC7-6A36-491B-9205-(E-Mail Removed)...
> 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?
>>
>

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" <(E-Mail Removed)> wrote in message
> news:F1D85DC7-6A36-491B-9205-(E-Mail Removed)...
> > 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?
> >>
> >
>

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" <(E-Mail Removed)> wrote in message
news:FFA12604-8F10-4CFD-B252-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:F1D85DC7-6A36-491B-9205-(E-Mail Removed)...
>> > 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?
>> >>
>> >
>>

Yup and it's added only when the menu is called, meaning you have no time to
actually grab hold of the function.

And the only way to disable it is to protect the sheet, and that is not
acceptable for what I'm doing. *sigh*

--
*********************
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" <(E-Mail Removed)> wrote in message
> news:FFA12604-8F10-4CFD-B252-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:F1D85DC7-6A36-491B-9205-(E-Mail Removed)...
> >> > 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?
> >> >>
> >> >
> >>
>

did you try deleting it?

"J Streger" <(E-Mail Removed)> wrote in message
news:6B3C5014-7C4C-4555-8FAA-(E-Mail Removed)...
> Yup and it's added only when the menu is called, meaning you have no time
> to
> actually grab hold of the function.
>
> And the only way to disable it is to protect the sheet, and that is not
> acceptable for what I'm doing. *sigh*
>
> --
> *********************
> 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" <(E-Mail Removed)> wrote in message
>> news:FFA12604-8F10-4CFD-B252-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:F1D85DC7-6A36-491B-9205-(E-Mail Removed)...
>> >> > 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?
>> >> >>
>> >> >
>> >>
>>

i doubt that works - I'd guess the item gets added when copy is selected,
and I'd expect the OnAction to be recreated then too?



"Patrick Molloy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> did you try deleting it?
>
> "J Streger" <(E-Mail Removed)> wrote in message
> news:6B3C5014-7C4C-4555-8FAA-(E-Mail Removed)...
>> Yup and it's added only when the menu is called, meaning you have no time
>> to
>> actually grab hold of the function.
>>
>> And the only way to disable it is to protect the sheet, and that is not
>> acceptable for what I'm doing. *sigh*
>>
>> --
>> *********************
>> 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" <(E-Mail Removed)> wrote in message
>>> news:FFA12604-8F10-4CFD-B252-(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in message
>>> >> news:F1D85DC7-6A36-491B-9205-(E-Mail Removed)...
>>> >> > 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?
>>> >> >>
>>> >> >
>>> >>
>>>

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" <(E-Mail Removed)> wrote in message
> news:FFA12604-8F10-4CFD-B252-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:F1D85DC7-6A36-491B-9205-(E-Mail Removed)...
> >> > 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?
> >> >>
> >> >
> >>
>