Hide Sheet Based on Cell Value

Hi I have tried searching the forum, however, I am struggling with this one.

I want to hide a sheet based on a cell value if possible.

I have created a weekly planner and in cell B3 on sheet1 I a would type in a property address. I want the code to hide Sheet2 if nothing is entered into the cell B3 and if I do enter any text in B3 then it will re appear.

help would be appreciated

Kind regards

Put this under sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("B3").Address Then
    If Target.Value = "" Then
        Sheets("Sheet2").Visible = False
    Else
        Sheets("Sheet2").Visible = True
    End If
End If
End Sub

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = ActiveSheet

If Not Intersect(Target, Range("B3")) Is Nothing Then
    If Target.Value = "" Then
            Sheets("Sheet2").Visible = False
            ws.Activate
    Else
                Sheets("Sheet2").Visible = True
            ws.Activate
    End If
End If
        
End Sub

BTW. Welcome to the Forum.

Hi,

Give it a try to this code. place it under the "Sheet1" module of the project.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub

If Target.Value = vbNullString Then
    Sheets("Sheet2").Visible = False
Else
    Sheets("Sheet2").Visible = True
End If

End Sub

Thanks

CopytoWorksheetModule.gif

Originally Posted by fredlo2010

Hi,

Give it a try to this code. place it under the "Sheet1" module of the project.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub

If Target.Value = vbNullString Then
    Sheets("Sheet2").Visible = False
Else
    Sheets("Sheet2").Visible = True
End If

End Sub

Thanks

Attachment 276349

That's overly complicated ... there is no need to use the INTERSECT function.

Also, if there IS data in B3 and it is deleted by selecting a range of cells that includes B3, then pressing "Delete" then macro fails.

Try this instead...

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B3").Value = vbNullString Then
    Sheets("Sheet2").Visible = False
Else
    Sheets("Sheet2").Visible = True
End If

End Sub

Hi thanks for the response I know it was a simple task for most but I could not get my head round it yesterday. I worked my way down the replies and J Davis worked how I wanted it to ...

thanks again people

I have noticed though that once my macro is running if I paste text into B3 then I get a type mismatch error. Same applies if I select the cell and press delete. To overcome this I have to select the text and press delete

any ideas?

Try this modification. If it doesn't work then can you attach a sample with data which doesn't work?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = ActiveSheet

If Not Intersect(Target, Range("B3")) Is Nothing Then
    If Target.Text = "" Then
            Sheets("Sheet2").Visible = False
            ws.Activate
    Else
                Sheets("Sheet2").Visible = True
            ws.Activate
    End If
End If
        
End Sub