Forum Discussion

ADumith's avatar
ADumith
Iron Contributor
May 10, 2023

Dashboard customization

Hello everyone,    I have a dashboard made in Excel, so I wonder if there is any way to: 1.- Set disable the ribbon when the user opens the file. 2.- Lock the file so that the user can only navig...
Charting
excel
office 365
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 10, 2023

ADumith 

You can add the following code to the ThisWorkbook object in the Visual Basic editor:

press "Alt + F11" to open the Visual Basic Editor and then insert the code in "ThisWorkbook".

Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub

This will hide the ribbon when the user opens the file. To show the ribbon again, the user can right-click on any of the tabs and select “Customize the Ribbon”.

 

As for locking the file so that the user can only navigate between pages and use filter controls, you can protect the workbook structure.

This will prevent users from adding, deleting, hiding, or renaming worksheets.

To do this, go to Review –> Protect Workbook and enter a password if desired.

 

 

Hope it helps you!

  • ADumith's avatar
    ADumith
    Iron Contributor
    May 11, 2023
    Hello NikolinoDE,

    Thank you so much.

    I was able to hide the ribbon but I can't get them back, so I wonder what tabs are you making reference?

    Thank you again.
    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      May 11, 2023

      ADumith 

      For close with open file

      Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub

       

      for open with closing file 

      Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub

       

      Insert this in ThisWorkbook and so you can have close the ribbon if you open the file and its is open again for the other files if you close the file.

      • ADumith's avatar
        ADumith
        Iron Contributor
        May 31, 2023

        Hello NikolinoDE 

         

        I have gone a little further, and have managed to put together this little code, however for some unknown reason when I open the file the code does not execute.

         

        I have enabled macro execution and it still does not work, any ideas?

         

        Option Explicit
Private Const Password As String = "My_Pass"
Private Sub Workbook_Open()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", False)"
    For Each Sheet In Worksheets
        Sheet.Visible = xlSheetVeryHidden
    Next Sheet
    If Not Application.EnableEvents Then
        MsgBox "Macros are disabled. To enable the modification, activate the macros and reopen the file.", vbExclamation
    End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"", True)"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not CheckPassword() Then
        Cancel = True
    End If
End Sub
Private Function CheckPassword() As Boolean
    Dim userInput As String
    On Error Resume Next
    userInput = InputBox("Enter the password to enable modification:", "Password")
    If Err.Number <> 0 Then
        CheckPassword = False
    ElseIf userInput = Password Then
        CheckPassword = True
    Else
        MsgBox "Wrong password. Modification is not allowed.", vbCritical
        CheckPassword = False
    End If
    On Error GoTo 0
End Function

         

        Thank you, 

Resources