Need help on activating the sheet on the Dropdown list value selected

[Sahadev Reddy]

Good Day,

I am in need of a help regaring the excel adn the scenario is as mentioned below.

a)Need to hide all the sheets except the first sheet say "Sheet1" when I open the workbook.
b)Cell A1 in Sheet1 will be the dropdown list with the sheetnames say Sheet2,Sheet3,sheet4,sheet5 and so on which were hidden
c)When I select the Sheet2 from dropdown Sheet2 should get unhide and showup as the next sheet in the workbook.
d)User should not be able to unhide any of the other sheets except by selecting a dropdown option which means only one sheet can be activated at once.

Regards,
Sahadeva

 

[Mike H..]

Have a look at the attached workbook

 

[Sahadev Reddy]

Hello Mike,

Thank you very much for your help on this.

However in the attached file everytime I select a different sheet it is getting added up to the work and every selected sheet is showing which I do not want to .
I just need only one sheet to be shown which is selected for the last time.

Say if I select sheet2 and again change to Sheet3 I should be able to see only sheet3 and not Sheet2 and Sheet3 both.

Also please advise on how to send the selected sheet by email using a button after the required information is filled in.

Thank you once again for your help on this.Have a wonderful time ahead!!!

Regards,
Sahadeva

 

[Mike H..]

Hi,

Right click the sheet tab of sheet1, view code and replace the code on the right with this version:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
If Target.Address = "$A$1" Then
Application.EnableEvents = False
For x = 2 To Worksheets.Count
  Sheets(x).Visible = xlVeryHidden
Next
Sheets(Target.Value).Visible = True
Application.EnableEvents = True
End If
End Sub

 

[Sahadev Reddy]

Hello Mike,

Sorry for the continuous questions, Attached is the file that I am referring to.

Sheet 1 has the selection option marked in Yellow, Column V has the List of Sheets which are part of the list selection
Workbook has all the sheets by the names in the list
I want to hide all the sheets and open the sheet selected in the Dropdown list only say Customer1, only CUstomer1 should be displayed nothing else.When I choose Customer2 only Customer2 should be activated.

Please do the needful.

Regards,
Sahadeva

 

[NARAYANK991]

Hi Sahadeva ,

See this file.

Narayan