Shortening VBA Code

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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
This is from the VBA cd... It basically takes out everything, I think.
Anyway, it may be a point to start from :

Code:
'Place the code below into the worksheet module
Private Sub Workbook_Open()
'    MsgBox "Alles ausblenden"
'    Call AusBlenden
'    MsgBox "Alles einblenden"
'    Call EinBlenden
End Sub

 'Place the code below into the standard module
Sub OutOfscreen()
    CommandBars("Worksheet Menu Bar").Enabled = False
    Application.DisplayFullScreen = True
End Sub

Sub Onscreen()
    ActiveWorkbook.Unprotect
    CommandBars("Worksheet Menu Bar").Enabled = True
    Application.DisplayFullScreen = False
End Sub

Sub Auto_Open()
    MsgBox "All out screen"
    Call OutOfscreen
    MsgBox "All from screen"
    Call Onscreen
End Sub

It has some separate components that you can either modify or exclude.


(y)
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
You're really concerned that someone will take the workbook into shared mode?

Realize, none of this dummy proof, Excel's very insecure.

Perhaps kill the whole thing and create your own interface via buttons.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,876
Messages
5,766,875
Members
425,383
Latest member
IllDo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top