| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hide/Unhide worksheets upon entries in a range of the main worksheet
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide/Unhide worksheets upon entries in a range of the main workshe
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 "ran58" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide/Unhide worksheets upon entries in a range of the mainworkshe
On 21 Jul., 17:07, Shane Devenshire
wrote: 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 "ran58" wrote: 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! 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide/Unhide worksheets upon entries in a range of the main workshe
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "ran58" wrote in message ... On 21 Jul., 17:07, Shane Devenshire wrote: 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 "ran58" wrote: 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! 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hide/Unhide worksheets upon entries in a range of the mainworkshe
On 21 Jul., 19:36, "Don Guillett" wrote:
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "ran58" wrote in message ... On 21 Jul., 17:07, Shane Devenshire wrote: 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 "ran58" wrote: 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! 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 Hi Don, your code is simply perfect! Thanks a lot!!! also to Shane, which helped to clarify the problem.... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| take all data from several worksheets to one main worksheet? | Excel Discussion (Misc queries) | |||
| Viewing other worksheets in a main worksheet | Excel Discussion (Misc queries) | |||
| How do I hide and unhide worksheets in Excel 2007? | Excel Discussion (Misc queries) | |||
| how do i format from the main worksheet to the other worksheets s. | New Users to Excel | |||
| Can I hide a worksheet and set a passwd to unhide it? | Excel Worksheet Functions | |||
Linear Mode
