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


Posted by Kurt on November 01, 2000 3:21 AM

tia

Posted by John Rigali on November 01, 2000 8:37 AM

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.

Posted by Tim Francis-Wright on November 01, 2000 9:13 AM

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

Posted by Kurt on November 01, 2000 9:34 AM

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 ?

Posted by Tim Francis-Wright on November 01, 2000 10:37 AM

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.

Posted by Ivan Moala on November 01, 2000 9:25 PM

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

Posted by angelo on November 01, 2000 11:39 PM

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

Posted by Ivan Moala on November 02, 2000 1:21 AM

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


Posted by angelo on November 02, 2000 3:38 AM

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

Posted by Ivan Moala on November 02, 2000 7:17 PM

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

Posted by John Rigali on November 02, 2000 11:32 PM

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.

Posted by Celia on November 03, 2000 5:58 AM

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

Posted by angelo on November 03, 2000 6:59 AM

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

Posted by Celia on November 03, 2000 7:09 AM

I had no intention of suggesting anything like that.

Posted by Ivan Moala on November 03, 2000 10:33 PM

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

Posted by Ivan Moala on November 03, 2000 10:34 PM

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

Posted by John Rigali on November 06, 2000 7:59 AM

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



Posted by Angelo on November 20, 2000 7:08 AM

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