VBA: Prevent user from exiting Full Screen Mode

[Jonerke]

Dear reader and helper,

I am trying to achieve the following in excel:

I’m making an excel application where the user logs in and is brought to a specific sheet designed for him only. In this sheet I keep a few fields unlocked where the user can upload a request through a macro. This all works fine but I want to restrict the user’s options to writing plane text and pushing a macro button. All other options I prefer to be disabled: e.g. save as, all the options (File – options – advanced), basically everything on the ribbons. There will be several people using this file and I don’t want to allow people to mess with the options. On this thread (http://www.mrexcel.com/forum/excel-questions/393306-options-menu.html) I found a nice way of hiding the ribbons and going to full screen mode:

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

The above code is great but I have one problem with it. The user can easily exit the Full Screen Mode by minimizing or resizing the window. After minimizing and maximizing again I arrive back in the normal mode: all ribbons are visible again and you can easily go to File – Options to mess around.

Therefore my questions: is there a reason to prevent the user from exiting the Full Screen Mode ?

Alternatively is there another way to prevent the user from messing with the options ? I once saw a file where the ribbons where greyed out. That would be great as well.

Best regards,
Jonerke

 

[dmt32]

Therefore my questions: is there a reason to prevent the user from exiting the Full Screen Mode ?

Jonerke

My Answer would be YES

The user may legitimately have another window open they are working on & if your application when opened prevents them switching back to it or locks out the ribbon or commandbars for earlier versions, they probably may decide not to load your application again.

My personal view would be that your application should whilst it is open, allow users to return to another open workbook with all functionality restored. Your attempts to limit access to certain functions will not prevent the most determined users defeating your efforts if that is their intent, but done correctly, should work ok for most.

Alternatively is there another way to prevent the user from messing with the options ?

Maybe - Not fully worked through but see if following helps:

Place following Code in the Thisworkbook Code Page

 Private Sub Workbook_WindowActivate(ByVal Wn As Window)    
  DisplayFullScreen True
End Sub


Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    DisplayFullScreen False
End Sub


Private Sub Workbook_WindowResize(ByVal Wn As Window)
With Application
    If .WindowState = xlNormal Then .WindowState = xlMaximized
End With
End Sub

Place this code in standard module

 Sub DisplayFullScreen(ByVal FullScreen As Boolean)    
   With Application
        If Val(.Version) > 12 Then
            'xl2007 >
            FullScreen = Not FullScreen
            .ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", " & FullScreen & ")"
        Else
            .DisplayFullScreen = FullScreen
        End If
        .DisplayFormulaBar = FullScreen
        .EnableCancelKey = Not FullScreen
    End With
End Sub

Code should set your application to FullScreen BUT allow users to minimize application if they want to switch to another window.

Code may need some adjustment but hopefully, give you some further ideas

Dave

 

[Jonerke]

Hi Dave!

I don't really understand your code but it works! I left out one piece however:

Private Sub Workbook_WindowResize(ByVal Wn As Window)
With Application
    If .WindowState = xlNormal Then .WindowState = xlMaximized
End With
End Sub

With this piece of code I could not resize or minimize the window. Everytime you press the minimize button it immediately maximizes it again and stays active. Same for the change button.
Without the above piece of code the minimizing and resizing works perfectly as the full screen is maintained when maximizing manually again.

Very nice because like this I don't have to change anything about the ribbons etc.

Many thanks!
Jonas

 

[dmt32]

Hi,
I did not fully work through all the code but thinking was with that event to only allow user to minimize or maximize the window but if it did not work for you & not an issue then no problem but glad rest of solution goes in right direction & helps you.

Dave

 

[mikerickson]

Dictator applications are really annoying.

1) Its easy for a user to get to your code. One click of "Disable macros" at start up and there your file is, open to their view.

2) Users want your code to work. If you put simple protection on things so that the clumsy fingers don't accidentally mess things up, users cooperate with a well written worksheet. They want your code to work, they don't want to damage it or make mistakes.

3) Malevolent people who want to damage or spy on things won't be stopped. Excel in not a secure platform in any way. If someone wants to get into your code, Google will show them a couple of dozen ways in the first page.

4) Dictator applications induce error. If the user has a file with data and wants to put it into the cells of your application, they can't simply copy/paste. They have to write down on a piece of paper and then retype, inducing error.

5) If someone has to ask (on this forum) how to prevent users from X, Y or Z, I wonder what provisions the asker has made to restore Excel if the workbook, Excel or Windows crashes in the middle of being used.