Forum Discussion

tmotaung's avatar
tmotaung
Copper Contributor
Jan 27, 2025

Hide Report Sheet

Good day Experts, I have 5 sheets and would like to protect and hide my report sheet from 4 other agents because it is confidential. How can I prevent them from opening the report. I tried to set a password but it still shows the information.

 

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    I see you have Excel for Web noted so macros won't work.  That said, if YOU use that workbook in desktop YOU can unlock and unhide and hide and lock.  I don't think you even need to use macros:

    a) open in desktop app

    b) right click tab and select 'hide'

    c) choose Review menu and select 'Protect Workbook' and give password when prompted

    using 'Protect Workbook' will (by default) prevent others from changing the overall workbook settings like adding or deleting or hiding OR unhiding individual sheets (tabs).

    That said, this is NOT a means for truely confidential information as people with access to the workbook CAN circumvent this.  This is more like those small locks you put on luggage zippers that are really easy to pick but they keep honest but curious people out.

    In the web version you could just 'hide' the tab but that will have not protection so they could easily 'unhide' the tab.

    Alternatively you could move that information into a different workbook and not give them access.

    And yet another option is to use 'Link to this Sheet' to share each of the 5 sheets you do want to share but not the 6th sheet.

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    You can set password to see the sheet. Means, when any user will click on the report sheet then Excel will prompt for password. If users do not know the password, they can't see sheet contents. Use the following codes in SheetActivate event. See the attached file. To prevent viewing password from VBA window, you must lock VBA project.

     

        Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        Dim MySheetName As String
        
        MySheetName = "Report" 'The sheed which I want to hide.
        
        If Application.ActiveSheet.Name = MySheetName Then
            Application.EnableEvents = False
            Application.ActiveSheet.Visible = False
            response = Application.InputBox("Password", "Enter Password", "", Type:=2)
        
                If response = "123456" Then 'Unhide Password.
                    Application.Sheets(MySheetName).Visible = True
                    Application.Sheets(MySheetName).Select
                End If
        End If
        
        Application.Sheets(MySheetName).Visible = True
        Application.EnableEvents = True
        End Sub

     

    • tmotaung's avatar
      tmotaung
      Copper Contributor

      Good day, I am only using WEB and not VBA.

Resources