Automatically Show/Hide Row based on cell value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

plantechbl@earthlink.net - 14 Dec 2006 23:15 GMT

I need to have a row hidden or revealed based on a value in cell A16.

I have this code in the worksheet which works fine EXCEPT it apparently
resets the Undo stack and I don't have any undo capabilities in any
workbook that is open at the time.

I simply need to hide or reveal a row based on the cell value in A16
being "None". Any help either with this code or a totally different
approach will be greatly appreciated.

Private Sub Worksheet_Calculate()
Dim rCell As Range
For Each rCell In Range("a16")
rCell.EntireRow.Hidden = (rCell.Value = "None")
Next rCell
End Sub

Reply to this Message

Don Guillett - 14 Dec 2006 23:27 GMT

Some clarification is needed. Perhaps you want to hide all cells in a larger
range if they have the same text as cell a16?
or
do you want to hide a16:a222 if they have none?
See, if you hide a16 only if it has none then how do you unhide it?

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I need to have a row hidden or revealed based on a value in cell A16.
>
[quoted text clipped - 12 lines]
> Next rCell
> End Sub

Reply to this Message

plantechbl@earthlink.net - 14 Dec 2006 23:59 GMT

Don,
Cell A16 is linked to another cell which is always visible, so when the
data entry cell C15="None" A16 will="None". As C15 value changes A16
will change and reveal the row. In this manner I can control the
visibility of many rows based on the value of a single always visible
value.

Thanks for such a prompt reply!
Bill

> Some clarification is needed. Perhaps you want to hide all cells in a larger
> range if they have the same text as cell a16?
[quoted text clipped - 22 lines]
> > Next rCell
> > End Sub

Reply to this Message

Don Guillett - 15 Dec 2006 01:08 GMT

then try this to hide row 16 when cell a15 changes

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$15" Then Exit Sub
If UCase(Target) = "NONE" Then
Rows(16).Hidden = True
Else
Rows(16).Hidden = False
End If
End Sub

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Don,
> Cell A16 is linked to another cell which is always visible, so when the
[quoted text clipped - 33 lines]
>> > Next rCell
>> > End Sub

Reply to this Message

plantechbl@earthlink.net - 15 Dec 2006 15:05 GMT

Don,
I got it to work fine if I use the value in the data entry cell C15
(and I have Undo back again).
My final code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$15" Then Exit Sub
If UCase(Target) = "NONE" Then
Rows(16).Hidden = True
'Rows(18).Hidden = True
Else
Rows(16).Hidden = False
'Rows(18).Hidden = False
End If
End Sub

Thank you very much for your help,
Bill

> then try this to hide row 16 when cell a15 changes
>
[quoted text clipped - 48 lines]
> >> > Next rCell
> >> > End Sub

Reply to this Message

Don Guillett - 15 Dec 2006 15:24 GMT

Glad to help

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Don,
> I got it to work fine if I use the value in the data entry cell C15
[quoted text clipped - 68 lines]
>> >> > Next rCell
>> >> > End Sub

Reply to this Message