Hide rows of one sheet based on values in another sheet?

[Andrew Raastad]

I have seen many posts about hiding rows based on a value, but my case is
slightly different. I need to hide a row (or rows) in one sheet based on
values entered on a separate sheet -- both sheets are part of the same
workbook though.

A quick rundown of what I am up against..... I have a workbook containing
two sheets, one sheet we'll call Order and the other is Invoice. On the
Order sheet, we have two columns, one for the product names and another for
the quantity desired of each. The Invoice sheet will have the same list of
products and the quantity desired, however, it also will have the price per
each, and total for each based on desired quantity, and finally a totaled
overall price at the bottom.

The way I am trying to get this to work is that when a quantity number is
typed on the Order sheet, that number appears on the Invoice sheet for the
matching product, the Invoice sheet then totals the price for that product,
and the overall total is computed. This is straightforward excel stuff, but
my problem is how to hide the rows of those products that have a 0 quantity?

I have tried using the "Worksheet_Change" method on the Invoice sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target = Range("A2:A10") Then ' A2-A10 : product quantity cells
whose values are changed from the Order sheet
If Len(Target.Value) > 0 Then
If IsNumeric(Target.Value) Then
If CInt(Target.Value) = 0 Then
Rows(Target.Row).RowHeight = 0
Else
Rows(Target.Row).RowHeight = 15
End If
End If
End If
End If

End Sub

But the above does not fire unless I manually click on and change the cells
on that sheet -- setting the Invoice cell to equal a value from a cell on
Order and changing the value from Order does not fire the event. I think it
may be because the cell's value is set to something like "=Invoice!E6"
instead of "0", but if that's the case, how do I get to the value being
passed in?

Any help is greatly appreciated.

-- Andrew

 

[Andrew Raastad]

Figures... you keep poking and poking at it and it finally works.... just
needed to place the code on the Order worksheet, and modify the code slighty
to make changes over on the Invoice sheet. This is what i came up with that
seems to do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

' We only want to mess with the Invoice numbers if the value changed is
in the quantity
' column and on one of the product rows
If (Target.Column = 5 And (Target.Row >= 5 And Target.Row <= 13)) Then
' Only continue if a value was entered
If Len(Target.Value) > 0 Then
' Only continue if that value is numeric
If IsNumeric(Target.Value) Then
' Using a "-3" offset to match up the product row on the
Order sheet with
' the product row on the Invoice sheet
If CInt(Target.Value) = 0 Then
' Set the row to be 'hidden'
Worksheets("Invoice").Rows(Target.Row - 3).RowHeight = 0
Else
' Set the row to be 'visible'
Worksheets("Invoice").Rows(Target.Row - 3).RowHeight =
15
End If
End If
End If
End If

End Sub


-- Andrew

 

[Don Guillett]

The change event worked ok to hide the rows on the active sheet(Order). If
you want to hide rows on Invoice based on the order sheet put the code on
the order sheet and tell it to hide rows on the invoice sheet.