PDA

View Full Version : [SOLVED] Removing the Toolbar Options/Customize function



seals
08-31-2005, 07:29 AM
Hi all,

I am hoping this would be relatively easy but I have searched around for a while with no luck.

I have created a custom menu and a custom toolbar in my excel sheet and I have managed to disable the right click so that users can't change the menus etc.

How do I disable the drop down list at the end of the toolbar (r/h) which gives the option of add or remove buttons, which in turn gives the pop up of customize? And of course you can delete the toolbars from that point.

Any help would be appreciated. http://vbaexpress.com/forum/images/smilies/100.gif

Seals

Marcster
08-31-2005, 08:30 AM
Hi Seals,

I'm been messing around with Excel and have found out the following:

The following code will disable the dropdown arrow, but Excel needs to
quit and re-open in order for it to take effect.
Tested on Excel 2000 (Version 9.0).



Sub DisableToolsCustomize()
Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")
wsh.RegWrite "HKCU\Software\Policies\Microsoft\Office\9.0\Excel\DisabledCmdBarItemsCheckB oxes\ToolsCustomize", "797", "REG_SZ"
'To reset
'wsh.RegWrite "HKCU\Software\Policies\Microsoft\Office\9.0\Excel\DisabledCmdBarItemsCheckB oxes\ToolsCustomize", "0", "REG_SZ"
End Sub


The following code disables the Customize menu item without the need to quit Excel but, the dropdown arrows still appear.



Sub DisableMenuItem()
Application.CommandBars("Worksheet menu bar").Controls("Tools").Controls("Customize...").Enabled = False
'To reset
'Application.CommandBars("Worksheet menu bar").Controls("Tools").Controls("Customize...").Enabled = True
End Sub


Marcster.

seals
08-31-2005, 08:46 AM
Marcster,

Thanks for your reply. I gave you're code a go and no luck I'm afraid, I am running the 2002 version.

I tried the following with my custom toolbar and nothing at all, well when I say nothing there were no errors at least. And the dropdown arrow still remains with the add or remove buttons and the customize... still enabled


Application.CommandBars("Orb_ Toolbar").Controls("Tools").Controls("Customize...").Enabled = False

I also tried this one but no luck either:


Application.CommandBars("Orb Toolbar").Protection = msoBarNoCustomize

Any other ideas?:doh:

Thanks again for you reply,

Seals

TonyJollans
08-31-2005, 09:01 AM
Hi Seals,

Who are you trying to stop? The average user who might get in a mess? Or the dedicated knowledgeable person?

You really can't stop the second and I think you're going too far in trying to stop the first.

What exactly are you trying to stop them doing?

seals
08-31-2005, 09:09 AM
Tony,

Realistically I am trying to stop the fiddle factor from the average user, I guess it would be nice to tidy the last little end up. So that they cant get in that mess in the first place.

The only other option I thought of just then was perhaps to code the toolbars and menus so that they are created each time the workbook is opened and deleted when it closes.

That way if the user deltes or changes a tool bar it wont matter quite so much????

Cheers,

Seals

Marcster
08-31-2005, 11:16 AM
Hi Seals http://vbaexpress.com/forum/images/smilies/039.gif ,

I've had another look into this (in Excel 2002 on Windows XP) and have come up with:

The following will prevent the toolbar 'Orb_Toolbar' from being re-named or further items added or removed onto it:

CommandBars("Orb_Toolbar").Protection = msoBarNoCustomize

This code will disable the 'Customize...' item:

Application.CommandBars.DisableCustomize = True

Also this code will prevent the toolbar being moved:

CommandBars("Orb_Toolbar").Protection = msoBarNoMove

Hope that helps,

Marcster.

seals
08-31-2005, 07:32 PM
Marcster,

That hit the spot. :bow::bow::bow::bow::bow::bow::bow::bow::bow::bow::bow:

It works like a treat, thank you so much. Let me know if I can help you out in the future.

Cheers,


Seals.
:beerchug::beerchug::beerchug::beerchug::beerchug::beerchug::beerchug::beer chug:

Marcster
09-01-2005, 01:29 AM
Hi Seals :hi: ,

Your welcome. Glad I could help out :yes .
When I come across another problem/query i'm having, i'll be posting
it on this site. :yes

:beerchug:

Marcster.

TonyJollans
09-01-2005, 04:33 AM
Thankyou Marcster, I didn't know about DisableCustomize.