Hiding worksheets based on cell result

[BaldySlaphead]

I have the following worksheets in Excel 2003:

Who Are You
Your Info Needs
Managing Records
Info Access & Retrieval
FOI, EIR & DPA
Other Comments

They form a questionnaire about information management FYI. I have a routine that totals what percentage of each sheet is completed. Using an if statement, I then assign a value of 0.166666666666666 to that sheet, and from there can calculate what percentage of the workbook is complete (0.166666666666666% * 6 =100%)

I would like to hide all but the first sheet until the 0.166666666666666 is achieved on that first sheet, then all but the first and the second until the 0.166666666666666 is achieved on the second and so on - i.e. until each sheet is completed, all subsequent sheets are hidden.

Can I adapt the following for hiding rows to sort this? If so, how?

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range("C5") 
If Target.Value = "No" Then
Target.Offset(1, 0).EntireRow.Hidden = True  
ElseIf Target.Value = "Yes" Then
Target.Offset(1, 0).EntireRow.Hidden = False 
End If

Thanks for taking a look.

[mudraker]

If the cell that you assign the 0.166666666666666 value to is a formula then you will will be using a Worksheet_Calculate event macro

 
Private Sub Worksheet_Calculate()
Dim wS As Worksheet
For Each wS In Worksheets
If Range("a1").Value = 0.166666666666666 Then
wS.Visible = xlSheetVisible
Else
If wS.Name <> "Main" Then
wS.Visible = xlSheetVeryHidden
End If
End If
Next wS
End Sub

If it is not a formula then you can use the Worksheet_Change event macro

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wS As Worksheet

If Not Application.Intersect(Target, _
Range("A1")) Is Nothing Then

For Each wS In Worksheets
If Target.Value = 0.166666666666666 Then
wS.Visible = xlSheetVisible
Else
If wS.Name <> "Main" Then
wS.Visible = xlSheetVeryHidden
End If
End If
Next wS
Else
Exit Sub
Set Target = Range("C5")
If Target.Value = "No" Then
Target.Offset(1, 0).EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Target.Offset(1, 0).EntireRow.Hidden = False
End If
End If
End Sub

[BaldySlaphead]

Thanks!

That seems to work fine for one sheet; it hides all other sheets until the 0.16666666666666 is achieved, but then it shows all of the sheets. I only want it to show the next sheet. That next sheet will also have a cell which will use an IF to put 0.16666666666666 into a cell when all questions have been completed, at which point the next cell should become apparent etc.

I also have an intro sheet (called 'Intro') that I wish to remain unhidden all the time.

Thanks!

[mudraker]

PLEASE USE TAGS WHEN ENTERING CODE !!!!


Oops slightly miss read you 1st post

Here are 2 options

use 1 of these 2 on each worksheet module - change sheet names as required

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wS As Worksheet

If Range("A1").Value = 0.166666666666666 Then
Sheets("Sheet2").Visible = xlSheetVisible
End If

Set Target = Range("C5")
If Target.Value = "No" Then
Target.Offset(1, 0).EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Target.Offset(1, 0).EntireRow.Hidden = False
End If
End Sub

Private Sub Worksheet_Calculate()
If Range("A1").Value = 0.166666666666666 Then
Sheets("Sheet2").Visible = xlSheetVisible
End If
End Sub