PDA

View Full Version : Solved: Remove Right-click Menu Custom Item



Zack Barresse
06-21-2004, 10:00 AM
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:


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:

Anne Troy
06-21-2004, 10:21 AM
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.

Zack Barresse
06-21-2004, 10:30 AM
Defenitely! :) I was just discussing that issue with Scott. Adding/removing custom right-click controls, adjusting, etc. Very cool stuff, very useful. :)

Zack Barresse
06-21-2004, 10:32 AM
Btw, the code that deleted the custom controls as in regards to the above code was:


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

roos01
06-21-2004, 10:44 AM
Hello Zack,
tried your code but it only created one "right menu item"

but why not first delete existing ones. like:


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

Zack Barresse
06-21-2004, 10:58 AM
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

Anne Troy
06-21-2004, 11:01 AM
Jeroen: It'd be GREAT if you could post similar code for Word at the other link? :)

mark007
06-21-2004, 11:54 AM
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.

:)

brettdj
06-22-2004, 06:02 AM
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

Kicker
07-01-2004, 11:27 AM
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

mark007
07-02-2004, 03:05 AM
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.

:)

Jacob Hilderbrand
07-02-2004, 04:58 AM
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:


Application.CutCopyMode = False


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


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

Jacob Hilderbrand
07-02-2004, 05:11 AM
To get the Control + C disabled use

Application.OnKey "^c", ""

To Reset use

Application.OnKey "^c"