PDA

View Full Version : [SOLVED] Worksheet event does nothing



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

Bob Phillips
05-23-2012, 12:09 PM
You didn't specify a property of Target, so it defaults to value, and was testing Target.Value for $E$6, not Target.Address


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim txt As String
If Target.Address = "$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.Address = "$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

CatDaddy
05-23-2012, 12:54 PM
once again, you are the boss xld. i dont want to overstep any lines but i love you