Unhiding "veryhidden" tabs w/ VBA (and issues with worksheet protection)

[jmo92]

Hi all,

I am working on a workbook that will be sent out to various people to input some information. There are about 40 countries, each with their own tab that they need to fill out - however I would like each person/country to only be able to view and edit their own tab.

I have a tab called Intro, which will be only tab visible when each country receives the doc. There are 4 fields that they need to fill in on Intro (B3:B6). B5 is where they enter which country they are from, so that cell needs to trigger the country tab unhiding. I have the following right now in Sheet1(Intro), which is working:

Private Sub worksheet_change(ByVal target As Excel.Range)


    Dim ws As Worksheet
     
    For Each ws In Worksheets
        If ws.Name <> "Intro" And ws.Name <> Worksheets("Intro").Range("B5").Value Then
            ws.Visible = False
        End If
        If ws.Name = Worksheets("Intro").Range("B5").Value Then
            ws.Visible = True
        End If
    Next ws
End Sub

This works perfectly (the tab names match what they would enter into B5), but is missing 2 limitations I need.

1) I need to protect each country's sheet, as well as the Intro sheet so that they cannot edit anything except certain cells (B3:B6 on Intro, a different range of cells on the country sheets). When I protect all of the sheets then input a value in B5, I get a runtime error and it doesn't work. Any ideas on why this might be happening?

2) I need all hidden tabs to be very hidden, including all 40 country tabs and 2 other reference tabs, "Functions" and "Markets." I have the following in ThisWorkBook (I'm testing this all w/ just 2 of the 40 tabs there will eventually be, US and Brazil):

Sub WorkbookChange()


Worksheets("Functions").Visible = xlSheetVeryHidden
Worksheets("Markets").Visible = xlSheetVeryHidden
Worksheets("US").Visible = xlSheetVeryHidden
Worksheets("Brazil").Visible = xlSheetVeryHidden


End Sub

This works when nothing is in cell B5. However, as soon as I input a value in B5 and run the macro, all of the tabs become just Hidden instead of VeryHidden.

I'm pretty new at VBA so would really appreciate any help, thank you!

 

[V_Malkoti]

1. Before protecting your sheets, unlock the cells that you want to keep editable. You can do it manually by selecting those cells, and then unchecking Locked in Format Cells -> Protection.
Or with code:

Sheets("SheetName").Range("A1:E10").Locked = False

2. To fix this try changing your ws.visible lines in the code to assing xlSheetVeryHidden and xlSheetVisible instead of True and False.

 

[jmo92]

Thank you! Your tip for #2 worked perfectly.

However, on #1 - I have done that, manually unlocked the cells I need to keep editable. That works fine, the problem is when I enter something in B5 on Intro and the macros runs. The macro doesn't work and I get a runtime error box.

 

[V_Malkoti]

What is the error (number/message) you get and on which line of your code?

 

[jmo92]

So right after I protect all three sheets (Intro, US and Brazil) everything works fine. When I save, close and reopen is when the issue starts. I am able to run it once, on either US or Brazil, and it works, unhiding the correct sheet. Then, when I try to change the value in B5 to the other country, I get the following error message:
Run-time error '1004':
Method 'Visible' of object '_Worksheet' failed

And the line that is yellow below is highlighted:

Private Sub worksheet_change(ByVal target As Excel.Range)

    Dim ws As Worksheet
     
    For Each ws In Worksheets
        If ws.Name <> "Intro" And ws.Name <> Worksheets("Intro").Range("B5").Value Then
            ws.Visible = xlSheetVeryHidden
        End If
        If ws.Name = Worksheets("Intro").Range("B5").Value Then
            ws.Visible = xlSheetVisible
        End If
    Next ws
End Sub


<colgroup><col></colgroup><tbody>

</tbody>

Thanks for any help you can offer!

 

[jmo92]

So right after I protect all three sheets (Intro, US and Brazil) everything works fine. When I save, close and reopen is when the issue starts. I am able to run it once, on either US or Brazil, and it works, unhiding the correct sheet. Then, when I try to change the value in B5 to the other country, I get the following error message:
Run-time error '1004':
Method 'Visible' of object '_Worksheet' failed

And the line that is yellow below is highlighted:

Thanks for any help you can offer!

Oops, code not working...

Private Sub worksheet_change(ByVal target As Excel.Range)

    Dim ws As Worksheet
     
    For Each ws In Worksheets
        If ws.Name <> "Intro" And ws.Name <> Worksheets("Intro").Range("B5").Value Then
            ws.Visible = xlSheetVeryHidden
        End If
        If ws.Name = Worksheets("Intro").Range("B5").Value Then
            ws.Visible = xlSheetVisible
        End If
    Next ws
End Sub

 

[V_Malkoti]

I'm not entirely sure but it sounds like the code is trying to hide the last visible sheet, which generates this error. Can you try changing the code a bit as following and see if you still get the error?

Private Sub worksheet_change(ByVal target As Excel.Range)
    Dim ws As Worksheet
    
    If Intersect(Worksheets("Intro").Range("B5"), target) Is Nothing Then Exit Sub
    
    For Each ws In Worksheets
        Select Case UCase(ws.Name)
            Case "INTRO", UCase(Worksheets("Intro").Range("B5").Value):
                ws.Visible = xlSheetVisible
            Case Else
                ws.Visible = xlSheetVeryHidden
        End Select
    Next ws
End Sub

 

[jmo92]

No luck

I actually forgot to mention something before, when I try to change the value of B5 to the other country, let's say Brazil (after US worked fine), nothing happens at all at first - the US tab remains open. I get the error when I click on anything else in the toolbar (and I also get prompted for my password at that time).

 

[V_Malkoti]

You mean that you get that error ('Visible' method failed) when you click on toolbar after changing value in Intro!B5? And if you are getting prompted for password then it is related to worksheet protection.

I tried simulating this with a workbook and I can't get my code to throw this error. Which makes me think that there is either some other code, setting or object causing this issue. Will it be possible for you to post a copy of your file (without any confidential data, of course) so that I can take a look?

 

[jmo92]

Sure, here is a (very cleansed) version at the link below (couldn't figure out how to attach).
https://www.dropbox.com/s/0afnwclej7aqm9k/VHidden Tab Excel.xlsm?dl=0

Thanks so much for your help!