Hide/Unhide Sheets Value on other cell through formula

Rioux

New Member
Joined
Jul 19, 2015
Messages
4
Hello,

I'm stuck so hopefully somebody can help me with this.
I'm trying to Hide or Unhide some sheets depending on the value of another cell.

I have this:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [E3] = "YES" Then
Sheets("Sheet2").Visible = True
Else
Sheets("Sheet2").Visible = False
End If
End Sub

The problem is that it will only work if I manually write "YES" on the cell.
I would like it to work when this value appears on the cell through the following:

=IF(D3>0,"YES","NO")

Is there any way to achieve this?

Any other solution to get the sheet to Hide or Unhide depending on the value of D3 would be ok too.

Thanks!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "D3" Then
      Sheets("sheet2").Visible = Target.Value > 0
   End If
End Sub
 
Upvote 0
Hello Fluff,

Thanks for your reply.

The issue is the same, it seems like the value still needs to be actually written (in this case the number) but doesn't work if I get it through a formula.
In this case a COUNTIF.
 
Upvote 0
Change events do not work if the cell is changed by a formula.
What is the formula in D3 & are those cells changed manually?
 
Upvote 0
If all the precedent cells for D3 are on the same sheet, this would work
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyCells As Range
    Set keyCells = Range("D3")
    On Error Resume Next
    Set keyCells = Application.Union(keyCells, keyCells.Precedents)
    On Error GoTo 0
    If Not Application.Intersect(keyCells, Target) Is Nothing Then
        Sheets("Sheet2").Visible = IIf(Range("D3").Value = "YES", xlSheetVisible, xlSheetHidden)
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top