My weekly Excel vba macro question

  Chris the Ancient 12:44 29 Nov 03

Getting to be a habit is this.

Not an earth-shattering macro question - more one of curiosity! But nice if I cold do it.

Is there any way of generating a macro in Excel that will stop the user right-clicking a toolbar?

I ask because my workbooks have custom toolbars, and I'd like to leave them that way with no-one fiddling!


  VoG II 13:23 29 Nov 03

You can protect your toolbar using

MYBAR.Protection = msoBarNoMove + msoBarNoCustomize + msoBarNoChangeDock + msoBarNoChangeVisible

I don't know of a way of stopping users right clicking on it but the above should stop them being able to fiddle.

  VoG II 13:58 29 Nov 03

... although you could try

CommandBars("ToolBar List").Enabled = False

  Chris the Ancient 13:58 29 Nov 03

Had a play with that one. Once you'd given me that gen, THEN I managed to find that ion the help.

Great routine. But... not quite what I wanted. Must learn to make myself clearer.

The thing I want the user to be able to NOT do is call up any other toolbars. Mine is plenty good enough for them! LOL


  Chris the Ancient 13:59 29 Nov 03

The simultaneous reply strike again. I'll try this one!

  Chris the Ancient 14:03 29 Nov 03

Once again the venerable VoG has cracked it!

One day, I'll get him ;o)))))

Again, Sir, your a gentleman and I really appreciate your help.



Which is the correct order of box ticking, button pressing and message writing to really resolve the thread?

  pc moron 14:13 29 Nov 03

The above with some/all of the following will provide a reasonable level of protection for Excel's menus and toolbars.

To enable/disable the menu that appears when you right click a toolbar use:

CommandBars("Toolbar List").Enabled = False

CommandBars("Toolbar List").Enabled = True

To enable/disable the main default menu bar use:

CommandBars("Worksheet Menu Bar").Enabled = False

CommandBars("Worksheet Menu Bar").Enabled = True

To enable/disable a particular menu on the default menu bar (Tools Menu in this case) use:

CommandBars("WorksheetMenuBar").Controls("Tools").Enabled = False

CommandBars("Worksheet Menu Bar").Controls("Tools").Enabled = True

  Chris the Ancient 14:17 29 Nov 03

Thanks a lot for those extras!

Methinks this thread will be a printed one in my book of useful thingies rather than just having it 'somewhere' in "my postings".


This thread is now locked and can not be replied to.

Hands-on: Samsung Galaxy S8 review

1995-2015: How technology has changed the world in 20 years

Samsung's beautifully designed Galaxy S8 makes for better VR experiences too

47 iPhone camera tips to help you take better photos