Glaswegian
Well-known Member
- Joined
- Oct 14, 2003
- Messages
- 1,487
I've been reading some postings on shortening code length, but I'm not sure if I can shorten the following:
I need to make sure users have access only to selected menu bar/pop-up menu items. A small selection is below:
With Application
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Share Workbook...").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Protection").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Macro").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Options...").Enabled = False
CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete...").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Clear").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Page Setup...").Enabled = False
.CommandBars("Cell").Controls("Delete...").Enabled = False
.CommandBars("Cell").Controls("Insert...").Enabled = False
.CommandBars("Cell").Controls("Format Cells...").Enabled = False
Is there some other way - other than using 'with' to shorten this? Would 'case' be of use - although I've only used it once before!
Cheers.
I need to make sure users have access only to selected menu bar/pop-up menu items. A small selection is below:
With Application
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Share Workbook...").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Protection").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Macro").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls("Options...").Enabled = False
CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Delete...").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("Edit").Controls("Clear").Enabled = False
.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Page Setup...").Enabled = False
.CommandBars("Cell").Controls("Delete...").Enabled = False
.CommandBars("Cell").Controls("Insert...").Enabled = False
.CommandBars("Cell").Controls("Format Cells...").Enabled = False
Is there some other way - other than using 'with' to shorten this? Would 'case' be of use - although I've only used it once before!
Cheers.