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.