Calendar Data to Input to Excel like Outlooks built in scheduling form

[HeyItsDizzy]

Hey There is there a way I can create something similar to the outlook Calendar input form that I can use for budgeting analysis on my excel spreadsheet?

Is it crazy hard?
Would this need to be done with User forms?
Is there an alternate method you would suggest?

Thanks in advance!

Kind regards
Dizzy

 

[EFANYoutube]

Hi Dizzy,

Its not crazy hard, the thing you'll find hardest will be processing all the payment date data once you click OK but this can be done by seperate functions
Do you know how to code in VBA?

 

[HeyItsDizzy]

Hi Dizzy,

Its not crazy hard, the thing you'll find hardest will be processing all the payment date data once you click OK but this can be done by separate functions
Do you know how to code in VBA?

Yes I am all across the VBA I need to process the Date Data, once I have a list generated based on the conditions I apply.

All I need right now is a way to enter what ever financial variations I may need eg
-Monthly insurances are always 17th of each month except weekends and public holidays
- End of month accounts
- Quarterly (3 monthly) Gas & electricity
- Yearly Car registration
- Weekly income
- Fortnightly income

that's just to name a few There are countless iterations that may be required and the Outlook Form has every option required.

Note: I already have a Yearly calendar made up to use as a data source.
I've done a tonne of time on this already and wondering if there is a better way

so how can I create a form similar to the previously attached images but in excel so that it can spool out all the dates that meet the required criteria


Thanks Again! Dizzy

PS. I am not comfortable uploading the actual excel sheets, as on other pages has highly confidential data within it and it's not worth the hassle to do so because I have tonnes of 'Defined Names' and Linked cells to another tab call 'Data Source' etc etc etc.
Short answer is, it's just a hassle to upload the Worksheet.
Also, it is because all I am after is a direction of where I can go to next

 

[EFANYoutube]

Looks like you need to create a few functions to handle what to do with a date and a frequency

Have you thought of a select case statment?

Function NextDate(CurrentDate As Date, Freq As String) As Date
    Select Case Freq
        Case Is = "Monthly"
            NextDate = DateAdd("m", 1, CurrentDate)
            
        Case Is = "Weekly"
            NextDate = DateAdd("d", 7, CurrentDate)
        
        Case Is = "Yearly"
            NextDate = DateAdd("y", 1, CurrentDate)
            
    End Select
End Function

so on and so forth
of course you'll have to do extra checks when the date comes back from the function to see if it a weekend and change it accordingly but your could loop through as many iterations up to the end date as you like and write the data to sheet?

 

[HeyItsDizzy]

Th

Looks like you need to create a few functions to handle what to do with a date and a frequency

Have you thought of a select case statment?

Function NextDate(CurrentDate As Date, Freq As String) As Date
    Select Case Freq
        Case Is = "Monthly"
            NextDate = DateAdd("m", 1, CurrentDate)
           
        Case Is = "Weekly"
            NextDate = DateAdd("d", 7, CurrentDate)
       
        Case Is = "Yearly"
            NextDate = DateAdd("y", 1, CurrentDate)
           
    End Select
End Function

so on and so forth
of course you'll have to do extra checks when the date comes back from the function to see if it a weekend and change it accordingly but your could loop through as many iterations up to the end date as you like and write the data to sheet?

is seems like an ok answer but that’s only going to work for the ‘current date’ + (x) formulas
Which I have already written that formula and that was the easy one lol

This will not work if I need a payment processed the first Tuesday of every month (which I do have one of those also) and I have also written the formula for that too.

inwas just hoping for another solution as this has taken 40 hours to make so far and I keep finding errors occasionally

 

[EFANYoutube]

Hi Dizzy
I understand you would like an easy answer and hoped there was oe out there
Reality is, in this situation your choices are to build it yourself or purchase a ready made solution.
Don't give up, you will finish this but I think your question is too broad, most of the time on these sites your question will be answered if you break it down into smaller pieces and post each piece.
I have given you a small piece of the puzzle and below is another to answer your second question, but these things arn't always simple, you get the satisfaction of a job well done when you put in the work.

Sub createRec()
    Dim startcell As Range
    Set startcell = Sheet1.Range("A1")
    
    For i = 1 To 20 'number of iterations you have predefined
        startcell = "Expense Name"
        startcell.Offset(, 1) = NextDate(UserDefDate, UserDefFreq) ' so if its from a form or somewhere else in code
        startcell.Offset(, 2) = "Debit"
    Next i
End Sub

 

[HeyItsDizzy]

Hi Dizzy
I understand you would like an easy answer and hoped there was oe out there
Reality is, in this situation your choices are to build it yourself or purchase a ready made solution.
Don't give up, you will finish this but I think your question is too broad, most of the time on these sites your question will be answered if you break it down into smaller pieces and post each piece.
I have given you a small piece of the puzzle and below is another to answer your second question, but these things arn't always simple, you get the satisfaction of a job well done when you put in the work.

Sub createRec()
    Dim startcell As Range
    Set startcell = Sheet1.Range("A1")
   
    For i = 1 To 20 'number of iterations you have predefined
        startcell = "Expense Name"
        startcell.Offset(, 1) = NextDate(UserDefDate, UserDefFreq) ' so if its from a form or somewhere else in code
        startcell.Offset(, 2) = "Debit"
    Next i
End Sub

Awesome.
Thanks, EFAN

I didn't think there would be an easy solution, I just wanted to make sure I was on the right path or if there was a better one I should pursue.

I think I am on the right path, I just foresee a 25+ lined equation in my near future, as I need to make sure I used named ranges and keep the whole thing dynamic to avoid creating error by a simple mistype.

I think when I Build one I will sell it! because I can't find it anywhere out there.

Once Completed I'll post a link to the Completed Calendar input file here. as mentioned this has taken over 40 hours so far and I estimate another 80 hours required if not more.
So if I can save a few people 120 hours of work, then I will do the best I can!


If ar reader is reading this now and there is no link to this file here send me a private message on this board and I will give you an ETA on when I think it will be finished