thomas.szwed
09-05-2008, 02:50 AM
Hi,
I have the following command which runs when a cell is changed or clicked on.
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
This is running on 'selectionchange'
What code could i use to make this run with a button?
So for instance a user makes a selection in a cell, then presses the button to run the above code?
Thanks for any help....
I have the following command which runs when a cell is changed or clicked on.
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
This is running on 'selectionchange'
What code could i use to make this run with a button?
So for instance a user makes a selection in a cell, then presses the button to run the above code?
Thanks for any help....