How can I make this code work?

  • Thread starter Thread starter Damil4real
  • Start date Start date
D

Damil4real

Hide rows 32:33 if E30 is NO. Hide rows 36:38 if E35 is "NO"...etc. I
intend to have about 4 or 5 of these variables.

The code I have below is currently not working. code is currently
placed in the workbook. Please help! Thanks!


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Select Case Target.Address
Case "$E$30"
If Target.Address <> "$E$30" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "NO" Then Rows("32:33").Hidden = True
Exit Sub


Case "$E$35"
If Target.Address <> "$E$35" Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "NO" Then Rows("36:38").Hidden = True

Exit Sub

End Select
End Sub
 
Maybe like this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim YesNo As Range

Set YesNo = Sheets("Sheet1").[E30]
If Not Intersect(Target, YesNo) Is Nothing Then
Select Case UCase(Target)
Case Is = "YES"
Rows("32:33").EntireRow.Hidden = False
Case Is = "NO"
Rows("32:33").EntireRow.Hidden = True
End Select
End If

Set YesNo = Sheets("Sheet1").[E35]
If Not Intersect(Target, YesNo) Is Nothing Then
Select Case UCase(Target)
Case Is = "YES"
Rows("36:38").EntireRow.Hidden = False
Case Is = "NO"
Rows("36:38").EntireRow.Hidden = True
End Select
End If

End Sub
 
THe version I posted works, but actually yours does too if you put it in the
sheet instead of the workbook and change the top line to:

Private Sub Worksheet_Change(ByVal Target As Range)

In the VBE, paste that whole code into the Sheet, not a module.
 
I thought I gave you the way to do this before. You MUST put in the sheet
module of the sheet desired or in the macro below in the ThisWorkbook module
for ALL sheets
''''''''''''
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub
''''''''''
Private Sub Worksheet_change(ByVal Target As Range)
If Intersect(Target, Range("e30, e35, e39")) Is Nothing Then Exit Sub
Rows.Hidden = False
If UCase(Target) = "NO" Then
Select Case Target.Row
Case 30: x = "32:33"
Case 35: x = "36:38"
Case 39: x = "46:48"
'etc
Case Else
End Select
Rows(x).Hidden = True
End If
End Sub
 
Do you really want this to work against all worksheets in the workbook?

Do you really want all the rows visible if you change one of those cells to "no"
-- and then just hide the rows controlled by that cell?

If yes, maybe:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)

Select Case Target.Address
Case "$E$30"
Sh.Rows.Hidden = False
If UCase(Target.Value) = "NO" Then
Sh.Rows("32:33").Hidden = True
End If
Case "$E$35"
Sh.Rows.Hidden = False
If UCase(Target.Value) = "NO" Then
Sh.Rows("36:38").Hidden = True
End If
End Select

End Sub

If you wanted this to work on a single sheet and each cell to only control the
rows you specify:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case "$E$30"
Me.Rows("32:33").Hidden _
= CBool(UCase(Target.Value) = "NO")
Case "$E$35"
Me.Rows("36:38").Hidden _
= CBool(UCase(Target.Value) = "NO")
End Select
End Sub

This would go under in worksheet that required this behavior.
 
Do you really want this to work against all worksheets in the workbook?

Do you really want all the rows visible if you change one of those cells to "no"
-- and then just hide the rows controlled by that cell?

If yes, maybe:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
    ByVal Target As Range)

    Select Case Target.Address
       Case "$E$30"
           Sh.Rows.Hidden = False
           If UCase(Target.Value) = "NO" Then
               Sh.Rows("32:33").Hidden = True
           End If
       Case "$E$35"
           Sh.Rows.Hidden = False
           If UCase(Target.Value) = "NO" Then
               Sh.Rows("36:38").Hidden = True
           End If
    End Select

End Sub

If you wanted this to work on a single sheet and each cell to only control the
rows you specify:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Address
       Case "$E$30"
            Me.Rows("32:33").Hidden _
                = CBool(UCase(Target.Value) = "NO")
        Case "$E$35"
            Me.Rows("36:38").Hidden _
                = CBool(UCase(Target.Value) = "NO")
    End Select
End Sub

This would go under in worksheet that required this behavior.












--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you all for your responses. I greatly appreciate your
assistance!

All of your codes work if not for the fact that there is ALREADY
another “Private Sub Worksheet_Change(ByVal Target As Range) “ code
pasted in the worksheet. And everything I paste any of your codes in
the same worksheet (sheet is named “main”), I get an error saying
“Compile error: Variable not defined” then it highlights the “Private
Sub Worksheet_Change(ByVal Target As Range)” name. I think it’s
because there are two of the same type of codes in that worksheet.
That was why I asked the question how I can combine two Private Sub
Worksheet_Change(ByVal Target As Range) codes or if there is another
way I can control that sheet from the workbook?

The intention is for the code to work on a single sheet (sheet named
“main”) and each cell to only control the rows I specify:

If E30 is NO, then hide Rows 31:32.

If E35 is NO, then hide Rows 36:37 (still leaving Rows 31:32 hidden)

With the codes you supplied, Rows 31:32 becomes visible again when E35
is NO, and then hides Rows 36:37. That’s not what I want. I want each
cell to only control the rows I specify.

Thanks for your continued support, guys!
 
First, you only get one _Change event per worksheet. You'll have to combine
your code from both procedures into a single procedure.

Second, I don't think you looked at my second suggestion.


Damil4real wrote:
 
Third, if you have trouble combining the code, post both of them and I'm sure
you'll get help.
 
Back
Top