+ Reply to Thread
Results 1 to 11 of 11

Hide sheets based on cell value input

  1. #1
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264

    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

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by EdMac
    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
    Hi,

    How are the sheets identified, as Sheet1, Sheet2, Sheet3 by name, or do you refer to the position of the sheets counting 1, 2, 3 from the left tab?

    no matter, delete the blocks of code that you do not want
    Please Login or Register  to view this content.

    ---
    Last edited by Bryan Hessey; 11-15-2006 at 09:48 AM.
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    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

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by EdMac
    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,

    Where to is: In the sheet required, rightmouse the tab and select View Code, paste the code in there.

    What to:

    The code is in 4 parts, 2 being For loops, 2 being SheetNames, you do not need the sheet names.

    Sheets are numberd as they appear tabbed across the bottom, Sheet(1) is the leftmost tab, tabs can be click-dragged into any sequence, the code will affect the tabs as they appear at the time.
    If you re-sequence the sheets be sure to check the code.
    Please Login or Register  to view this content.
    is the code you need.

    hth
    ---

  5. #5
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    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

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by EdMac
    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.Any thoughts - appreciate the time you've given me.

    Ed
    Hi,

    remove the ' (single quotes) - these are comments lines

    ' Sheets("Needs").Visible = False

    should be

    Sheets("Needs").Visible = False


    ---

  7. #7
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    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

  8. #8
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by EdMac
    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
    ~~
    End If
    Application.ScreenUpdating = True
    End Sub

    Ed
    Please Login or Register  to view this content.
    should do that, this goes into the Sheet nominated for E37

    ---
    Last edited by Bryan Hessey; 11-16-2006 at 10:44 PM.

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Hi Bryan,

    Really appreciate your help - still no joy.
    Hi,


    If it doesn't work for you then,

    check the spelling of Essential Information

    go to sheet Essential Information

    check E37 has Yes or No

    rightmouse the tab and select View Code

    make sure the code is there in that presented sheet (Essential Information)


    add
    Please Login or Register  to view this content.
    after the screenupdating true

    add a new sub at the bottom
    Please Login or Register  to view this content.
    and run that

    Add a
    Msgbox "here"
    to the main sub to show that when you enter Yes or No or anything else, the sub activates.

    Let me know how you go.

    ---

  10. #10
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    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

  11. #11
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by EdMac
    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
    Good to see, but the msg box is very useful for trapping where you got to,

    MsgBox "Here"

    will stop when you get there and you press OK

    But, no need if it's working.

    Thanks for the response.
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1