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

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

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