build userform calendar using "cell counting" method looping named labels (SamT)

[mperrah]

_frm_Calendar snb_mod_04.xlsm
Here is the working file
Need to add back the frame around payday options.. but this is working well thanks to SamT and snb.

I will pursue the idea of making a cash flow management tool.
I started tweaking this one but I think it will need a major overhaul.

So for now a huge thank you to helping me bring this idea to working awesomeness.
-mark

[mperrah]

BTW: snb
I now understand what your code was doing:

.Controls("dnum" & x).Font.Bold = .ob_EoF.Value

I just had to add the "." or ufCal in front to get it to work...
So if the option button was checked it would bold the caption,
so my whole IF statement was not even necessary.
very nice one liner for sure.
I will be implementing this practice in past and future projects.
thank you

[mperrah]

Only thing I can get is now if we display May, i get bold on the 1st, 8th and 22nd
the first is correct but then should be 15th and 29th ?
I combed through the code top to bottom and tried formating m-d-yyyy or yyyy-m-d or yyyy-d-m or "m" -d-yyyy.
This is baffling

If you look at the VBA_Variables page you can see the code testing seems to work, (Column AG and AF)
but translation to the userform falls short

[SamT]

Function WeekNum(dDate As Date) As Long
'Returns the number of the week of the year of dDate
  WeekNum = CLng(DateDiff("ww", dDate, "1-1" & Year(dDate)))
End Function

Suggest you set the spinbutton min property = 0 and max property = 13. On Changing, If Value = Min Then Value = 12. If Value = Max then value = 1. This will give a Rollover quality to it. I also would use an "Update" command button instead of using the SpinButton change event. Otherwise, the screen will flicker a lot if the user "spins" several months (or years.)



Who says that pay days start on the second Friday of the year?
One year, the last payday was 12 days before New Year's Day. Now I gotta wait yet another 2 weeks to get paid again?

That is why I recommended storing the date of Last payday. Once initiated, it is eternal and covers any weekday payday. By also storing periodicity, and using it as a multiplier, one routine handles getting paid every 1 week, 2 weeks, ... 20 weeks. all you have to add is a check for holidays and the option to be paid before or after such.

I have been paid weekly on days other than Fridays. Payday is at the convenience of the employer, not by some universal rule.

[mperrah]

So add a year change if spin down and month is 0 - year
and if spin up +1 if month 12

[SamT]

It actually sounds good for this particular application.

How are you handling special one time bill planning? Say the User wants to add something one time 6 months in advance?

I am thinking of a bill scheduling form and Data sheet both with Date Due and Approximate amounts. The Data Sheet would have several tables: Monthly, semi-Monthly, Quarterly, Biannual (Great for Insurance payments,) Annual, and Special.

All would need a DayDue Column, Semi-monthly, Quarterly, Biannual, and Annual a month due number column, but Special only a Date Column.

SideBar:

Since all Collections use positional Keys, the first Item added to a Collection can be retrieved with Collection(1) and the Second Item with Collection(2) etc. This means you don't have to use any Control Tags as keys, just add them in Date order. This means that you can have your bill controls in another Collection accessible by Day number.

BTW, for the Bill controls, I would use two Column ComboBoxes, so as to both display the predicted value of the bill and allow the user to edit it on an unlimited, scrollable control. This would also facilitate updating the bill totals from estimated to actual.

If you were using Collections:
Sort the Monthly by DayDue, Loop thru the column and Access each Bill Control from its Collection by DayDue and add the bill to the Control.
Repeat for semimonthly, but check if ODD/Even matches the Current Calendar Month.
For Quarterly bills, just remember that in Mod 3 counting 1, 2, and 0 are Jan, Feb, and Mar, respectively [Month(March 1, '15) Mod 3 = 0]

SideBar:

Come to think of it all any of the tables need is a MonthDue, a DayDue, and approximate amount columns. Since Mod Counting always ends in 0, the MonthDue for monthly bills is 0 and the "MonthDue =value" for monthly bills is Month(CurrentMonth) Mod 1. For SemiMonthly bills its Month(CurrentMonth) Mod 2, (odd = 1, even = 0, Quarterlies is Month(CurrentMonth) Mod 3, Semiannuals is Month(CurrentMonth) Mod 6, (June and Dec = 0) and annuals is Month(CurrentMonth) Mod 12, Dec = 0.

This means that one procedure can read all the tables and add the appropriate bills to any Calendar month. Just change the Modulus value

Sub GetBills()
'Pseudocode
AddBills(MonthlyTable, ModDivisor:=1)
AddBills(SemiMonthlyTable, ModDivisor:=2)
AddBills(QuarterlyTable, ModDivisor:=3)
AddBills(SemiAnnualTable, ModDivisor:=6)
AddBills(AnnualTable, ModDivisor:=12)
AddBills(SpecialTable,  ModDivisor:=12)
End Sub

The AddBills Routine checks Month(CurrrentMonth) Mod Modulus and compares it to the MonthDue column.

Sub AddBills(Table As Range, ModDivisorAs Long)
'PseudoCode
'Table Structure: Column1 = Bill name, 2 = Amount, 3 = DayDue, 4 = MonthDue
ModMonthDue = Month(currentMonth) mod ModDivisor
For each Row in Table
If .Cells(4) = ModMonthDueThen BillControls(.Cells(3)) AddItem Row
Next
End Sub

Of course you want the Bill Scheduling form to use Mod counting when assigning values to the Month due column

[mperrah]

I like this. We can use a users input + the 12 for the special modulus instead of just 12.
I will start building this, I will lean on you for userform comparability
the sheet manipulation should be straight foward but I'm very new with userforms and adapting for that is an exciting challenge. I really appreciate all the help and awesome ideas you are contributing.
It's like being a padawan with my own private Jedi academy

[mperrah]

So divide the frequency into 12 and have that be the multiplier. If it's yearly then just 12 and if it's once a year just need the start day. So we have 3 (or 4) variables: start day, frequency, holiday test, maybe end day. I think the modulus method will do great, just have the added qualifier for one time events.
For mortgages and car loans we can have a term variable for number of payments to help calculate end date

[SamT]

So divide the frequency into 12

I mistakenly used the term "modulus" as the divisor. In reality Modulus is the result, (the remainder,) of a Division by the Mod Operator.

1 Mod 2 = 1
2 Mod2 = 0
3 Mod 2 = 1
1 Mod 6 = 1
5 mod 6 = 5
6 Mod 6 = 0
7 Mod 6 = 1
11 Mod 6 = 5
12 Mod 6 = 0

For clarity, replace each instance of "Modulus" in my code above with "ModDivisor" and "MonthDue" with "ModMonthDue." Using the Prefix "Mod" to indicate that there is a relationship to the Mod Operator.

The AddBills Routine checks Month(CurrrentMonth) Mod ModDivisor and compares it to the ModMonthDue column. Note this routine is now complete

Sub AddBills(FreqTable As Range, ModDivisor As Long) 
     'Table Structure: Column1 = Bill name, 2 = Amount, 3 = DayDue, 4 = ModMonthDue
    ModMonthDue = Month(currentMonth) Mod ModDivisor 
    For each Row In FreqTable 
        If .Cells(4) = ModMonthDue Then BillControls(.Cells(3)) AddItem Row 
    Next 
End Sub

See Following Tables for Visual aid.

Monthly Bills Table: ModDivisor = 1
Name	Amount	DayDue	ModMonthDue
	all Months		0

Semi-Monthly Bills Table: ModDivisor = 2
Name	Amount	DayDue	ModMonthDue
	Jan,Mar,May		1
	Jul,Sep,Nov		1
	Feb,Apr,Jun		0
	Aug,Oct,Dec		0

Quarterly Bills Table: ModDivisor = 3
Name	Amount	DayDue	ModMonthDue
	Jan,Apr,Jul,Oct,		1
	Feb, May,Aug,Nov		2
	Mar,Jun,Sep,Dec		0

Semi-Annual Bills Table: ModDivisor = 6
Name	Amount	DayDue	ModMonthDue
		Jan, Jul	1
		Feb, Aug	2
		Mar, Sep	3
		Apr, Oct	4
		May, Nov	5
		Jun, Dec	0

Annual Bills Table: ModDivisor = 12
Name	Amount	DayDue	ModMonthDue
		Jan	1
		Feb	2
		Mar	3
		Apr	4
		…	…
		Nov	11
		Dec	0

Looking at the Quarterly Table, you see that a bill that is due in the first month of the quarter has the ModMonth number of 1. This means that once a recurring bill is entered into the table with a ModMonth number, you don't need to know what months it is due. The AddBills Routine will load it every time the CurrentMonth's Mod Result matches.

You can see that only when the Calendar Month number equals the ModDivisor is the ModMonthDue number = 0.

If the EnterNewBills Form asks the User "When is the bill is next due?" Then

ModMonthDue = Month(DateNexDue) Mod ModDivisor

LastDateDue must be a date and will need to be checked separately. Add a Column and compare if LastDateDue < Now Then Delete that Table Row, Shift:=Up

A Word of Warning!
I see Feature Creep coming into play. It can kill a project. Take what you have now and perfect it before adding any new features. As long as you keep modularizing the code like the AddNewBills, AddBillsToForm, and GetBills subs, it won't be hard to add features later, after this is perfected.

Get a notebook and put your new Features ideas in it. Reserve 4 pages for each feature, so you can work in them in the notebook. Take note of the writing style I have been using. Don't look at what I said, look at how I said it.

[mperrah]

This is marking the Every other Friday correctly now:

Sub pd_EoF()

Dim wknm As Date
Dim mywk As Long
Dim x, j As Long

With ufCal

 For j = 1 To 42
        .Controls("dnum" & j).Font.Bold = False
        .Controls("dnum" & j).Font.Size = 9
    Next j

    For x = 6 To 41 Step 7
        If .Controls("dnum" & x).Caption <> "" Then
            wknm = .uMonth.Caption & "-" & .Controls("dnum" & x).Caption & "-" & .uYear.Caption
            mywk = myWeekNum(wknm)
            If wknm Mod 2 = 1 Then
                .Controls("dnum" & x).Font.Bold = .ob_EoF.Value
                .Controls("dnum" & x).Font.Size = 11
            End If
        End If
    Next x
End With

End Sub

I will prepare a new post for the revised version of this project soon.
Thank you to SamT ans snb for all the help, instruction and truly inspiring ideas.

[mperrah]

ufCal snb_SamT.xlsm
Here is the working version.
There are several sheets with formulas in action for testing, they are not necessary but very informative.
-mark

[SamT]

I like the Bill sorting, that's handy!

It has been a pleasure watching you run away with this.

[mperrah]

Thank you Sam. Forgot the print routine -just noticed. I'll post that shortly
Ill add the spinner adjustments too

[mperrah]

ufCal snb_SamT.xlsm
This adds print function to userform and cleaned up the Payday option buttons. (still updating spinners)
enjoy,
It was a fun and enlightening project.
Thank you for the help!
SamT ans snd.

-mark

[mperrah]

Check this out,
Now the user can scroll up or down unlimited to update the month values. I love vba..

Private Sub sMonth_Change()
'when the up or down arrow is clicked change the Month and update the calendar day numbers and bill dates
    With ufCal
        Select Case .sMonth.Value
        Case Is = 0
            .uMonth.Caption = 12
            .uYear.Caption = .uYear.Caption - 1
            .sMonth.Value = 12
        Case Is = 13
            .uMonth.Caption = 1
            .uYear.Caption = .uYear.Caption + 1
            .sMonth.Value = 1
        Case Is > 0
            .uMonth.Caption = ufCal.sMonth.Value
        Case Is < 13
            ufCal.uMonth.Caption = ufCal.sMonth.Value
        End Select
    End With
    
    m_Cal
    pd_select
    
End Sub

[snb]

Read the help files on 'modulo'.

[SamT]

"The Change event occurs when the setting of the Value property changes, regardless of whether the change results from execution of code or a user action in the interface."

User Action changto 0 or 13 generates an additional code change. You may need to change the SpinButton Delay value for slower computers.

Private Sub sMonth1_Change()
'when the up or down arrow is clicked change the Calendar Month and Year Captions

    With ufCal
        Select Case .sMonth.Value
        Case Is = 0
            .uYear.Caption = CStr(CLong(.uYear.Caption) - 1)
            .sMonth.Value = 12
        Case Is = 1: .uMonth.Caption = "Jan"
        Case Is = 2: .uMonth.Caption = "Feb"
        Case Is = 3" .uMonth.Caption = "Mar"
        Case Is = 4: .uMonth.Caption = "Etc"
        Case Is = 5: .uMonth.Caption = "Etc"
        Case Is = 7: .uMonth.Caption = "Etc"
        Case Is = 8: .uMonth.Caption = "Etc"
        Case Is = 9: .uMonth.Caption = "Etc"
        Case Is = 10: .uMonth.Caption = "Etc"
        Case Is = 11: .uMonth.Caption = "Etc"
        Case Is = 12: .uMonth.Caption = "Etc"
        Case Is = 13
           .uYear.Caption = CStr(CLong(.uYear.Caption) + 1)
            .sMonth.Value = 1
        End Select
  End With
End Sub

[mperrah]

Snb: not finding help files on Modulo. Maybe I'm looking on wrong place. Do you have a link I could try ? I am curious

SamT: not sure what to change looking at your offering. are suggesting a different code trigger for the spinner? And what happens if the use is at Jan and clicks down with yours?

[xman2000]

hello mperrah !

i think you BillCalendar works very well with Bluecactus-Calendar.

Bluecactus Calendar have comments like OutlookCalendar
but calendarBluecactus not works!

link to Thread forum by me:
"vbaexpress.com/forum/showthread.php?55438-Calendar-by-BlueCactus-not-works"

if you have this workbook working please share with me!
thanks!