PDA

View Full Version : Solved: Changing to Button


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....
shamsam1
09-05-2008, 03:19 AM
r u using office 2003 or 2007
i dotn haev office 2003 installed in my machine
if ur using 2007 then go to developer then insert-then from active x control chosse button and draw on sheet.then chose design mode and double click button


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
delete code from here
End Sub

add ur code in this evet

Private Sub CommandButton1_Click()
ur code here
End Sub
.

last option attach ur work book here
Bob Phillips
09-05-2008, 03:31 AM
I thought I gave you this a couple of days ago



Public Sub myMacro()

Select Case Range("K10").Value ' or Select Case Activecell.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