My weekly Excel vba macro question

  Chris the Ancient 12:44 29 Nov 2003

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 2003

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 2003

... although you could try

CommandBars("ToolBar List").Enabled = False

  Chris the Ancient 13:58 29 Nov 2003

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 2003

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

  Chris the Ancient 14:03 29 Nov 2003

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 2003

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 2003

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.

Elsewhere on IDG sites

Samsung Galaxy S9 review

Wacom Cintiq Pro 24 and 32 review – hands-on

Best Mac video editors

Idées cadeaux pour geeks et tech addicts