Dashboard customization

Question

Hello everyone,&nbsp;&nbsp;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 navigate between pages and use the filter controls.&nbsp;Thank you in advance,

nikolinode · Answer

ADumith&nbsp;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 SubThis will hide the ribbon when the user opens the file.&nbsp;To show the ribbon again, the user can right-click on any of the tabs and select “Customize the Ribbon”.&nbsp;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 –&gt; Protect Workbook and enter a password if desired.&nbsp;&nbsp;Hope it helps you!

adumith · Answer

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 · Answer

ADumith&nbsp;For close with open filePrivate Sub Workbook_Open()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub&nbsp;for open with closing file&nbsp;Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub&nbsp;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 · Answer

Hello&nbsp;NikolinoDE&nbsp;&nbsp;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.&nbsp;I have enabled macro execution and it still does not work, any ideas?&nbsp;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 &lt;&gt; 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&nbsp;Thank you,&nbsp;

nikolinode · Answer

ADumith&nbsp;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: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.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.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.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.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.

Forum Discussion

Dashboard customization

Share

Resources