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:
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):
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!
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:
Code:
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
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):
Code:
Sub WorkbookChange()
Worksheets("Functions").Visible = xlSheetVeryHidden
Worksheets("Markets").Visible = xlSheetVeryHidden
Worksheets("US").Visible = xlSheetVeryHidden
Worksheets("Brazil").Visible = xlSheetVeryHidden
End Sub
I'm pretty new at VBA so would really appreciate any help, thank you!