+ Reply to Thread
Results 1 to 19 of 19

Show or hide Excel calendar rows based on current date

  1. #1
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Show or hide Excel calendar rows based on current date

    Hi,
    I have very little knowledge of how to write code for excel and typically use google for help. Here's my question: using a calendar I created in Excel, how can I only display two rows about 14 days of a calendar month based on the current date? So the reader would see the the row (week) containing today's date plus 14 days forward. Everything else would be hidden. I have the calendar months vertically stacked. Any advice you can offer is greatly appreciated.
    Regards,
    Saabra

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    Depending on how your calendar is formatted a filter could do the job or you'd need to add some code.
    Can you upload this calendar?
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Hi Jsjallie,
    I've attached the census with calendar. Census Report.xlsm Your help is greatly appreciated.
    Regards,
    Saabra

  4. #4
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    Hi Saabra,
    here's the workbook I added filtering of the calendars to.
    I made some notes on the Census sheet of the changes I made.
    And left some work for you to do of course
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Wow, thank you. I'm not sure I understand it yet and will take some time today to see what you did. Just an FYI that the following screens popped up upon opening: Two_Screens.docx. I'll be in touch.
    Saabra

  6. #6
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    That has most probably to do with Office executing the Workbook_Open procedure while still in blocked mode for security.
    Once you saved the workbook or made a trusted document this error will no longer occur.

  7. #7
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Okay, I'll have more time to look this over tomorrow and get back to you.
    Thanks again,
    Barb

  8. #8
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Tsjallie,
    It appears that the ISFORMULA function used in column A is not compatible with Excel 2010. Is there another function we can use compatible with Excel 2010?
    Saabra

  9. #9
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    Oops, overlooked that you're using Office 2010.
    Yes, the ISFORMULA-function is available since Office 2013.
    Here's the workbook again.
    Added a user defined function ISFORMULA and adjusted the formula to accommodate the new function.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Wow, this looks great. I'll get back to you after I make the adjustments. Thanks a million.
    Saabra

  11. #11
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Tsjallie,
    Can you resend the adjusted attachment in unprotected mode? I can't enable editing on the spreadsheet.
    Saabra

  12. #12
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Tsjallie,
    See attached screen shot upon opening the revised census report with filter. Screen shot upon opening of revised calendar.docx. Please advise.
    Thanks,
    Saabra

  13. #13
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    As I see in the screenshot that the workbook is still in Protected View. That's done by Windows for security reasons, because the workbook comes from the Internet.
    The workbook or sheet itself is not protected.
    When the error messages start to pop up click End to stop processing the user defined function.
    Then quit protected view by clicking Enable Editing.
    After that save the workbook.
    If you're asked if you want to make it a trusted document, choose Yes.

    BTW, the IsFormula-function is called when the workbook is recalculated.
    And I think recalculating is triggered by saving the workbook in the Workbook_Open() procedure. Why do you want to save it on opening?
    I'm uploading a version of the workbook with this line disabled for you to see if it then opens without errors.

  14. #14
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Thanks for the insight. I wasn't trying to save it upon opening. I was trying enable editing.
    Barb

  15. #15
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    I was referring to this procedure in ThisWorkbook:
    Please Login or Register  to view this content.
    In the latest version I uploaded I disabled that line, so it should open without errors now.

  16. #16
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Tsjallie,
    I've attached the first 5 screen shots upon opening your spreadsheet. Five Screens.docx It's not working, and I'm sorry I don't understand why. Do the screen shots shed anymore light on the problem?
    Thanks,
    Saabra

  17. #17
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    Can you check the references in the Extra menu of the VB Editor.
    Do any of the checked references show 'Missing'?
    SaabraRefs.JPG

  18. #18
    Registered User
    Join Date
    04-27-2017
    Location
    Masschusetts
    MS-Off Ver
    2010
    Posts
    70

    Re: Show or hide Excel calendar rows based on current date

    Tsjallie,
    I just want to thank you for your persistent help on this project. Your spreadsheet works like a charm. Your efforts will help expedite the care of hundreds of people suffering with mental illness. Thank you again.
    Saabra

  19. #19
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Show or hide Excel calendar rows based on current date

    Good to hear that it's working now.
    Was it something with a reference?

    Your efforts will help expedite the care of hundreds of people suffering with mental illness.
    Glad I could help. Keep up the good work!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Show Hide Rows based on cell value
    By Juicy2052 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-13-2015, 07:55 AM
  2. [SOLVED] macros for two push button to show up calendar/hide calendar.
    By pejoi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2014, 04:11 AM
  3. [SOLVED] Sort Rows by Date & Only Show Rows After Current Date
    By rwatson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-23-2013, 01:58 PM
  4. [SOLVED] Hide/Show rows based on the value in a cell
    By PatRiot199 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-11-2012, 11:52 PM
  5. VBA code to hide/show rows based on value in each row
    By KEM4321 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2012, 01:19 PM
  6. Let calendar show current date
    By jpcsolutions in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2011, 05:28 PM
  7. Code to show/hide rows based on Y
    By Justinmih in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2011, 06:12 PM

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