Auto Hide a row based on a value from a differnt sheet

[marc.fuller]

I have a pricing calulator with 2 sheets.

The first sheet is the quote sheet with 25 rows of data

The second sheet is the order form with 25 rows that feed directly off of the quote sheet

If there is a 0 for the value for one of the rows on the quote sheet, is there a code that will automatically hide the corresponding row on the order form?

Thanks in advance

 

[HalfAce]

is there a code that will automatically hide the corresponding row on the order form?

OK, making a few assumptions here.
1. By code, you mean using VBA.
2. The data on both sheets resides in the same row(s) on each sheet. (ie, if there is a zero in row 12 of the Quote sheet, you want to hide row 12 of Order sheet.)
3. The column of interest (to test for the zeros) is column A.
4. The zero(s) are not the return of a formula but are instead being manually entered. (or copied & pasted)

If those assumptions are correct you can put something like this in the sheet code module of the quote sheet. (Note the sheet names used in the code and amend those to suit.)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ThisRw As Long
If Intersect(Target, Range("A1:A25")) Is Nothing Then Exit Sub
ThisRw = ActiveCell.Row
Sheets("OrderForm").Rows(ThisRw).EntireRow.Hidden = Target.Value = 0
End Sub

If that sounds like what you want but still needs to be tweaked, let us know what you want to change.

Hope it helps.

 

[marc.fuller]

Thanks for the timely response.

Every assumption is correct except for #2. The rows are not the same number on both sheets.

 

[HalfAce]

OK, if there isn't a set way to correspond the rows, (as in they are a fixed number of rows apart or something like that), then how do we determine which row in the Order sheet gets hidden when (for example) row 12 gets a zero entered into it on the quote sheet?

 

[Ingolf]

OK, making a few assumptions here.
1. By code, you mean using VBA.
2. The data on both sheets resides in the same row(s) on each sheet. (ie, if there is a zero in row 12 of the Quote sheet, you want to hide row 12 of Order sheet.)
3. The column of interest (to test for the zeros) is column A.
4. The zero(s) are not the return of a formula but are instead being manually entered. (or copied & pasted)

If those assumptions are correct you can put something like this in the sheet code module of the quote sheet. (Note the sheet names used in the code and amend those to suit.)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ThisRw As Long
If Intersect(Target, Range("A1:A25")) Is Nothing Then Exit Sub
ThisRw = ActiveCell.Row
Sheets("OrderForm").Rows(ThisRw).EntireRow.Hidden = Target.Value = 0
End Sub

Hi,

This line

Sheets("OrderForm").Rows(ThisRw).EntireRow.Hidden = Target.Value = 0

should not be?

Sheets("OrderForm").Rows(ThisRw - 1).EntireRow.Hidden = Target.Value = 0

 

[HalfAce]

Hi,

This line

Sheets("OrderForm").Rows(ThisRw).EntireRow.Hidden = Target.Value = 0

should not be?

Sheets("OrderForm").Rows(ThisRw - 1).EntireRow.Hidden = Target.Value = 0

Yep, if you want to enter a zero in row 20 of the Quote sheet and have that hide row 19 of the Order sheet.

 

[Ingolf]

Yep, if you want to enter a zero in row 20 of the Quote sheet and have that hide row 19 of the Order sheet.

I tested your code and I hide the next row of Order sheet.
If you put 0 in row 2 in active sheet it hide row 3 on Order sheet.
Where I made wrong?

 

[HalfAce]

You didn't go wrong, I did.
The difference is in our settings. You have your (default) setting to have the cursor drop down one cell upon hitting the Enter key. (yes?) Whereas I always set mine not to move upon hitting Enter.
This being the case, when the code runs after making the change, the cell below your change is now the 'active cell', and therefore the row below the change gets the code executed on it in the other sheet. This is where I went wrong with the code I posted. Instead of using 'ActiveCell.Row', I should have used 'Target.Row' when assigning the variable 'ThisRw'

So, if you replace this:

ThisRw = [COLOR=Blue][B]ActiveCell[/B][/COLOR].Row

with this:

ThisRw = [B][COLOR=Red]Target[/COLOR][/B].Row

then all will be right with the world again.
Or, conversely, you (and the OP) could both change your settings to NOT move down when hitting Enter. biggrin

Either way, thanks for pointing that out.

 

[Ingolf]

Hello HalfAce,

I assumed that "activecell" is "guilty" but because I'm not a VBA programmer I chose the easiest solution for me.
And YES, the difference is in our settings.
Thanks for the explanations.