Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Hide worksheet based on a cell entry (Excel 2002)

    Hi

    I have a worksheet named input in cell D11 is a simple data validtion yes/no, if the user selects no I want to xlVeryHide a worksheet called Agreement.

    Can anyone help with this please.

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hide worksheet based on a cell entry (Excel 2002)

    Right-click the worksheet tab of the sheet with Yes/No in cell D11.
    Select View Code from the popup menu.
    Paste the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("D11"), Target) Is Nothing Then
    If Range("D11") = "No" Then
    Worksheets("Agreement").Visible = xlSheetVeryHidden
    Else
    Worksheets("Agreement").Visible = xlSheetVisible
    End If
    End If
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Hide worksheet based on a cell entry (Excel 2002)

    Hi Hans

    Thank you so much, it works perfectly, but of course you know that. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>


    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide worksheet based on a cell entry (Excel 2002)

    Hans:

    To understand your code, I have looked for the answer in VBA help, but without success:

    If I comment out your line "If Not Intersect(Range("D11"), Target) Is Nothing Then" (and the coresponding End If) the code still works.

    What is the advantage of the Intersect line?

    Thanks, Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hide worksheet based on a cell entry (Excel 2002)

    Tony,
    It basically says only do the following if the changed cells include D11.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Hide worksheet based on a cell entry (Excel 2002)

    The advantage of the Intersect line is that it is only checking for changes in a specific cell (in this case D11)

    Otherwise, whenever *any* cell is changed on that sheet, the routine would still be running the code every time.
    No point in doing that unless cell D11 has specifically been changed.

    zeddy

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Hide worksheet based on a cell entry (Excel 2002)

    I should have added that it is a useful construct if your code does a lot of processing because it only runs if the range you are interested in changes - I generally use something like:
    <code>If Intersect(Target, range_I_want) Is Nothing Then Exit Sub </code>
    at the top of the event procedure.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide worksheet based on a cell entry (Excel 2002)

    Rory:

    Got it. Thanks.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  9. #9
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide worksheet based on a cell entry (Excel 2002)

    Thanks both.

    I was wondering how to do exactly that, last week - had a project which needed to check if a cell had a value. Since it was only a small w/sheet I let it check any time any cell was changed - inefficient, but it worked. Now I can do it better.

    Tony.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

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