disable clear all using VBA

[Brad]

I have tried several versions of the following code and have been
unsuccessful on disabling the "clear all" feature in version 2003 and 2007.
Any suggestions?

Sub testclear()
Application.CommandBars("edit").Controls("Clear").Enabled = False
End Sub

 

[john]

change "e" on edit to uppercase "E" - this worked ok for me.

Sub testclear()
Application.CommandBars("Edit").Controls("Clear").Enabled = False
End Sub

 

[Brad]

With the upper case E
I'm getting a run-time error 5

Invalid procedure call or agrument.

 

[john]

should have said I use 2003.

try this:
Application.CommandBars(1).Controls("Edit").Controls("Clear").Enabled = False

 

[Brad]

John,

Sub testclear()
Application.CommandBars(1).Controls("Edit").Controls("Clear").Enabled =
False
End Sub

Is still giving me the same error message as before - any other suggestions.
I appreciate the input.

 

[Jim Thomlinson]

You are going to need completely different code for 2003 and 2007. 2003 uses
command bars so John's code is close so long as you are using the same
language. To be more robust you would be better off using ID's... Check out
this link... At this site you will also find a pile of stuff on XL2007 which
no longer uses command bars but instead uses a Ribbon...

http://www.rondebruin.nl/menuid.htm
http://www.rondebruin.nl/tips.htm

 

[john]

Brad,
Both versions worked ok in 2003 for me. I can’t immediately think what you
may be doing that would cause your problem & I am about to leave the office.
Hopefully, someone else in the community reading this may be able to offer
further guidance.