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]

I don't get any error on changing values in Intro!B5 in this file. Everything seems to be working normally. Opened and closed files a bunch of times but that didn't make any difference either.