Hiding Rows on a Second Sheet, based on the cell value on a First Sheet

[mikeb7500]

I thought I already posted this, but it said I was logged off, so I apologize if this question appears twice.

On sheet named "Proposal", if N7=0, I need sheet named "Closing Statement" Rows 6-24 to auto-hide.

If "Proposal" N7>0, I need the "Closing Statement" Rows 6-24 to un-hide.

Currently the cell N7 contains "=SUM(H7,L7,M7)" which determines the value as 0, or more than 0.

Really need some help on this one folks...Thanks in advance!

 

[Logit]

.
Paste this into the PROPOSAL sheet module:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Me.Range("N7").Value = 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = True
    ElseIf Me.Range("N7").Value > 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = False
    End If
End Sub

 

[mikeb7500]

Worked Great...Thanks!


Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("N7").Value = 0 Then
Sheet2.Rows("6:24").EntireRow.Hidden = True
ElseIf Me.Range("N7").Value > 0 Then
Sheet2.Rows("6:24").EntireRow.Hidden = False
End If
End Sub


[/CODE][/QUOTE]

 

[Logit]

.
Glad to help.

 

[mikeb7500]

.
Glad to help.

Sorry, I was mistaken. I set up a test workbook with two tabs...a Proposal Tab, and Sheet 2 Tab. I then pasted your code on the Proposal Tab. It just sits there...does nothing. Any ideas? I do appreciate your further help.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Range("N7").Value = 0 Then
Sheet2.Rows("6:24").EntireRow.Hidden = True
ElseIf Me.Range("N7").Value > 0 Then
Sheet2.Rows("6:24").EntireRow.Hidden = False
End If
End Sub

 

[Logit]

.
Try this .. paste into the PROPOSAL sheet module :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("N7").Value = "=SUM(H7,L7,M7)"
    If Me.Range("N7").Value = 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = True
    ElseIf Me.Range("N7").Value > 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = False
    End If
End Sub

 

[mikeb7500]

I did, it asked me to name it, I named it HiddenRows, but still nothing. Any ideas?

Getting the following error, with this line of code yellowed

Sheet2.Rows("6:24").EntireRow.Hidden = True

Unable to set the Hidden property of the Range class

.
Try this .. paste into the PROPOSAL sheet module :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("N7").Value = "=SUM(H7,L7,M7)"
    If Me.Range("N7").Value = 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = True
    ElseIf Me.Range("N7").Value > 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = False

 
    End If
End Sub

 

[Logit]

.
That error in this circumstance usually means those rows are already hidden.

Try unhiding them, then run the macro again.

You can also (after unhiding those rows), add one more line to the macro in hopes of preventing that error again : On Error Resume Next

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Me.Range("N7").Value = "=SUM(H7,L7,M7)"
    If Me.Range("N7").Value = 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = True
    ElseIf Me.Range("N7").Value > 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = False
    End If
End Sub

 

[mikeb7500]

I can send you the two sheet workbook if you'd like to take a look at it. Then, when it works, I can transfer to the project I'm working on. I'm sure you'll spot the problem quickly. mikeb7500@gmail.com Send me an email, and I'll attach and send back. Thanks!

.
That error in this circumstance usually means those rows are already hidden.

Try unhiding them, then run the macro again.

You can also (after unhiding those rows), add one more line to the macro in hopes of preventing that error again : On Error Resume Next

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Me.Range("N7").Value = "=SUM(H7,L7,M7)"
    If Me.Range("N7").Value = 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = True
    ElseIf Me.Range("N7").Value > 0 Then
        Sheet2.Rows("6:24").EntireRow.Hidden = False
    End If
End Sub

 

[Logit]

Sent you a private message.