PDA

View Full Version : Dynamic Sheets



thomas.szwed
08-27-2008, 07:10 AM
Hi,

I require some code to hide and unhide certain sheets in my workbook all depedant on a cell dropdown value that is chosen by a user. Pls take a look at the picture attached. It shows the dropdown list (which contains the names of all the sheets in my workbook) and all the sheets displayed at the bottom.

One sheet must always remain visible called 'INPUT'. This sheet contains the dropdown. So when you select the sheetname from the dropdown list it then displays this sheet along with 'INPUT'. If the user changes their selection then the sheet must change to reflect their decision....

Can anyone help me with some code here?

I have this so far but has not worked for me

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, False, False, False, False)
Case "New Malden - Manager": Call SheetVisibility(True, True, False, False, False, False, False)
Case "New Malden - Staff": Call SheetVisibility(True, False, True, False, True, True, True)
Case "New Malden - Manager": Call SheetVisibility(True, True, False, False, False, False, False)
Case "Sudbury Process": Call SheetVisibility(True, False, False, True, False, False, False)
Case "Sudbury StaffMan": Call SheetVisibility(True, False, False, False, True, False, False)
Case "Wisbech Process": Call SheetVisibility(True, False, False, False, False, True, False)
Case "Wisbech Process": Call SheetVisibility(True, False, False, False, False, False, 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, sh6 As Boolean, sh7 As Boolean)

Application.ScreenUpdating = False
Worksheets("INPUT").Visible = xlSheetVeryHidden
Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
If sh1 Then Worksheets("INPUT").Visible = xlSheetVisible
If sh2 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
If sh3 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
If sh4 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
If sh5 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
If sh6 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
If sh7 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function

Many Thanks

Bob Phillips
08-27-2008, 08:17 AM
You don't say in what way it is not working, but perhaps this is what you want



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(False, False, False, False, False, False)
Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False)
Case "New Malden - Staff": Call SheetVisibility(False, True, False, True, True, True)
Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False)
Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False)
Case "Sudbury StaffMan": Call SheetVisibility(False, False, False, True, False, False)
Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False)
Case "Wisbech Process": Call SheetVisibility(False, False, False, False, False, 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, sh6 As Boolean)

Application.ScreenUpdating = False
Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
If sh1 hen Worksheets("NewMaldenMan").Visible = xlSheetVisible
If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function

thomas.szwed
08-27-2008, 08:20 AM
In your response here, if 'SELECT' was chosen in the dropdown would all sheets disappear? Because thats not what i want, i need the INPUT sheet to be working at all times......

Thanks

Bob Phillips
08-27-2008, 08:42 AM
Why would INPUT disapper, I took out the code that touches that sheet.

thomas.szwed
08-28-2008, 05:30 AM
because it has FALSE x 6 in the first sub?

thomas.szwed
08-28-2008, 05:36 AM
The problems i have come across using your suggested code are as follows.

1. When you choose an option from the dropdown nothing happens. You have to click off and click on the cell a number of times to make the sheets change.

2. When selecting option NewMaldenStaff, 4 sheets appear instead of just the NewMaldenStaff one........

???

Any help would be great thanks....

thomas.szwed
08-28-2008, 05:41 AM
Point 2 has now been solved but i dont think the ON Select function is working on the sub as it requires you to click on and off the sub several times for it to work......?

Aussiebear
08-28-2008, 06:01 AM
Is there any reason for a double up in the Case Select for "New Malden - Manager" or is that just a typo?

thomas.szwed
09-03-2008, 06:09 AM
Its just a typo i think........

XLD - is there anyway the code below could change so that it is run under a button as opposed to clicking on a cell, as this is proving abit unprofessional.

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(False, False, False, False, False, False, False, False, False)
Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False, False, False, False)
Case "New Malden - Staff": Call SheetVisibility(False, True, False, False, False, False, False, False, False)
Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False, False, False, False)
Case "Sudbury Staff/Man": Call SheetVisibility(False, False, False, True, False, False, False, False, False)
Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False, False, False, False)
Case "Wisbech Staff/Man": Call SheetVisibility(False, False, False, False, False, True, False, False, False)
Case "Aintree Process": Call SheetVisibility(False, False, False, False, False, False, True, False, False)
Case "Aintree Staff/Man": Call SheetVisibility(False, False, False, False, False, False, False, True, False)
Case "Factory Grade 4+": Call SheetVisibility(False, False, False, False, False, False, False, False, 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, sh6 As Boolean, _
sh7 As Boolean, sh8 As Boolean, _
sh9 As Boolean)

Application.ScreenUpdating = False
Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
Worksheets("AintreeProcess").Visible = xlSheetVeryHidden
Worksheets("AintreeStaffMan").Visible = xlSheetVeryHidden
Worksheets("FactGrade4+").Visible = xlSheetVeryHidden
If sh1 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
If sh7 Then Worksheets("AintreeProcess").Visible = xlSheetVisible
If sh8 Then Worksheets("AintreeStaffMan").Visible = xlSheetVisible
If sh9 Then Worksheets("FactGrade4+").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function

Thanks

Bob Phillips
09-03-2008, 06:25 AM
Sure, assign the ProcessK10 procedure to your button



Public Sub ProcessK10()

Select Case Range("K10").Value

Case "SELECT": Call SheetVisibility(False, False, False, False, False, False, False, False, False)
Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False, False, False, False)
Case "New Malden - Staff": Call SheetVisibility(False, True, False, False, False, False, False, False, False)
Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False, False, False, False)
Case "Sudbury Staff/Man": Call SheetVisibility(False, False, False, True, False, False, False, False, False)
Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False, False, False, False)
Case "Wisbech Staff/Man": Call SheetVisibility(False, False, False, False, False, True, False, False, False)
Case "Aintree Process": Call SheetVisibility(False, False, False, False, False, False, True, False, False)
Case "Aintree Staff/Man": Call SheetVisibility(False, False, False, False, False, False, False, True, False)
Case "Factory Grade 4+": Call SheetVisibility(False, False, False, False, False, False, False, False, True)
End Select
End Sub


Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
sh3 As Boolean, sh4 As Boolean, _
sh5 As Boolean, sh6 As Boolean, _
sh7 As Boolean, sh8 As Boolean, _
sh9 As Boolean)

Application.ScreenUpdating = False
Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
Worksheets("AintreeProcess").Visible = xlSheetVeryHidden
Worksheets("AintreeStaffMan").Visible = xlSheetVeryHidden
Worksheets("FactGrade4+").Visible = xlSheetVeryHidden
If sh1 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
If sh7 Then Worksheets("AintreeProcess").Visible = xlSheetVisible
If sh8 Then Worksheets("AintreeStaffMan").Visible = xlSheetVisible
If sh9 Then Worksheets("FactGrade4+").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function