0

I need to remove the 'Paste Options' from the right button click menus in Excel 2010 using VBA.

I am able to remove all unnecessary 'normal' control items (such as "Cu&t", "&Copy", "Paste &Special..." etc.) that I do not need, but cannot get rid of 'Paste Options' and its icon children. I have also removed 'Paste Options' from the Excel File->Options->Advanced->Cut Copy and Paste, but this has no effect on the right button click 'Paste Options'. I have searched high and wide for a solution but can find no reference to this anywhere on the Web.

I have also examined the CommandBar object in the VBA 'Locals' window and looked at all of the properties and cannot see where the 'Paste Options' are held.

Thank you in advance

lfrandom
  • 1,013
  • 2
  • 10
  • 32
user2615484
  • 59
  • 1
  • 5

2 Answers2

3

using the customui editor you can add this to the customui14 part

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <commands>
        <command idMso="Paste" enabled="false"/>
        <command idMso="PasteGallery" enabled="false"/>
        <command idMso="PasteGalleryMini" enabled="false"/>
        <command idMso="PasteMenu" enabled="false"/>
        <command idMso="PasteLink" enabled="false"/>
        <command idMso="PasteAsHyperlink" enabled="false"/>
        <command idMso="PasteSpecialDialog" enabled="false"/>
        <command idMso="PasteFormatting" enabled="false"/>
        <command idMso="PasteFormulas" enabled="false"/>
        <command idMso="PasteValues" enabled="false"/>
        <command idMso="PasteFormulasAndNumberFormatting" enabled="false"/>
        <command idMso="PasteTranspose" enabled="false"/>
        <command idMso="PastePictureLink" enabled="false"/>
    </commands>
</customUI>

if you want to hide the controls instead you have to do it for each menu you want to manipulate. for instance for the Cell menu

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <contextMenus>
        <contextMenu idMso="ContextMenuCell">
            <gallery idMso="PasteGalleryMini" visible="false"/>
        </contextMenu>
    </contextMenus>
</customUI>
JosieP
  • 3,360
  • 1
  • 13
  • 16
  • 1
    Hi JosieP, many thanks. I downloaded the customUI editor from http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx and added the code as instructed and the "Paste Options:" item is now disabled. Is there any way to hide it? – user2615484 Jul 30 '13 at 20:41
  • 1
    @JosieP I am able to hide Paste options successfully but I am not able to hide Paste Special. – Attiqe Aug 16 '16 at 15:06
0

Using a for loop, here's the IDs and Captions of the CommabdBarControl elements of my context menu:


    ID      Caption
    21      Cu&t
    19      &Copy
    22      &Paste
    21437   Paste &Special...
    3624    &Paste Table
    25536   Smart &Lookup
    32713   Data T&ype
    33409   Translate
    295     Insert C&ells...
    27690   Insert C&ells...
    292     &Delete...
    3125    Clear Co&ntents
    24508   &Quick Analysis
    31623   Sp&arklines
    31402   Filt&er
    31435   S&ort
    2031    Insert Co&mment
    1592    Delete Co&mment
    1593    Sh&ow/Hide Comments
    855     &Format Cells...
    1966    Pic&k From Drop-down List...
    1614    &Show Phonetic Field
    13380   Define N&ame...
    1576    &Hyperlink...
    1577    Edit &Hyperlink...
    1015    &Open Hyperlink
    3626    &Remove Hyperlink
    11299   E&xpand to detail
    31595   Additional Act&ions
    178     F&ull Screen
    22577   &Additional Actions

So it seems like if you get rid of "Paste" and "Paste Special" then all the paste options go away. Not exactly sure what "Paste Table" refers to.


    For Each menuItem In Application.CommandBars("Cell").Controls
        If menuItem.ID = 22 or menuItem.ID = 21437 Then menuItem.Delete
    Next
berban
  • 1