PDA

View Full Version : Solved: Hide a Row when Formula is "True or False"



070968
11-04-2005, 11:11 AM
Hi

Please solve my following problem :-

Hide a Row when Formula is "True or False".


Example : if cell Sheet1!B2="yes"
then, Hide Sheet2!entire 3rd row


Is it possible to do this through following function :


Function FunctionHide(lvRow As Double, lvTrue As Boolean)
Dim lvRange As Range
If lvTrue = True Then
Set lvRange = Range("A1").Offset(lvRow - 1)
lvRange.EntireRow.Hidden = True
End If
McHide = lvTrue
End Function



I use the above condition in many rows of my workbook. Please help me.


regards!


Garima

vonpookie
11-04-2005, 12:00 PM
You can't use a function to hide a row or column, no.

However, if the true/false value will always be determined by a formula in one column, you could use a Worksheet_Calculate event to loop through the rows and hide/unhide rows accordingly.

How are you determining which row(s) to hide?

070968
11-04-2005, 12:15 PM
Hi

Sheet1!d2 is user defined cell in which he will put "yes" or "no"

If it is "no" then I want to hide a Row say Sheet2! Row 5

Please Help. I am new to VBA. Please can u give me full written code.

Thanks

Garima

vonpookie
11-04-2005, 12:20 PM
Well, it's easy enough to write code for that one particular instance, but if you want to check each row of data for "yes" or "no" and hide rows--I'm afraid I don't understand the logic behind it.

So if you are entering "yes" into D2, why hide row *5* on the other sheet? It can be done, but will it always be hiding the row that is 3 down from the row the "yes" appears on in the other sheet? This is where I am confused at the moment.

070968
11-04-2005, 12:56 PM
Please see attached file.

I have written a note in yellow marked cell in Sheet "DR"

Is it possible ?

Thanks

Garima

vonpookie
11-04-2005, 01:10 PM
Ok, I think I see what you're doing now.

Put this code in the module for sheet "DR". If you right-click the sheet tab and select "View code" the Visual Basic Editor will automatically open and display the correct module. Just paste this code into the blank window it displays.

Private Sub Worksheet_Calculate()
Dim c As Range

For Each c In Range("B40:B41,B43")
If c.Value = "no" Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c

End Sub

This will run whenever the formulas on sheet "DR" calculate. On this sheet, if B40, B41 or B43 ="no" then the row will be hidden. If they recalculate again and do not ="no" then the rows will be unhidden.

070968
11-04-2005, 01:31 PM
Thanks so much for your kind help !!!.

:bow: :bow: :bow: :bow:

Code worked.

Is it possible to auto run this code, when value changes in the relevent cells?


best regards,


Garima

vonpookie
11-04-2005, 01:38 PM
That's what it should do, if I'm understanding you correctly.

The formulas on Sheet DR refer to the other sheets. When the values on those sheets change, it will cause any formulas that refer to them to recalculate. If those formulas happen to be on Sheet DR, it will automatically run the Worksheet_Calculate code that was placed in the module for that sheet.

Try changing the values on Sheet "AT" and see if any of the 3 rows are hidden on sheet "DR" when you go back and look at it :)

070968
11-04-2005, 02:08 PM
HI :yes

Now I am using the code successfully, It is working automatically. I Tried all options of my file to check it.

Thanks for ur valuable co-operation.

I am sooooooooooooooooooo happyyyyyyyy
:rotlaugh: :rotlaugh: :rotlaugh: :rotlaugh: :rotlaugh: :rotlaugh:

regards!


Garima