"Options Menu"

[MarkDShark]

Is there a way to disable or hide the options menu so no one can change anything on a spreadsheet? I hide the scroll bars, tabs etc, but don't want any user to unhide them through the options menu. Even though the sheets are pass protected and they cannt view formulas or what not, a user can still go up to options and check the boxes in the options menu and add back the tabs and scroll bars and whatever else. I want to be able to lock those options out as well. I want whats hidden to remain hidden.

 

[TheNoocH]

this is what I used in the past

Sub HideExcelStuff()
'Hide All Background Excel Stuff
   With ActiveWindow
     .DisplayHeadings = False                           'Hides Row/Column Headings
     .DisplayHorizontalScrollBar = False                'Hides Horizontal Scroll Bar
     .DisplayVerticalScrollBar = False                  'Hides Vertical Scroll Bar
     .DisplayWorkbookTabs = False                       'Hides Workbook Tabs
   End With
   ActiveSheet.DisplayPageBreaks = False
   With Application
     .DisplayFullScreen = True                           'Sets Excel to Display Full Screen
     .DisplayFormulaBar = False                          'Hides Excel formula bar and toolbars
     .DisplayStatusBar = False                           'Hides Excel Status Bar
     .CommandBars("Worksheet Menu Bar").Enabled = False  'Hides Excel Menus
     .CommandBars("Full Screen").Visible = False         'Hides Full Screen Toolbar
     .CommandBars("Standard").Visible = False            'Hides Standard Toolbar
     .CommandBars("Formatting").Visible = False          'Hides Formatting Toolbar
     .EnableCancelKey = xlDisabled                       'Disable CTRL-BREAK
   End With
   
End Sub

Sub UnHideExcelStuff()
  'Turn Screen changes off
   Application.ScreenUpdating = False

  'UnHide All Background Excel Stuff
   With ActiveWindow
     .DisplayHeadings = True                            'UnHides Row/Column Headings
     .DisplayHorizontalScrollBar = True                 'UnHides Horizontal Scroll Bar
     .DisplayVerticalScrollBar = True                   'UnHides Vertical Scroll Bar
     .DisplayWorkbookTabs = True                        'UnHides Workbook Tabs
   End With
   ActiveSheet.DisplayPageBreaks = True
   With Application
     .DisplayFullScreen = False                          'Sets Excel to Disable Full Screen
     .DisplayFormulaBar = True                           'UnHides Excel formula bar and toolbars
     .DisplayStatusBar = True                            'UnHides Excel Status Bar
     .CommandBars("Worksheet Menu Bar").Enabled = True   'UnHides Excel Menus
     .CommandBars("Standard").Visible = True             'Hides Standard Toolbar
     .CommandBars("Formatting").Visible = True           'Hides Formatting Toolbar
   End With
   Application.ScreenUpdating = True
End Sub

 

[Smitty]

You can hide all of the command bars, but you need to make sure to reset them before closing or else you'll have some very aggravated users:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> HideCommandBars()<br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> CommandBar<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> x <SPAN style="color:#00007F">In</SPAN> Application.CommandBars<br>            x.Enabled = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> x<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[MarkDShark]

Well once the spreadsheet is complete there would be no need for anyone to change its appearance for any reason. All editing command bars or options should not really be a choice for them. My goal is to have the sheet operational but not changable in anyway. If anything needs to be changed then i would be the one to make those changes in all regards to how it looks or operates. Thank you all for you help.

 

[Greg Truby]

What you are describing is commonly known as a dictator application and you really need to be a pretty good (read professional) coder before trying this. I would strongly urge you to experiment with various built-in protection settings before attempting to assume complete control of the user interface. With a dictator app you have to force macros to be enabled. If your project is unsigned and the users security is high, that's a problem right there.

Even if you do something as drastic as what Smitty demonstrates, do you know how to block me from hitting Ctrl+0 and then Ctrl++ to insert a column? Or Ctrl+- to delete one? [Protection, as you appear to already know, handles this effortlessly.]

 

[MarkDShark]

Well most users shouldn't use those options or consider them since the spreadsheet would be an offical user doc that would have restrictions on it by default. Any changes would have to go through official sources to have them made. So basically even if they know how to use those options, they really shouldn't. But there's always those that try.