+ Reply to Thread
Results 1 to 29 of 29

Hide and Unhide Sheets in Worksheet_Calculate() Event

  1. #1
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Question Hide and Unhide Sheets in Worksheet_Calculate() Event

    Hi,

    I am currently struggling with a Private Sub Worksheet_Calculate() macro.

    I have 3 areas of my worksheet that I wish to hide / unhide under different conditions.

    Unfortunately I am struggling to get 3 'IF' statements into the one sub.

    Here's what I have:

    Please Login or Register  to view this content.

    I have also tried nesting the IF commands - but unfortunately only one of the conditions seem to work.

    If anybody could shed some light on this I'd be really grateful as I've spent the past 2 days banging my head against a brick wall!
    Last edited by Leith Ross; 09-15-2011 at 11:43 AM. Reason: Added Code Tags

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Personally, I would use the change event.

    Please Login or Register  to view this content.
    Last edited by Bob Phillips; 09-15-2011 at 11:56 AM.

  3. #3
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    The trouble is the criteria fields are set by IF formulas.

    If I use the change sub then it doesn't update when the formulas do.

  4. #4
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Ok, I've got two of the IF commands working. The first and the last, however the middle one still doesn't seem to work:
    If Range("F94").Value = "No" Then

    Range("86:86", "92:92").EntireRow.Hidden = True

    Else

    Range("86:86", "92:92").EntireRow.Hidden = False

    End If
    For the record - I am using the calculate function as the target fields are set by a formula.

    Please help!

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Define "doesn't seem to work"
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Do not use the 'calculate' event, but the 'change' event instead.

    Please Login or Register  to view this content.
    Last edited by snb; 09-16-2011 at 04:44 AM.



  7. #7
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Sorry, "doesn't seem to work" means - it does nothing.

  8. #8
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Quote Originally Posted by snb View Post
    Do not use the 'calculate' event, but the 'change' event instead.

    Please Login or Register  to view this content.
    The trouble is the criteria fields are set by IF formulas.

    If I use the change sub then it doesn't update when the formulas do.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    It must do something, since one or other part must get triggered. Are those rows already hidden? If not, hide them and then calculate. If so, unhide and then calculate and see what happens.
    Note: you really ought to be disabling events in this code since hiding rows can cause a calculation to occur (then reenable them at the end).

  10. #10
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    I'm sorry about this - it appears I have got a little mixed up. It's not the second IF that isn't working - it's the first.

    It literally does nothing. When the value of B70 = No it should hide rows 71-73 however it doesn't.

    The rows are not already hidden - by default nothing is hidden.

    I have tried disabling events and it's done nothing.

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    If you hide the rows, does the code unhide them? Presumably so, in which case your cell value is not 'No' - it may be 'NO', or 'no' or 'No ' for example?

  12. #12
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    What about:

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    It does unhide them?

    The formula for the value of that cell is this:

    =IF(AND(Functions!G190>0,Functions!G185>0),"No","Yes")
    The VBA if for the cell is this:

    If Range("B70").Value = "No" Then

    Range("71:71", "73:73").EntireRow.Hidden = True

    Else

    Range("71:71", "73:73").EntireRow.Hidden = False

    End If
    I don't see the difference unless I'm being extremely thick? :/

  14. #14
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    In my workbook this code does what you want:

    Please Login or Register  to view this content.
    if you disabled 'application.doevents' the code won't be triggered.
    Enable 'application.doevents' before testing de code.

    Please Login or Register  to view this content.
    Last edited by snb; 09-16-2011 at 05:53 AM.

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    @snb,
    You seem to have misread the ranges. It's:
    Please Login or Register  to view this content.
    and not:
    Please Login or Register  to view this content.
    Also, based on what the OP describes, your criteria are effectively no different.

  16. #16
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Can you try altering the code to:
    Please Login or Register  to view this content.
    and see what message you get?

  17. #17
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Quote Originally Posted by snb View Post
    In my workbook this code does what you want:

    Please Login or Register  to view this content.
    I've just tried this - I've changed the "no" to "No" to fit in with my criteria but it does absolutely nothing in my worksheet - not one of the three statements result in rows being hidden - am I missing something or is there something else I need to tweak?

  18. #18
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Quote Originally Posted by romperstomper View Post
    Can you try altering the code to:
    Please Login or Register  to view this content.
    and see what message you get?
    I get the 'Yes' message whether setting it to Yes or No?

    That said the value of B70 is clearly displaying as No

  19. #19
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Guy must be very unhappy with the help offered, cross-posted at XLGuru this morning http://www.excelguru.ca/forums/showt...te%28%29-macro

  20. #20
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    I've edited the formula that sets B70 to yes or no to make it a little simpler.

    Essentially it's now being set by one condition not and and / or.

    Now I have done this the message says 'Yes' when I set the value to 'Yes' but does't hide the rows when it's set to "No" and it gives no message box.

  21. #21
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Quote Originally Posted by Bob Phillips View Post
    Guy must be very unhappy with the help offered, cross-posted at XLGuru this morning http://www.excelguru.ca/forums/showt...te%28%29-macro
    Far from it Bob - I was concerned that it had dropped to page 3 very shortly after logging it with no responses, so I logged it elsewhere. This infact is the only place I have had any constructive replies - and if it is a "no no" to post the same problem elsewhere I apologise and will close the other threads.

    I was merely trying to get the best chance of an answer as it's driving me mad!

  22. #22
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Please read and analyse the code:

    lcase doesn't appear for nothing.
    You disabled the functioning of the code by altering it.
    Use
    Please Login or Register  to view this content.
    without changing anything.
    and enable application.doevents before testing.

  23. #23
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    I've just noticed that your third condition determines the visibility of the same rows (amongst others). Is that deliberate?

  24. #24
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    FYI cross-posting is allowed as long as you follow the rules and add links.

    See Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    Read this to understand why we ask you to do this.

  25. #25
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Quote Originally Posted by romperstomper View Post
    I've just noticed that your third condition determines the visibility of the same rows (amongst others). Is that deliberate?
    Yes - essentially there are 3 conditions I need to account for.

    Either all can be seen, part can be seen or none can be seen.

  26. #26
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    But your third condition overrides the one you are having problems with since it comes after it.

  27. #27
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    See the attachment

    Change the value in A1, B1 or C1 and see what happens.
    Attached Files Attached Files

  28. #28
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Simple example:
    B70 is No, E94 is Yes:
    Condition 1: hides rows 71:73
    Condition 3 unhides rows 68:75, which includes the rows you just hid.

  29. #29
    Registered User
    Join Date
    09-15-2011
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Hide and Unhide Sheets in Worksheet_Calculate() Event

    Right folks.

    Thank you SO much for your help - I have however resolved this.

    It seems I massively overcomplicated the issue by creating three different cases when infact there were only two.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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