How to disable the Tools menu..is it possible.thanks

You can't disable the Tools pull-down menu itself, but I think that the commands within it could be removed in either Excel 95, Excel 97 or both. (Excel 2000 doesn't seem to have this option.) Unfortunately, I don't remember how to remove the commands in Excel 95/97. Hopefully someone else can post that.

You should be able to hide it:
application.CommandBars.item(1).controls("Tools").Visible = False

or merely disable it (making it grey and disabling the drop-down menu):
application.CommandBars.item(1).controls("Tools").Enabled = False

HTH

Thanks Mr Tim but its not working..

i have put your code in the workbook module
but the tools menu still shows and the drop
down also still active. any ideas ?

Re: Thanks Mr Tim but its not working..

My one idea is to specify explicitly the toolbar:

application.CommandBars("Worksheet Menu Bar").Controls("tools").Enabled = False

beyond that, I'm not sure what would be preventing
this from doing what you want.

Excel97 you can disable the Tools drop down list.
The Tools drop down list is just a memeber of the
Commandbars "Worksheet menu bar", if you specify
this you should be able to disable it.
Tims syntax = Application.CommandBars("Worksheet Menu Bar").Controls("tools").Enabled = False
should have disabled it (Greyed out)...the only
reason it may not have is if it was moved to
another commandbar.

Ivan

where should i put this code Ivan.........

where should i put this code Ivan :
Tims syntax = Application.CommandBars("Worksheet Menu Bar").Controls("tools").Enabled =
False

i put in on the workbook and in the worksheet via vbeditor
but its not working Please help me

Re: where should i put this code Ivan.........

In the VBA editor get to this via
ALt F11
Then in here If project explorer not visible
press Ctrl R

In the project explorer IF you haven't already
got a module present then
Right click on any of the workbook objects
and select Insert.....then select Module.

This is where you would place your code;
eg.

Sub Disable_Tools()
Application.CommandBars("Worksheet Menu Bar").Controls("tools").Enabled =False
End sub

'Need this to enable
Sub Enable_Tools()
Application.CommandBars("Worksheet Menu Bar").Controls("tools").Enabled =True
End sub

To call it just run as normal eg.
from your sheet via Alt F8

Ivan

Ivan ,ok thanks it works.. 1 more thing.lastly

can you do that automatically like
sub workbook_open..i am assuming like
is it possible

In the VBA editor get to this via

: where should i put this code Ivan

Re: Ivan ,ok thanks it works.. 1 more thing.lastly

sub workbook_open..i am assuming like is it possible

Use (excel97)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Enable_Tools
End Sub

Private Sub Workbook_Open()
Disable_Tools
End Sub

This code goes into the Thisworkbook excel object

Ivan

you're a bunch of VB heads, aren't you?

I'm impressed! I figured that it could be done with VB, but I didn't expect anyone to actually post the code. There's a manual way to remove the commands from the pull-down menu too, but I guess that's not what was asked? *sigh* It sucks being clueless about Visual Basic.

Here's a solution

John
Well, you asked for it - if you think it sucks, why don't you buy a book and learn it? Just like the "VB heads" did !!!
Celia

Yeah Celia. i thought this Rigali guy is a looser in his life

John Well, you asked for it - if you think it sucks, why don't you buy a book and learn it? Just like the "VB heads" did !!! Celia

I had no intention of suggesting anything like that.

Re: Yeah Celia. i thought this Rigali guy is a looser in his life

Angelo I don't think John meant this.......

*ahem*

I'm not trying to insult anyone here. I'm merely trying to be helpful where I can, and learn anything helpful that I don't already know. I would appreciate it if everyone here (Angelo) would do the same.

Close case.okay.just a communication breakdown..we all buddy right..cheers..no hard feelings.