How to Make an Excel 2010 File Appear as a Program with Vba

When programming an Excel-file with links between sheets and assorted macros for whatever is needed you may want the file to behave as a program so that users can not access the ribbon even by double-clicking on the top bar. Protect the code from being accessed by pressing alt-F8 or alt-F11. Make sure you can run the macro show_sheets_and_ribbon () from the opening sheet before continuing to step 3

Step 1: Code In "This Workbook"

Private Sub Workbook_Open()
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.ScreenUpdating = False
Application.DisplayFormulaBar = False
ActiveWindow.WindowState = xlMaximized
Application.DisplayFullScreen = True

Step 2: Code for Yourself as Admin for Easy Access to All Sheets

Sub show_sheets_and_ribbon ()
Application.WindowState = xlMaximized
Application.DisplayFullScreen = False
Dim Password As String
'change "password" to whatever....
Do Until Password = "password"
Password = InputBox("Admin only!! - Password required", "Password", "????????")
If Password = "" Then
Hide_sheets

Exit Sub

End If
Loop
Application.ScreenUpdating = False
Dim wsSheet As Worksheet

For Each wsSheet In ActiveWorkbook.Worksheets
wsSheet.Visible = xlSheetVisible
Next wsSheet
ActiveWindow.DisplayWorkbookTabs = True
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.ScreenUpdating = True
End Sub
Sub Hide_sheets()
'Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Worksheets
ws.Visible = True
Next

For Each ws In Worksheets
'providing your first sheet is "Menu" and thats where the control for this macro is.
If ws.Name <> "Menu" Then ws.Visible = False
Next
ActiveWindow.DisplayWorkbookTabs = False

Application.ScreenUpdating = True
Application.DisplayFullScreen = True
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

End Sub

Step 3: Code in All Subs for Changing Sheet

First line in all subs to change sheet:

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

Be the First to Comment

Share Your Thoughts

  • Hot
  • Latest