Excel VBA Hide - Show Rides

I have a VBA code that shows rows if a cell is "Yes" and hides rows if "No".

If I type "Yes" or "No" in a cell, it will show / hide the rows. If I i use an IF formula to generate "Yes" or "No" in the cell, it does not work.

To capture changes by a formula you have to use the Worksheet_Calculate() event.

You can also use the Worksheet_Change event, but on the cells that are the source for the Yes/No formula.
You program the _Change event for the source cells, check what the Yes/No formula returns and take the appropriate action depending on the read formula result.

Artik

Thanks for your help, but I cannot get it to work. I have the Worksheet Change in the code but to no avail. Below is the code that I have.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim answer As Integer




'to hide Family POS

Set KeyCells = Range("B52")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

ActiveSheet.Unprotect

If Target.Value = "Yes" Then
Rows("54:61").Select
Selection.EntireRow.Hidden = False
Range("e52").Select
ActiveCell.Offset(0, 1).Select

ActiveSheet.Protect

ElseIf Target.Value <> "Yes" Then

ActiveSheet.Unprotect

Rows("54:61").Select
Selection.EntireRow.Hidden = True
Range("e51").Select
ActiveCell.Offset(0, 1).Select

ActiveSheet.Protect

End If
End If


End Sub

What's in cell B52? The code shown will work when you manually (or code) change its content. If the cell contains a formula, your code won't work. In this case, show what formula it contains. Ideally, you would include a sample workbook. See the yellow banner at the top of the page.

By the way, edit your post and put the presented code in the [code] tags (select the entire code and press the [#] button).

Artik

Thanks for replying. Cell b52 contains a formula. It was a if formula with the answer as “Yes”or “no”

The Worksheet_Change event is dispatched when you manually or with code change the contents of cells in a given sheet, i.e. edit cells. Thanks to the Target parameter, you can specify that we are only interested in a certain range of cells, and if there is a change in the Target range, we will take a specific action. In other ranges the action will not be taken. The Worksheet_Calculation event is dispatched when there are formulas on the sheet. It cannot be directly indicated that we are only interested in calculating cell B52 (no Target parameter). The event affects all formulas on the sheet. So, if you use the _Calculation event, your action will always be executed, even if the formula in B52 does not change at the moment. So redundant work is performed (regardless of whether the result of the formula has changed or not, the action will be performed). If you use the Worksheet_Change event, your action can only be performed when cell B52 changes the value from Yes to No or vice versa. But in order to program this event, you must investigate whether the change occurs in the cells that are the source for the formula in B52.

Originally Posted by PaulHenderson

Cell b52 contains a formula. It was a if formula with the answer as “Yes”or “no”

We know what the formula returns from the very beginning. However, we still don't know what the source scope is. The problem may be more complex if B52 refers to the FORMULA in a different scope. Then we need to get to the source range of this formula. We need to get to the original range, the change of which ultimately results in the recalculation of cell B52. That's why I asked you to show in the attachment how it actually looks like.
If you don't want to dig that deep, you can use the _Calculate event. But be warned, this is not correct.

Please Login or Register  to view this content.

The _Calculate event of this sheet can be dispatched when it is not active. It could even be another workbook that is open. In this case, Range(XX).Select will cause an error, because you can only select a cell in the active sheet.

For the next time, please edit your previous post where you showed the code. Edit the post, select the code and use the [#] icon to add [code] tags.

Artik

Hi Artik, Amazing response, many thanks for taking the time. The users were happy to always show the rows in the end. I have another query about refeshing a pivot table via VBA. I will post separately. Thanks