Change Event in Excel

[laurenbattaglia]

I am trying to use Change Event to hide or unhide rows based on user input to a given cell. Here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False


Windows("Deal_checklists_v6_1.xls").Activate
Sheets("Deal Reg. Overall").Select


'Future Purchasing Terms
If Target = Range("B29") And Target = "Y" Or Target = "y" Then
Rows("31:36").Select
Selection.EntireRow.Hidden = False 'Unhide Row

ElseIf Target = Range("B29") And Target = "N" Or Target = "n" Then
Rows("31:36").Select
Selection.EntireRow.Hidden = True 'Hide Row

'License Information
ElseIf Target = Range("B39") And Target = "Y" Or Target = "y" Then
Rows("41:45").Select
Selection.EntireRow.Hidden = False 'Unhide Row

ElseIf Target = Range("B39") And Target = "N" Or Target = "n" Then
Rows("41:45").Select
Selection.EntireRow.Hidden = True 'Hide Row

'ELA
ElseIf Target = Range("B47") And Target = "Y" Or Target = "y" Then
Rows("49:59").Select
Selection.EntireRow.Hidden = False 'Unhide Row

ElseIf Target = Range("B47") And Target = "N" Or Target = "n" Then
Rows("49:59").Select
Selection.EntireRow.Hidden = True 'Hide Row

'Term License
ElseIf Target = Range("B61") And Target = "Y" Or Target = "y" Then
Rows("63:69").Select
Selection.EntireRow.Hidden = False 'Unhide Row

ElseIf Target = Range("B61") And Target = "N" Or Target = "n" Then
Rows("63:69").Select
Selection.EntireRow.Hidden = True 'Hide Row

'Support Information
ElseIf Target = Range("B71") And Target = "Y" Or Target = "y" Then
Rows("73:84").Select
Selection.EntireRow.Hidden = False 'Unhide Row

ElseIf Target = Range("B71") And Target = "N" Or Target = "n" Then
Rows("73:84").Select
Selection.EntireRow.Hidden = True 'Hide Row

End If
End Sub




It worked for the first couple of if's but when I got to the third or forth it stopped working. I am very rusty with VB, so any help would be greatly appreciated.

 

[PHV]

Always use parenthesis when mixing And & Or:
If Target = Range("B29") And [!]([/!]Target = "Y" Or Target = "y"[!])[/!] Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[laurenbattaglia]

I tried that and it does't work at all.

Thanks for your quick reply.

 

[PHV]

it does't work at all
What happens ?
Any error message ?
Unexpected behaviour ?
Computer crash ?
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Try this and keep in mind that Target can be more than one cell, so what do you want to happen under that condition???

If Target.Count > 1 then

Else
  If Target = Range("B29") And UCase(Target) = "Y"  Then 
end if

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DaveInIowa]

I think you're looking to match the target's address. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowsToHide As Range
    Dim hideRow As Boolean
    Dim targetAddress As String
    Dim targetValue As String
    
    If (Target.Cells.Count <> 1) Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Windows("Deal_checklists_v6_1.xls").Activate
    Sheets("Deal Reg. Overall").Select

    targetAddress = Target.Address
    targetValue = LCase(Target.Value)
    
    If (targetValue = "n" Or targetValue = "y") Then
    
        Select Case targetAddress
            Case "$B$29": Set rowsToHide = Rows("31:36") 'Future Purchasing Terms
            Case "$B$39": Set rowsToHide = Rows("41:45") 'License Information
            Case "$B$47": Set rowsToHide = Rows("49:59") 'ELA
            Case "$B$61": Set rowsToHide = Rows("63:69") 'Term License
            Case "$B$71": Set rowsToHide = Rows("73:84") 'Support Information
        End Select
        
        If (Not rowsToHide Is Nothing) Then rowsToHide.EntireRow.Hidden = (targetValue = "n")
        
    End If
    
    Application.ScreenUpdating = True
    
End Sub

 

[SkipVought]

or this...

If Target.Count > 1 then

Else
  If not intersect(Target, Range("B29")) is nothing And UCase(Target) = "Y"  Then 
end if

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[laurenbattaglia]

DaveInIowa,

I used your code and it works beautifully. Thank you and thanks to everyone else who weighed in. I don't think I could have done it without you.