Solved: VBA to hide unrequired sheets?

[thomas.szwed]

Hi there.

I am looking for some code that will hide sheets dynamically depending on a Cell dropdown or Form Dropdown tool on my main excel worksheet.

In my workbook i have one sheet which acts as a data input sheet, and then many other sheets that are all different in certain ways, but essentially pull in data from my main data input sheet.

What i therefore need is some code that when you choose an option from the Dropdown List hides all the unrelated sheets and just keeps the sheet required at the bottom the screen (where all the sheet tabs are).

Could anyone tell me if this is possible and how?

Many Thanks....:clap:

[Bob Phillips]

Maybe you can work with this. It goes in the sheet module where th dropdown is



Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Me.Range("A2")) Is Nothing Then

Select Case Target.Value

Case "ABC": Call SheetVisibility(True, False, True, True, False)
Case "XYZ": Call SheetVisibility(False, False, False, True, False)
Case "MNO": Call SheetVisibility(True, True, True, True, True)
End Select
End If
End Sub


Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
sh3 As Boolean, sh4 As Boolean, _
sh5 As Boolean)

Application.ScreenUpdating = False
Worksheets("Sheet1").Visible = xlSheetVeryHidden
Worksheets("Sheet2").Visible = xlSheetVeryHidden
Worksheets("Options").Visible = xlSheetVeryHidden
Worksheets("Algo").Visible = xlSheetVeryHidden
Worksheets("Mitre").Visible = xlSheetVeryHidden
If sh1 Then Worksheets("Sheet1").Visible = xlSheetVisible
If sh2 Then Worksheets("Sheet2").Visible = xlSheetVisible
If sh3 Then Worksheets("Options").Visible = xlSheetVisible
If sh4 Then Worksheets("Algo").Visible = xlSheetVisible
If sh5 Then Worksheets("Mitre").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function

[thomas.szwed]

This is what i have so far but it doesnt seem to be doing anything.....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Me.Range("K10")) Is Nothing Then

Select Case Range("K10").Value

Case "SELECT": Call SheetVisibility(True, False, False)
Case "New Malden - Staff": Call SheetVisibility(True, False, True)
Case "New Malden - Manager": Call SheetVisibility(True, True, False)

End Select
End If
End Sub


Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
sh3 As Boolean)

Application.ScreenUpdating = False
Worksheets("INPUT").Visible = xlSheetVeryHidden
Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
If sh1 Then Worksheets("INPUT").Visible = xlSheetVisible
If sh2 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
If sh3 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function

[thomas.szwed]

Hi XLD and thanks for looking into this.

Can you tell me where i am going wrong?