VBA Hide/Unhide individual sheets based on selection in drop down menu

Hello:

I have seem many similar questions to mine while googling, however I am yet to come up with a suitable solution for my situation. I have a physical training program template that supports up to 20 athletes. I have a dropdown list with all of the athletes on a sheet, say Sheet1. What I want to be able to do is select the athlete from the drop down list, which well systematically 'unhide' that athletes information sheet. Also, when I select a different athlete's name i would like their sheet to become unhidden and the old athlete's page to become unhidden.

I have come up with a code that allows the selected athlete to become unhidden, but they do not become 're-hidden', if you will, when i select a different athlete. The code that I'm using to unhide the sheet when it is selected is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D1" Then Sheets(Target.Value).Visible = xlSheetVisible
End Sub

Also, it would be extremely inefficient to individually code each sheet to open/close when they're particular name is selected from the dropdown list. The athlete lists are lists that will be continually updated and it would not be reasonable to recode the sheet everytime a new athlete is added to the program.

Thanks in advance for your help!

Ian.


EDIT: Cell "D1" is the location of the dropdown list.

Hello and welcome to the forum,

Here's how I would do it :

Please Login or Register  to view this content.

Hi,
Just edit the code I provide and list the sheets you want to keep here:

Change :

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.