Hide/Unhide worksheets upon entries in a range of the main worksheet

[ran58]

I have a workbook with 6 worksheets of the same format (name it target
1-6). In my main worksheet (name it assessment) I have an overview of
related values. In cells A5:A10 of the latter, I give a catch-word for
each target, which then will be displayed in the respecitive target
worksheet. In cells F5:F10 I give the weight of each target (e.g. 10,
20, or blank, if no weight). I wish to have a code that displays the
main worksheet and hides all target worksheets (since the values in
the weight column are blank) as long as no values have been entered.
Entering a value in the weight-column must display the corresponding
worksheet. Deleting or setting the value to 0 must hide the
corresponding worksheet again (conditionally/dynamically on changing
values). Any experience on this? Many thanks!

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Shane Devenshire]

Here is the basic idea which you should modify according to your data:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("F5:F10"))
If Not isect Is Nothing Then
If Target.Address = "$F$5" Then
If Target = "" Then
Worksheets("Sheet2").Visible = False
Else
Worksheets("Sheet2").Visible = True
End If
ElseIf Target.Address = "$F$6" Then
' more code ...
End If
End If
End Sub

1. To add this code to your file right click on the Master sheet tab and
choose View Code.

 

[ran58]

Here is the basic idea which you should modify according to your data:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("F5:F10"))
If Not isect Is Nothing Then
If Target.Address = "$F$5" Then
If Target = "" Then
Worksheets("Sheet2").Visible = False
Else
Worksheets("Sheet2").Visible = True
End If
ElseIf Target.Address = "$F$6" Then
' more code ...
End If
End If
End Sub

1. To add this code to your file right click on the Master sheet tab and
choose View Code.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Hi Shane, many thanks for the code. As I am rather at the dummy user's
side of VB, I wonder whether you could please check the code again.
Until now, the code does not effect anything. Many thanks for your
assistance!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect As Range

Set isect = Application.Intersect(Target, Range("F5:F10"))

If Not isect Is Nothing Then

If Target.Address = "$F$5" Then

If Target = "" Then

Worksheets("Ziel1").Visible = False

Else

Worksheets("Ziel1").Visible = True

End If

ElseIf Target.Address = "$F$6" Then

If Target = "" Then

Worksheets("Ziel2").Visible = False

Else

Worksheets("Ziel2").Visible = True

End If

ElseIf Target.Address = "$F$7" Then

If Target = "" Then

Worksheets("Ziel3").Visible = False

Else

Worksheets("Ziel3").Visible = True

End If

ElseIf Target.Address = "$F$8" Then

If Target = "" Then

Worksheets("Ziel4").Visible = False

Else

Worksheets("Ziel4").Visible = True

End If

ElseIf Target.Address = "$F$9" Then

If Target = "" Then

Worksheets("Ziel5").Visible = False

Else

Worksheets("Ziel5").Visible = True

End If

ElseIf Target.Address = "$F$10" Then

If Target = "" Then

Worksheets("Ziel6").Visible = False

Else

Worksheets("Ziel6").Visible = True

End If

' more code ...

End If

End If

End Sub

 

[Don Guillett]

This should do it. Be SURE it is placed in the sheet with the f5:f10 range

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo nomo
If Intersect(Target, Range("F5:F10")) Is Nothing Then Exit Sub
If Len(Application.Trim(Target)) > 0 And IsNumeric(Target) Then
Sheets("ziel" & Target.Row - 4).Visible = True
Else
Sheets("Ziel" & Target.Row - 4).Visible = False
End If
nomo:
End Sub

 

[ran58]

This should do it. Be SURE it is placed in the sheet with the f5:f10 range

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo nomo
If Intersect(Target, Range("F5:F10")) Is Nothing Then Exit Sub
If Len(Application.Trim(Target)) > 0 And IsNumeric(Target) Then
Sheets("ziel" & Target.Row - 4).Visible = True
Else
Sheets("Ziel" & Target.Row - 4).Visible = False
End If
nomo:
End Sub

Hi Don, your code is simply perfect! Thanks a lot!!! also to Shane,
which helped to clarify the problem....