Auto unhide rows if formula returns a value >0

**Jauharra** · 04-09-2013, 08:34 AM

Hello all

I have been searching for hours and tried many codes (copying, pasting and manipulating to my requirements) but I am just not that good at it, so have not been successful and would really love some help please.

Within my workbook on Sheet 60 "RESULT LINE GRAPHS" I have formulae to return calculations from 59 other sheets, which in turn feeds line graphs. I want to prevent an item with "0" showing on the graphs and I've discovered that I can achieve this by hiding the corresponding row.

So, if a value in cells E7:E12 and E40:E98 returns any value >0 I would like the corresponding row to be automatically unhidden (for example, E7 = value >0 Then Row 7 Visible - just the corresponding row in each case). Also, if this value changes back to "0" I would like the row to be automatically hidden again. I will start with all rows hidden.

In my mind this is simple, but I just can't do it.

Kindest regards to all

**Thalassa** · 04-09-2013, 08:46 AM

Right click the sheet tab, select View Code and paste in

Please Login or Register  to view this content.

**Jauharra** · 04-09-2013, 10:11 AM

Hello Thalassa (and anybody else who reads this)

Thank you for a really speedy response. I've tried it, but it appears to be continually looping. I've had another idea. On the "Module Summary" worksheet I have already created activeX checkboxes which hide and unhide worksheets.

Here is a code for one of the checkboxes:

Private Sub CheckBox59_Click()
If Sheets(CheckBox59.Caption).Visible = xlHidden Then
Sheets(CheckBox59.Caption).Visible = -1
Else
Sheets(CheckBox59.Caption).Visible = xlHidden
End If
End Sub

Is there a code I could add to each of the 59 Checkbox codes which is a VBA Code on a worksheet called "Module Summary" to unhide a line within the "RESULT LINE GRAPHS" worksheet when the checkbox is ticked?

Checkbox 1 when ticked would unhide Row 40 in "RESULT LINE GRAPHS" Sheet
Checkbox 2 when ticked would unhide Row 41 in "RESULT LINE GRAPHS" Sheet
Checkbox 3 when ticked would unhide Row 42 in "RESULT LINE GRAPHS" Sheet
and so on...........

But, if the checkbox is unticked to hide the row again.

Kindest regards

**Jauharra** · 04-09-2013, 03:43 PM

Hi all

I've cracked it. I have used the following code:

Private Sub checkbox1_Click()

If Sheets(CheckBox1.Caption).Visible = xlHidden Then
Sheets(CheckBox1.Caption).Visible = -1
Else
Sheets(CheckBox1.Caption).Visible = xlHidden

End If

If CheckBox1 = True Then
Sheets("RESULT LINE GRAPHS").Rows("41:41").EntireRow.Hidden = False
End If
If CheckBox1 = False Then
Sheets("RESULT LINE GRAPHS").Rows("41:41").EntireRow.Hidden = True
End If

End Sub