Prev: Custom Header Aligment
Next: Move macro to Personal.xls
From: cate on 6 Mar 2010 12:29
I have tried a dozen protection setups in an attempt to keep someone
from using clear all to delete data in unlocked cells. (They need to
change values in these cells thus the access). Clear contents is OK.

Is there a vba way to remove this particular entry in the standard
excel menu?

Thank you.

excel 2003
From: cate on 6 Mar 2010 14:06

> I have tried a dozen protection setups in an attempt to keep someone
> from using clear all to delete data in unlocked cells.  (They need to
> change values in these cells thus the access).  Clear contents is OK.
>
> Is there a vba way to remove this particular entry in the standard
> excel menu?

My savior, record macro, didn't come thru for me. I get no output
when I manually remove the ALL from the menu:

-->Tools -->Customized =Edit ....
I could send this out like this, I will, but would be nice to be able
to do this in the code.




From: Dave Peterson on 6 Mar 2010 15:18
This worked ok in my USA/English menus (xl2003):

Option Explicit
Sub auto_open()
With Application.CommandBars("worksheet menu bar")
.Controls("Edit").Controls("Clear").Controls("All").Enabled = True
End With
End Sub
Sub auto_Close()
With Application.CommandBars("worksheet menu bar")
.Controls("Edit").Controls("Clear").Controls("All").Enabled = True
End With
End Sub

I like to keep the option there, but disable it.

But if you want to hide it, you .visible = false (and true when the workbook
closes).

ps.

This really won't stop anyone. If they customize their toolbars, they can
add/rename the control to anything they want--or use a macro to do the clear
all.



cate wrote:
>
> > I have tried a dozen protection setups in an attempt to keep someone
> > from using clear all to delete data in unlocked cells. (They need to
> > change values in these cells thus the access). Clear contents is OK.
> >
> > Is there a vba way to remove this particular entry in the standard
> > excel menu?
>
> My savior, record macro, didn't come thru for me. I get no output
> when I manually remove the ALL from the menu:
>
> -->Tools -->Customized =Edit ....
> I could send this out like this, I will, but would be nice to be able
> to do this in the code.

--

Dave Peterson
 | 
Pages: 1
Prev: Custom Header Aligment
Next: Move macro to Personal.xls