CatDaddy
05-23-2012, 11:09 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim txt As String
If Target = "$E$6" Then
For i = 2 To ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets(i).Visible = xlSheetVeryHidden
Next i
Select Case Target.Text
Case "CASH NO SERVICE":
ThisWorkbook.Sheets("CASH NO SERVICE").Visible = xlSheetVisible
Case "CASH WITH SERVICE":
ThisWorkbook.Sheets("CASH WITH SERVICE").Visible = xlSheetVisible
Case "FMV LEASE PAYMENT COMBINED WITH SERVICE PAYMENT":
ThisWorkbook.Sheets("FMV LEASE WITH COMBINED SERVICE").Visible = xlSheetVisible
ThisWorkbook.Sheets("FMV LEASE").Visible = xlSheetVisible
Case "FMV LEASE PAYMENT WITH SERVICE PAYMENT SEPARATE":
ThisWorkbook.Sheets("FMV LEASE-SEPARATE SERVICE").Visible = xlSheetVisible
ThisWorkbook.Sheets("FMV LEASE").Visible = xlSheetVisible
ThisWorkbook.Sheets("PROD SHED FMV").Visible = xlSheetVisible
Case "IMP":
ThisWorkbook.Sheets("IMP").Visible = xlSheetVisible
ThisWorkbook.Sheets("IM AMENDMENT").Visible = xlSheetVisible
ThisWorkbook.Sheets("PROD SHED IMP").Visible = xlSheetVisible
ThisWorkbook.Sheets("PS-IT SERVICES ONLY").Visible = xlSheetVisible
Case "SERVICE ONLY":
ThisWorkbook.Sheets("SERVICE ONLY").Visible = xlSheetVisible
ThisWorkbook.Sheets("MMSA").Visible = xlSheetVisible
Case "PS/IT SERVICE ONLY":
ThisWorkbook.Sheets("PS-IT SERVICES ONLY").Visible = xlSheetVisible
End Select
End If
If Target = "$E$12" Then
txt = ThisWorkbook.Sheets(1).Range("E6").Text
If Target.Text = "YES" And (txt = "FMV LEASE PAYMENT COMBINED WITH SERVICE PAYMENT" _
Or txt = "FMV LEASE PAYMENT WITH SERVICE PAYMENT SEPARATE") Then
ThisWorkbook.Sheets("CONTINGENCY").Visible = xlSheetVisible
End If
End If
End Sub
Should be hiding and unhiding sheets based on the selection made in cell e6 or e12, not sure why its not working
Dim i As Integer
Dim txt As String
If Target = "$E$6" Then
For i = 2 To ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets(i).Visible = xlSheetVeryHidden
Next i
Select Case Target.Text
Case "CASH NO SERVICE":
ThisWorkbook.Sheets("CASH NO SERVICE").Visible = xlSheetVisible
Case "CASH WITH SERVICE":
ThisWorkbook.Sheets("CASH WITH SERVICE").Visible = xlSheetVisible
Case "FMV LEASE PAYMENT COMBINED WITH SERVICE PAYMENT":
ThisWorkbook.Sheets("FMV LEASE WITH COMBINED SERVICE").Visible = xlSheetVisible
ThisWorkbook.Sheets("FMV LEASE").Visible = xlSheetVisible
Case "FMV LEASE PAYMENT WITH SERVICE PAYMENT SEPARATE":
ThisWorkbook.Sheets("FMV LEASE-SEPARATE SERVICE").Visible = xlSheetVisible
ThisWorkbook.Sheets("FMV LEASE").Visible = xlSheetVisible
ThisWorkbook.Sheets("PROD SHED FMV").Visible = xlSheetVisible
Case "IMP":
ThisWorkbook.Sheets("IMP").Visible = xlSheetVisible
ThisWorkbook.Sheets("IM AMENDMENT").Visible = xlSheetVisible
ThisWorkbook.Sheets("PROD SHED IMP").Visible = xlSheetVisible
ThisWorkbook.Sheets("PS-IT SERVICES ONLY").Visible = xlSheetVisible
Case "SERVICE ONLY":
ThisWorkbook.Sheets("SERVICE ONLY").Visible = xlSheetVisible
ThisWorkbook.Sheets("MMSA").Visible = xlSheetVisible
Case "PS/IT SERVICE ONLY":
ThisWorkbook.Sheets("PS-IT SERVICES ONLY").Visible = xlSheetVisible
End Select
End If
If Target = "$E$12" Then
txt = ThisWorkbook.Sheets(1).Range("E6").Text
If Target.Text = "YES" And (txt = "FMV LEASE PAYMENT COMBINED WITH SERVICE PAYMENT" _
Or txt = "FMV LEASE PAYMENT WITH SERVICE PAYMENT SEPARATE") Then
ThisWorkbook.Sheets("CONTINGENCY").Visible = xlSheetVisible
End If
End If
End Sub
Should be hiding and unhiding sheets based on the selection made in cell e6 or e12, not sure why its not working