Unhide a specific sheet using VBA

[Rays]

Hello Excel experts,

I have a spreadsheet that has 11 tabs named Main, Sheet1, Sheet2, Sheet3 and so on until Sheet10. The names of all sheets are saved on the sheet named main which has a drop down containing these names. All the sheets named Sheet1.....Sheet10 are hidden.
What I want to achieve is When i select the sheet name from the drop down (the one which I want to unhide) and then press an Activex command button the corresponding sheet should be unhidden.
I have attached the sample spreadsheet for your kind consideration. Can someone kindly help.

 

[Deepak]

Check with attached.

 

[shrivallabha]

Hi Rays,

Welcome to Chandoo. Org forums.

Any special reason to use VBA and not use Excel's built in functionality?

If you right click on the sheet tab and choose "unhide" then it opens a userform to choose the sheet you wish to unhide.

In case of VBA you will have to make extra effort for maintaining the data validation up to date with respect to
1. Number of sheets
2. And their sheet names

 

[Rays]

That worked like a charm ! Thank you very much !
Please can you explain what the -1 in the code Sheets(sh).Visible = -1, means ?

 

[Rays]

Hi Rays,

Welcome to Chandoo. Org forums.

Any special reason to use VBA and not use Excel's built in functionality?

If you right click on the sheet tab and choose "unhide" then it opens a userform to choose the sheet you wish to unhide.

In case of VBA you will have to make extra effort for maintaining the data validation up to date with respect to
1. Number of sheets
2. And their sheet names

Dear Shrivallabha,

This sheet is meant to be used by users who might / might not know this functionality of using the Excel in built functionality. That's why

Now your second question about Number of sheets and sheet names. The maximum number is always constant and so are the names.

Thank you very much your your interest and your time.

 

[Rays]

Check with attached.

Thank you very much Deepak ! That helped !

 

[Deepak]

That worked like a charm ! Thank you very much !
Please can you explain what the -1 in the code Sheets(sh).Visible = -1, means ?

http://msdn.microsoft.com/en-us/library/office/ff821673(v=office.15).aspx