Unhide sheets according to cell value RRS feed

  • Question

  • Hi,

    I have a report which is divided in 6 access distinct sections, I would like to be able to unhide a group of sheets according to a security priority. i.e. for cell contents "General" it would unhide Sheet1, Sheet2 and Sheet3, for cell contents "Level1" then Sheet4 to Sheet6, would be the sheets unhiden...

    Just a small note:

    The VB code will be protected with a password

    Is there any way that could be done?

    Thanks for the help,

    • Edited by vdafonse Wednesday, February 27, 2013 3:04 PM add detail
    Wednesday, February 27, 2013 3:02 PM

All replies

  • As far I understand, you have workbook with protected sheets, and you want to make navigate to sheets...

    If you have VBA locked then you should use separate addin.

    Take look on this: Move between worksheets active workbook Add-in

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, February 27, 2013 3:23 PM
  • Hi,

    I think I managed to get something... But I am stuck in one part of the code, as I am "pulling" the USERNAME and if the same don't exist it will give me an error, any idea how can I bypass the error?

    the code I am using is... which seems to work while the VB is protected!!


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' to open the requires sheets within the choosen group
        Application.ScreenUpdating = False
        If Target.Address <> "J77" Then
            With Range("J77")
                If .Value = "Advisor" Then
                    Worksheets("Split1").Visible = True
                    Worksheets("Sheet3").Visible = True
                    Worksheets("Sheet4").Visible = True
                    Worksheets("Split2").Visible = False
                    Worksheets("Split3").Visible = False

    ElseIf .Value = "Complaints Advisor" Then
                    Worksheets("Split1").Visible = True
                    Worksheets("List").Visible = False
                    Worksheets("CELData").Visible = False
                    Worksheets("CEMData").Visible = False
                    Worksheets("ADVData").Visible = True
                    Worksheets("ADVHist").Visible = True
                    Worksheets("CELHist").Visible = True

                End If
            End With
        End If
    End Sub

    Thanks for the help,

    Wednesday, February 27, 2013 3:40 PM
  • Use this function:

    Private Function SheetExists(sname) As Boolean
    '   Returns TRUE if sheet exists in the active workbook
        Dim x As Object
        On Error Resume Next
        Set x = ActiveWorkbook.Sheets(sname)
        If Err = 0 Then SheetExists = True _
            Else SheetExists = False
    End Function

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, February 27, 2013 3:51 PM