thomas.szwed
09-03-2008, 03:20 AM
Hi,
I have a fairly tricky problem. Imagine my workbook contains 5 sheets, so 'Sheet1' through to 'Sheet5'.
Sheet 1 is ALWAYS displayed and is the master sheet. It contains lots of input boxes. Sheets 2 - 5 are hidden. They display information from the master sheet.
On Sheet 1 there is a cell dropdown that contains the names of Sheets 2 - 5. When you select a sheet from there, the worksheet tab appears at the bottom along side Sheet 1.
Say i wanted to print just the sheet that the user selected, how would i code a button to do that?
How would it know which sheet to print? Would it be through some sort of link to the dropdown list options?
What i have explained above is an example to try and make you understand. Below is the code i am currently using and there are more sheets here as this is the real version ('SELECT' is the name given to my master sheet). Maybe this code may help.
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, 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 if anyone can help me out....:friends:
I have a fairly tricky problem. Imagine my workbook contains 5 sheets, so 'Sheet1' through to 'Sheet5'.
Sheet 1 is ALWAYS displayed and is the master sheet. It contains lots of input boxes. Sheets 2 - 5 are hidden. They display information from the master sheet.
On Sheet 1 there is a cell dropdown that contains the names of Sheets 2 - 5. When you select a sheet from there, the worksheet tab appears at the bottom along side Sheet 1.
Say i wanted to print just the sheet that the user selected, how would i code a button to do that?
How would it know which sheet to print? Would it be through some sort of link to the dropdown list options?
What i have explained above is an example to try and make you understand. Below is the code i am currently using and there are more sheets here as this is the real version ('SELECT' is the name given to my master sheet). Maybe this code may help.
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, 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 if anyone can help me out....:friends: