Remove Right-click Menu Custom Item

Hello,

I was reading a post in the Word Help forum: http://www.vbaexpress.com/forum/showthread.php?t=344

And I have a issue with some similarities in Excel. I've successfully added two custom items to my right click menu. I've used the following code with two of my macros:

Code:

---

Sub CreateRightClick1()
    With Application.CommandBars("Cell").Controls.Add
    .Caption = "Paste Values"
    .OnAction = "pasteValues"
    .Caption = "Paste Formats"
    .OnAction = "pasteFormats"
    End With
End Sub

---

My problem lies in the fact that I accidently ran this code twice and have now duplicated each control. :wot I'm having trouble finding the correct syntax/method of removing them. Can anybody point me in the right direction here? I've seen oodles of code to add to, but taking off a custom control? Any help would be greatly appreciated, this one's exhausting me :confused:

Zack: I'm asking the same question at that link: http://www.vbaexpress.com/forum/showthread.php?t=344

:)
We should then put all these into the kb.

Defenitely! :) I was just discussing that issue with Scott. Adding/removing custom right-click controls, adjusting, etc. Very cool stuff, very useful. :)

Btw, the code that deleted the custom controls as in regards to the above code was:

Code:

---

Sub delMenu()
    Application.CommandBars("Cell").Controls("Paste Formats").Delete
    Application.CommandBars("Cell").Controls("Paste Values").Delete
End Sub

---

Hello Zack,
tried your code but it only created one "right menu item"

but why not first delete existing ones. like:

Code:

---

Sub CreateRightClick1()
On Error Resume Next
    Application.CommandBars("Cell").Controls("Paste Formats").Delete
    Application.CommandBars("Cell").Controls("Paste Values").Delete
With Application.CommandBars("Cell").Controls.Add
    .Caption = "Paste Values"
    .OnAction = "pasteValues"
    End With
    With Application.CommandBars("Cell").Controls.Add
    .Caption = "Paste Formats"
    .OnAction = "pasteFormats"
    End With
End Sub

---

Hi Jeroen,

Well, because I'm a lame-brain at times, to tell you the truth. I was dinking around with it and seeing what I could do. So I whipped up a couple macros and tried assigning them to righ-click contols. That worked all well and good, until I accidently ran the same macro again. Which, as you've seen and noted, doesn't take into account any of the same existing items.

If I need to do it again, I think that's going to be the way I go with it. That's the way I've gone with any custom menus also as of lately. Avoiding duplication is a serious issue, as I've seen with 20/20 hind-sight. ;)

Thanks for your advice, it's greatly appreciated!! :yes

Jeroen: It'd be GREAT if you could post similar code for Word at the other link? :)

I find the best way to do it is to make sure you assign the tag property of your control when you add it with something unique e.g. .Tag="AddinNameAuthor"

You can then use the findcontrols method of the commandbar object to search for all controls with that tag and delete them.

:)

While in this vein do we have a volunteer to write up the Disable Copy functionality etc(Menus, Icons, Keyboard shortcuts and right click)

Jeroen? :)

Cheers

Dave

In VB6, I would use a global or static boolean that gets changed when the function is run the first time. Then, everytime it is run thereafter while the application is open, it just ignores the code.

if DidIRunTheCode = false then
run the code
end if

Any reason that wouldn't work in VBA?

ttfn
Kicker

There is no problem in principle but in practice it's not the best technique. The problem is that if you work on some other code at the same time or some other macro crashes etc. then you can lose the state of all your variables and the menu item would be added again. Also, if you closed the spreadsheet that contained the macro and later reopened it it would have it's variables reset but the toolbar item would already be there.

:)

Quote:

---

Originally Posted by brettdj

While in this vein do we have a volunteer to write up the Disable Copy functionality etc(Menus, Icons, Keyboard shortcuts and right click)

Jeroen? :)

Cheers

Dave

---

Disabling copy is actually rather difficult since there are many ways to copy. Drag & Drop for example is not really thought of as copying (at least by me) but it is.

When I don't want people to paste I use a worksheet selection change event, then just run the code:

Code:

---

Application.CutCopyMode = False

---

But to specifically answer your question, this will disable the copy from the edit menu, standard toolbar, and right click menu.

Code:

---

Application.CommandBars("Standard").Controls("&Copy").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("&Edit").Controls("&Copy").Enabled = False
Application.CommandBars("Cell").Controls("&Copy").Enabled = False

---

To get the Control + C disabled use

Code:

---

Application.OnKey "^c", ""

---

To Reset use

Code:

---

Application.OnKey "^c"

---