Macro to open only relevant 'password protected' tab

[krish2503]

I have a file with a 'Main' sheet containing a number of countries and a series of hidden worksheets, each worksheet representing a country shown on the 'Main' tab. Conditions are as follows:
1. Every time the file is opened, all tabs, except 'Main' should be hidden.
2. On clicking a country in the 'Main' tab, there should be a prompt for a password (to be provided centrally but would be different for each worksheet) and on correct entry, the relevant country tab should be opened.
3. None of the other tabs should be accessible (due to confidential nature of the data).
4. Centrally, I should have access to all worksheets without the need for entering passwords for each of the tabs.

I guess I could use the wsSheet.Visible = False/True to toggle between hiding and showing the sheets but am at a loss on how to make the password work. Any help will be very much appreciated.

Attached is a file with no data but with relevant tabs.

Merry Christmas and a Very Happy New Year 2016.

[Kamboj]

I have done for one sheet

[krish2503]

Thanks, Kambhoj...does it mean that I will have to have as many macros as I have Countries??

[Kamboj]

Yes, you can use many

[krish2503]

Hi, the macro seems to work fine. However it seems like the confidentiality of data is not certain as any user can still 'unhide' the hidden tabs. If I protect the workbook to disable the unhide option, the macro fails and gives a run time error '1004': Unable to set the Visible property of the Worksheet class", as it cannot go beyond the Sheets ("worksheet name"). Visible = True

Any help appreciated.

Thanks

[Kamboj]

if user give right password then you have to unprotect the sheet using
sheets("your sheet").unprotect password:="your password"
and again you can protect sheet using
sheets("your sheet").protect password:="your password"

[pipoliveira]

To completely hide your sheets, please add the following code on the Workbook_BeforeClose event, this will hide and protect all unprotected and visible sheets:

Please Login or Register  to view this content.

This way, the user will not be able to unhide any sheet and is not able to see the list of hidden sheets.
Hope it helps.

[krish2503]

Thanks, Pip for the tip...much appreciated.