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, 

      • NikolinoDE's avatar
        NikolinoDE
        Platinum Contributor
        May 31, 2023

        ADumith 

        The code you provided appears to be free of syntax errors. However, please note that without the full context and the specific requirements of your Excel workbook, it is difficult to determine if the code will function as intended. There could be several reasons why the code is not executing properly.

        Here are a few things you can check:

        1. Macro Security Settings: Make sure that the macro security settings in Excel are not set to disable all macros. You can check this by going to the "File" menu, selecting "Options," then "Trust Center," and finally "Trust Center Settings." Under the "Macro Settings" category, choose the option that enables macros.
        2. Trusted Locations: Ensure that the location of the file is added to the trusted locations in Excel. This can also be found in the Trust Center settings mentioned above, under the "Trusted Locations" category.
        3. Trusted Documents: If the file was previously opened with macros disabled, Excel might have marked it as an untrusted document. In this case, you can try to remove the file from the "Trusted Documents" list. To do this, go to the Trust Center settings, select the "Trusted Documents" category, and remove the file from the list.
        4. Protected View: Check if the file is opening in "Protected View." In this mode, macros are disabled by default. You can see if the file is in protected view by looking for a yellow bar at the top of the Excel window. If the file is in protected view, you can click on the "Enable Editing" button to enable macros.
        5. Debugging: Add some debugging statements or breakpoints in your code to see if it is getting executed at all. For example, you can add MsgBox "Workbook_Open event triggered." at the beginning of the Workbook_Open sub to check if it runs when the file is opened.

        By checking these points, you should be able to identify the issue preventing the code from executing properly.

Resources