Hide sheets based on cell value input

Hi there,

I have a workbook where I require the following to occur. The purpose of the exercise is to present one set of sheets for completion depending on on of two conditions being met - I know that with VB this should be reasonably straightforward but unfortunately I have no VB skills at all, so please be very explicit as to how to achieve this and how to get it into the workbook.

If sheet1 cell E37 has 'Yes' selected, I want sheets 3-9 to be hidden (all other sheets visible)
If sheet1 cell E37 has 'no' selected, I want sheets 10-16 to be hidden (all other visible)
If sheet1 cell E37 is cleared or has no selection made, I want all sheets visible.

Grateful for any help or advice you can give.

Ed

Thanks for that Bryan.

The sheets are not called sheet1...... etc, they all have discrete names indicating their subject.

Sheet1 is called 'essential details'
Sheet3 is called 'Needs'
Sheet4 is called 'Support'
sheet5 is called 'health'
Sheet 6 is called 'protection'
Sheet7 is called 'Access'
Sheet8 is called 'complaints'

(Just realised I can't count! Should be sheets 3-8)
next 6 have the same names prefixed by QL- e.g. 'QL-needs'

Idea is only one set will show depending on the content of 'essential details' cell E37.

I will try and play with what you have given me - can you tell me where to put it? Ignorance is not bliss in this respect.

Grateful for your help.

Ed

Hi Bryan,

I think I'm struggling!

I've tried the following, based on your first response, as there is the possibility of sheets being moved around by other users, BUT - it doesn't do anything. My preferred format is like this for clarity.

Sub HideSheets()
Dim ws As Worksheet
Dim sWord As String
sWord = Sheets("Essential Information").Range("E37").Value
Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Visible = True
Next
If sWord = "Yes" Then
' Sheets("Needs").Visible = False
' Sheets("Support").Visible = False
' Sheets("Health).Visible = False
' Sheets("Protection").Visible = False
' Sheets("Access").Visible = False
' Sheets("Complaints").Visible = False
ElseIf sWord = "no" Then
' Sheets("QL Needs").Visible = False
' Sheets("QL Support").Visible = False
' Sheets("QL Health").Visible = False
' Sheets("QL Protection").Visible = False
' Sheets("QL Access").Visible = False
' Sheets("QL Complaints").Visible = False
End If
Application.ScreenUpdating = True
End Sub

Then I tried, from your second reply (with a couple of minor changes) - this gives 'Run Time error 9, subscript out of range'

Sub HideSheets()
Dim ws As Worksheet
Dim sWord As String
Dim ctr As Integer
sWord = Sheets("Sheet2").Range("E37").Value
Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Visible = True
Next
If sWord = "Yes" Then
For ctr = 3 To 8
Sheets(ctr).Visible = False
Next
ElseIf sWord = "no" Then
For ctr = 10 To 15
Sheets(ctr).Visible = False
Next
End If
Application.ScreenUpdating = True
End Sub

Any thoughts - appreciate the time you've given me.

Ed

I'm getting closer. It will work when run through the VB editor but not from the spreadsheet - I was envisaging that one should be able to toggle from one to the other using the 'Yes"/"No" cell.

I have tried adding code as below to 'reset' the conditions but with no success - any ideas, or am I asking too much?

Sub HideSheets()
Dim ws As Worksheet
Dim sWord As String
sWord = Sheets("Essential Information").Range("E37").Value
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Visible = True
Next
If sWord = "Yes" Then
Sheets("Needs").Visible = False
Sheets("Support").Visible = False
Sheets("Health").Visible = False
Sheets("Protection").Visible = False
Sheets("Access").Visible = False
Sheets("Complaints").Visible = False
Sheets("QL Needs").Visible = True
Sheets("QL Support").Visible = True
Sheets("QL Health").Visible = True
Sheets("QL Protection").Visible = True
Sheets("QL Access").Visible = True
Sheets("QL Complaints").Visible = True
ElseIf sWord = "No" Then
Sheets("QL Needs").Visible = False
Sheets("QL Support").Visible = False
Sheets("QL Health").Visible = False
Sheets("QL Protection").Visible = False
Sheets("QL Access").Visible = False
Sheets("QL Complaints").Visible = False
Sheets("Needs").Visible = True
Sheets("Support").Visible = True
Sheets("Health").Visible = True
Sheets("Protection").Visible = True
Sheets("Access").Visible = True
Sheets("Complaints").Visible = True
End If
Application.ScreenUpdating = True
End Sub


Ed

Hi Bryan,

Spellings were all OK. When I added the extra code that did the trick! I also had to add an extra 'End if' to the original code.

I haven't added the message box as I'm a) not sure how to, b) don't see what it achieves.

Anyway, thanks a million - I'd never have got there without you. Now I'm filled with enthusiasm to get into VB.

All the best and thanks again.

Ed