Hiding and Unhiding worksheets based on Drop Down list

So, what you're saying is you'd need to have this dropdown as a (potential custom) toolbar (because it has to be available with every sheet...). So why not just record some macros that do what you want, then assign those macros to toolbar buttons?

Recording macros:
http://www.officearticles.com/excel/record_a_macro_in_microsoft_excel.htm

Launching macros:
http://www.officearticles.com/misc/methods_to_run_a_macro_in_microsoft_office_applications.htm

Hope it helps!

Sure.

The Drop Down in question is on the 'Score Sheet' Worksheet in cell D1.

Depending on what is selected in the drop down, I want to hide all other '...Average' worksheets, only exposing the appropriate worksheet based on what is selected in cell D1.

There are already subs in place for this worksheet .  I'm thinking that it would be appropriate to add this functionality to the 'Worksheet_SelectionChange' sub, but wasn't sure.

Thanks.

Hi Dreamboat,
I know about recording macros, but wasn't sure where to incorporate this functionality in my existing code.

See attached in previous post of this thread.

I cant open the workbook...as whenever i try to open it my excel crashes..can you please update the same again...

Which version of Excel are you using?

2003

strange.  your the first person to report this problem as I've had multiple people use this spreadsheet.  Now I'm concerned as to what's causing this.

Can be possible..that while uploading the file got corrupted so if you update again..it will be resolved..

If this attachment doesn't work again, please take a look at a different posting that had an attachment as well:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23810671.html

I was not still able to download the file..but i made this macro...just add this into the score sheet module..and it will do what you want...I have assumed you have used validation in D1 and the sheet names and the list in the validation is exactly the same...

Saurabh....

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("D1")) Is Nothing Then Exit Sub
    If Trim(Range("d1").Value) = "" Then Exit Sub
    Dim ws As Worksheet, ws1 As Worksheet, X As String
    Set ws = ActiveSheet
    X = Range("D1").Value
 
    For Each ws1 In ActiveWorkbook.Worksheets
        If ws1.Name = ws.Name Or ws1.Name = X Then
            ws1.Visible = xlSheetVisible
        Else
            ws1.Visible = xlSheetVeryHidden
        End If
    Next ws1
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

This is close.  Now everything but the scoresheet is getting hidden.

The drop down list items are not identical to the worksheet tab names.  Just the first portion is the same.  Each worksheet name is followed by ' Average'.

It should not hide the scoresheet as it wont hide the sheet where you apply this..and can you give me a couple of sample sheet names which has average to look at since i cant open your workbook...

Drop Down List:

   Bremerton
   City2
   City3
   ...

Sheet name:

  Bremerton Average
  City2 Average
  City3 Average
  ...

Okay use this...

Saurabh...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("D1")) Is Nothing Then Exit Sub
    If Trim(Range("d1").Value) = "" Then Exit Sub
    Dim ws As Worksheet, ws1 As Worksheet, X As String
    Set ws = Sheets("Scoresheet")
    X = Range("D1").Value & " Average"
    
 
    For Each ws1 In ActiveWorkbook.Worksheets
        If ws1.Name = ws.Name Or ws1.Name = X Then
            ws1.Visible = xlSheetVisible
        Else
            ws1.Visible = xlSheetVeryHidden
        End If
    Next ws1
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Curious about the reasoning for the usage of "xlSheetVeryHidden".  Using that prevents users from 'unhiding' any other sheets.  That's a neat trick though.

I used that because this will make the sheet go very hidden..so incase if there is a curious user who wants to look under format-->sheet-->unhide..he wont find it there..even when the worksheet is there in your workbook and hidden...

That's a very handy trick.  I can certainly use that on other sheets.

saurabh726,

I realize that I was not clear with my original request.

I need to hide all tabs ending with " Average" other than the one that is chosen from the drop down.

The reason behind this is because there are other tabs that need to be displayed.

So as an example, I need:

Dropdown:

  City1 (selected)

Worksheets:

  Worksheet1 (visible)
  Worksheet2 (visible)
  Worksheet3 (visible)
  City1 Average (visible)
  City2 Average (hidden)
  City3 Average (hidden)
  City4 Average (hidden)

I'm currently getting:

Dropdown:

  City1 (selected)

Worksheets:

  Worksheet1 (hidden)
  Worksheet2 (hidden)
  Worksheet3 (hidden)
  City1 Average (visible)
  City2 Average (hidden)
  City3 Average (hidden)
  City4 Average (hidden)

Right now, everything (including the scoresheet) is getting hidden except for the worksheet that's selected from the drop down.in the drop down.

I can email you the working copy I have if you let me know where to send it.

Shad