VBA Macro to show/hide hidden worksheets using drop down list

[Tillybee]

I have a large excel document with 23 sheets, 16 of which MUST remain visible at all times.

7 worksheets (my classnames - Reception, Year 1, Year 2, Year 3, Year 4, Year 5, Year 6) are to be hidden until selected from a drop down list currently on sheet 22. The drop down list is also active on each of the above 7 classname worksheets. On each sheet it is active on it is in the same cell, D3

Another sheet is hidden as it simply contains my values for the data validation used to create the drop down list.

Ideally I would like to be able to move between the 7 hidden classname worksheets named above rather than having to return to sheet 22 to select the chosen worksheet each time.

I have managed to get this working using the code

Private Sub Worksheet_Change(ByVal Target As Range)

'Change from A1 to the cell containing your languages drop-down
If Target.Address(0, 0) = "D4" And Len(Target.Value) > 0 Then
With Sheets(Target.Text)
.Visible = xlSheetVisible 'Unhide the chosen worksheet
Application.Goto .Range("A1") 'Go to the chosen worksheet, cell A1
End With
End If

End Sub


However, I ONLY want ONE of these 7 classname worksheets open at any one time i.e. when you select a different classname any unhidden classname worksheets become hidden again.

E.g. I click on Year 2 in the drop down box and the Year 2 worksheet becomes visible. If I then select Year 5 in the dropdown box (either on the Year 2 sheet or on sheet 22) I want the Year 2 sheet to become hidden again. At the moment it does not and remains visible alongside the newly visible Year 5 sheet.

I hope that makes sense!!!

I have spent the last 6 hours trying every permutation I can think of with no luck - once a worksheet is visible it remains so!

Unfortunately in the process of discovering the above code works to an extent I seem to have lost all my other versions too (how do I avoid this in future - I thought they'd saved when I named them!!??)

Very many thanks to anyone who can solve this for me as its now 2am and I am going a little crazy on this!

xxx

[ajryan88]

Hi,

Try placing this code in the "ThisWorkbook" module:

Please Login or Register  to view this content.

See the attachment to see the code in action. You will have to add/change some values to make this work in your workbook, but hopefully you get the idea.

Hope this helps

[Tillybee]

Very many thanks!!

Your version in the attachment works perfectly. However when I put it in mine it comes up with an error which I've marked in red and bold (I hope)

Sheet 22 is now "Children's Levels" and D3 is now D4

Please Login or Register  to view this content.

Also, is there any way of removing the "Children's Levels' sheet (sheet 22) and just moving between the class name sheets?

Many thanks again and sorry for being such a vba dunce!

[Tillybee]

I've attached the file I'm working on... I've had to remove most of the unhidden sheets as they contain confidential information but have kept the main sheet where names are inputted and the ones I want to be able to change between. (I hope that makes sense!!)

Change worsksheet science tracking document.xlsm

[Tillybee]

Anyone able to shed any light on this please?

[ajryan88]

Hi Tillybee,

In your code that you posted 3 posts ago, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That should fix the error issue.

Hope this helps

[JakeMills]

This is a very similar problem to the one I am facing, and the code seems to be working for the most part in doing what I'm trying to do.

However, in the file that has been attached (and similarly on mine), when the sheet is edited at all elsewhere (outside of D3), the value in D3 is changed to whatever is entered into that box.

This is most likely just a minor mistake in the coding but I can't seem to work out how to fix it as a beginner.

If you have any tips or advice on how to fix this, it would be greatly appreciated.