VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Thanks for your added example ... and for the two possible input layouts ... :wink:


    Hope I will find some time over the week-end to actually dive into the challenge ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello Christine,


    As we are moving forward into the issue of the Recurrence Rules ... we need to be very explicit and lay out all the details of each individual rule ...


    Indeed, since there is no generic solution, we will need to code each separate rule ...


    If you are looking for inspiration regarding the possible recurrence rules :


    https://www.ma.utexas.edu/webcalendar/doc/misc/recurrence.html


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    I'd rather just forget the recurring b/c this is taking too long to get help --
    I'd rather just go with the simple list (shown in the most recent attachment) and have the user be forced to just enter recurrences as they are needed --
    So if the vba can just look at the single table and place a SUMmed dollar amt for each day -- onto the calendar - I would assume - calling it good at this point --
    Is this something you think is doable?

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Does it mean you need an urgent solution ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello Christine,


    Attached is your sample workbook ... with a macro to correctly display the daily recap amounts within the Calendar ...


    For your Urgent solution, your User will need to input the dates for each item ...


    Hope this will help

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Have you had a chance to take a look at your workbook Version 8 ... and test the macro which generates the Income & Expenditures within the calendar ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    OH WOW! That works great!!! Just tested it to feed a few INC and EXP items to Jan and Nov Dec and each fed through perfectly!
    I was laying in bed last night thinking I'd just start trying to find a lookup/index/sum combo formula and start plugging it in every calendar cell manually - for worse case scenario -- but YOU MADE MY DAY!! This is MUCH more efficient and works like a charm!


    If you know that the recurrence capability (originally sought) is doable -- this would be great to see when time permits --but this current/attached/ more manual process will at least get them started for a fresh new month of July in 'better overseeing their INC/EXP activity month-to-month'...


    RE: The Rules you mentioned previously for recurrence: I think would be pretty straight forward (based on what column data dictated)..
    Out of the list of rules provided at the UTx link, I think we could scale it back to only a handful to keep it somewhat simple..
    1. OneTime (is what we're essentially doing w/ the current list) it is driven solely by a full date to go place it on that sole date. mm/dd/yy
    2. Weekly (is definitely needed as this is common for incoming payroll checks (every Friday) and outgoing childcare expenses (every Monday))
    3. Every 2 Weeks (is very common for INC payroll)
    4. Monthly By Date (very common for EXP, rent/house pmt, car pmt)
    5. Occurs every N weeks on a set of selected weekdays : (I'm not sure if this is the right one to use for the "Occurs Every Monday of Every Week of Every Month" childcare EXP)?
    6. This one I'm not seeing in the list of rules below: "Bi-Monthly" (which is true in this case and for many - where INC only comes in twice a month and is very diff from Bi-Wkly esp in mos where 5 wks are present. Generally with Bi-Wkly payroll it's something like "15th & 30th" -- (so 2 number designations would be needed to accommodate this) -
    I'll try to update the previous sample file to accommodate only the 6 rules above --- and see if that's something that you see is achievable?
    (I'll post that update today)


    Recurrence Rules

    The occurrence of an repeating event is determined by three variables: repeating rule, occurrence count and an list of exceptions.
    Repeating rule and occurrence count are set at the time when the event is entered into a calendar.
    The exception list is constructed at a later time when occurrence at certain dates were deleted.

    • OneTime: does not repeat
    • Daily: occurs daily
    • Weekly: occurs weekly
    • Every 2 Weeks: occurs bi-weekly
    • Monthly By Date: occurs on the same day every month. If the specified date does not apply to a month (e.g. Apr. 31), no events will be scheduled for that month.
    • Monthly By Weekday: occurs on the specified weekday every month. For example, the second Thursday of the month. The week number is determined by the initial date the event was scheduled.
    • Yearly: occurs yearly on the same date, like New Year's day.
    • MTWThF: occurs on weekdays. The occurrence count is the number of weeks the event is scheduled to run, not the number of occurence of the events. (Depending on user feedbacks, the occurrence count maybe changed to the actual number of occurrence of the event in the future.)
    • MWF: occurs on Monday, Wednesday and Fridays.
    • TTh: occurs on Tuesday and Thursdays.
    • Yearly By Month Weekday: occurs yearly, on the same weekday of the month the event was scheduled. For example, the third Sunday of June.
    • Every Other Day: occurs every two days.
    • Every Third Day: occurs every three days.
    • Every Fourth Day: occurs every four days.
    • Every Fifth Day: occurs every five days.
    • Every Sixth Day: occurs every six days.



    Custom Recurring Rules

    • Occurs every N days: N is a customized variable.
    • Occurs every N months on the same day or weekday: N is a customized variable.
    • Occurs every N months on the same day/weekday counting backwards from the end of the month: N is a customized variable.
    • Occurs every N weeks on a set of selected weekdays : N and weekday-mask are customized variables.
    • Occurs every N weekdays: N is a customized variable.
  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Quote from ChrisOK;794947

    OH WOW! That works great!!! Just tested it to feed a few INC and EXP items to Jan and Nov Dec and each fed through perfectly!
    I was laying in bed last night thinking I'd just start trying to find a lookup/index/sum combo formula and start plugging it in every calendar cell manually - for worse case scenario -- but YOU MADE MY DAY!! This is MUCH more efficient and works like a charm!


    Pleased to hear you can benefit from the initial solution ...:wink:


    Ideally, with a bit of feedback from your users, you will be in a position to focus on the recurrence features ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Christine,


    By the way ... forgot to ask you how exactly you would like to handle the ' OMG Debt Alarm ' within the calendar ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    ROFL -- I literally had to laugh out loud --- Well, re: "OMG Debt Alarm" --
    A pop-up triggered by a negative bank balance would probably work if easy to trigger and look up the associated date?
    Some possible messages:
    "YOUR A/C EXPERIENCES A NEGATIVE BALC ON: Aug 25"
    "OMG! You'll be overdrawn by Aug 25 if you don't take actions prior!"
    "OMG! You spent WAY too much this month -- get a grip, buckle down or find 2nd job!"
    "OMG! When it rains it pours they say --- you've poured out too much and will SEE RED on 8/25"
    "TIME FOR A NEW CAR if that many repairs has put you into the RED on 8/25"


    (the list can go on -- with probably many more ---much better alternatives) - I'd like to see other Ozgriders submissions...but has appeared it's just you and me in this string for some crazy reason...!?!


    :party:I think it would be really fun to have that chunk of code built in -- to where if the user wants it turned on, I can go in the backside and toggle TRUE to FALSE or vice-a-versa.. and can edit msg as appropriate to the user's sense of humor level..


    Sadly, I know folks are struggling because they've lost their job due to layoffs, shutdowns, & things totally out of their control such as enormous medical bills are cutting into their once-balanced-budget... Sooo, having something that's a little more fun to manage the horrible topic of **DEBT** is the goal... (with hopes things will lighten up quicker - w/ the ease of at-a-glance visibility and debt be more expeditiously eliminated) :flower:

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Let me try to find an original twist to handle your OMG messages ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    For the sake of your OMG Debt messages, attached is version # 9 ... for your review ...:wink:

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    INCREDIBLE! -- I added another "new car" on 8/15/17 and it worked!
    (updated all the balance info, added the new purchase and triggered an alert):wowee::yikes::thumbcoo:
    This is fantastic and I'm sure they'll actually enjoy working with their finances now! (and should now, NOT encounter the OMG moments as much):dance:


    One thing that was bit cumbersome was after the alert triggered, I looked at the calendar to see where things 'went read' - but "Jan" was what was visible...(so I had to scroll and scroll all the way down til' I could find AUG 2017 which is where things hit negative)


    Is there a way to make scrolling occur to whatever mo the "RED RED date's month" is that's already being listed within the alert?
    Or alternatively, at the very least -- to whatever calendar month their desktop shows as =TODAY...


    The first would be ideal, the 2nd alternative is still good..
    In other words, COL J (MO) + COL L (YR) would be Indexed in some way for a MATCH to be the same MO+YR of either (1) the delinquency or (2) to TODAY's mo if that's easier -- that would be fine too.. (at least it would eliminate the majority of scrolling)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Hello,


    Quickly added the automatic scrolling to the month when things turn [COLOR="#FF0000"]Red[/COLOR] ... :wink:


    Cheers


    :smile:

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Woooo Hooo! So excited to show this to them and show them that their dreams have come true! Love the solution - thanks for the awesome help with the solution! I gave 5 starts on the reputation scale! SOOO very much appreciate your help -- now I've got to go study it to see if I can learn from it!
    Thanks again! :cheers: :flower: :rock: :thanx:

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    Extremely pleased to hear the Calendar solution can help you out ... :smile:


    Let me THANK YOU ... for your Thanks, for the Reputation Points ....for the flowers and for the Beer ...:drink:


    This is Very Nice of You ... !!! :thumbcoo:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    ...


    As an afterthought ... :drunk: ... your users might need to keep Toggling between Current Month and '[COLOR="#FF0000"]Red[/COLOR]' Month ...


    So attached is the Version 10 with a hijacked F9 function key ... which allows to go back and forth between these two months ... :wink:


    Hope this will help ...

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    YOU-ARE-JUST-CRAZZZZY GOOD at this aren't you!!
    Love it! - Very smart afterthought --- more "beers for your horses" and flowers for you!
    :congrats::thumbcoo:


    :cheers:


    :rose::rose::flower::flower:

  • Re: VBA auto-fed Calendar Blocked View of Income, Expense and OMG Debt Status


    With ALL these Beers ... and ALL these Flowers ...


    I can start my own business : either open a Bar or a Flower Shop ...!!! :lol: :jumpupdo:



    Thanks a ton for your very kind words ...!!!


    Cheers
    :cheers:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)