VBA Userform automatically populate due dates based on Calendar or Business Days

Question

Hello -

I am unable to find a way to automatically calculate Due Dates based on textbox value (i.e. Calendar or Business) for each textbox on a userform. The code below populates the the textboxes once I enter a date in txtDueDate1.text. However, I can't figure out how to add a condition to check if txtBusCal1, txtBusCal2, txtBusCal3, txtBusCal4, txtBusCal5, etc. is Calendar versus Business and automatically populate the Due Dates.

Any assistance would be greatly appreciated since I spent days searching, reading, and applying potential solutions from the various posts.

I tried the following:

1. I created separate Sub for each Due Date, but the Due Dates stay Empty
   • Private Sub txtDueDate2_AfterUpdate() - (Me.txtDueDate3.Text = CDate(Me.txtDueDate2.Text) + (Me.txtDays3.Value) )
   • Private Sub txtDueDate3_AfterUpdate(),(Me.txtDueDate4.Text = CDate(Me.txtDueDate3.Text) + (Me.txtDays4.Value) )
2. I tried Else after each Due Date textbox but the Due Dates stay empty.
   • If txtBusCal2.value = "Calendar Then Me.txtDueDate2.Text = CDate(Me.txtDueDate1.Text) + (Me.txtDays2.Value) Else
   • If txtBusCal3.value = "Calendar Then Me.txtDueDate3.Text = CDate(Me.txtDueDate2.Text) + (Me.txtDays3.Value)
3. I couldn't figure out how to apply And / Or Statements or even if it will work.
   • Me.TxtDueDate2 = CDate(me.txtDueDate1.text) & txtBusCal2.value = "Calendar" + (Me.txtDays2.Value)
   • Me.TxtDueDate3 = CDate(me.txtDueDate2.text) & txtBusCal3.value = "Calendar" + (Me.txtDays3.Value)
   • Me.TxtDueDate4 = CDate(me.txtDueDate3.text) & txtBusCal4.value = "Calendar"+ (Me.txtDays4.Value)

Private Sub txtDueDate1_AfterUpdate()

If IsDate(Me.txtDueDate1.Text) Then

Me.txtDueDate2.Text = CDate(Me.txtDueDate1.Text) + (Me.txtDays2.Value)

Me.txtDueDate2.Text = Format(CDate(Me.txtDueDate2.Text))

Me.txtDueDate3.Text = CDate(Me.txtDueDate2.Text) + (Me.txtDays3.Value)

Me.txtDueDate3.Text = Format(CDate(Me.txtDueDate3.Text))

Me.txtDueDate4.Text = CDate(Me.txtDueDate3.Text) + (Me.txtDays4.Value)

Me.txtDueDate4.Text = Format(CDate(Me.txtDueDate4.Text))

Me.txtDueDate5.Text = CDate(Me.txtDueDate4.Text) + (Me.txtDays5.Value)

Me.txtDueDate5.Text = Format(CDate(Me.txtDueDate5.Text))

Me.txtDueDate6.Text = CDate(Me.txtDueDate5.Text) + (Me.txtDays6.Value)

Me.txtDueDate6.Text = Format(CDate(Me.txtDueDate6.Text))

Else

Me.txtDueDate2.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate1.Text), Me.txtDays2.Value)

Me.txtDueDate2.Text = Format(CDate(Me.txtDueDate2.Text))

Me.txtDueDate3.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate2.Text), Me.txtDays3.Value)

Me.txtDueDate3.Text = Format(CDate(Me.txtDueDate3.Text))

Me.txtDueDate4.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate3.Text), Me.txtDays4.Value)

Me.txtDueDate4.Text = Format(CDate(Me.txtDueDate4.Text))

Me.txtDueDate5.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate4.Text), Me.txtDays5.Value)

Me.txtDueDate5.Text = Format(CDate(Me.txtDueDate5.Text))

Me.txtDueDate6.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate5.Text), Me.txtDays6.Value)

Me.txtDueDate6.Text = Format(CDate(Me.txtDueDate6.Text))

(TxtDays1 thru txtDays6) ( Txt.BusCal1 thru Txt.BusCal6) (TxtDueDate1 thru TxtDueDate6)

Days	Business / Calendar	Due Date
0	Calendar	2/3/2023
1	Business	2/4/2023
2	Calendar	2/6/2023
5	Business	2/11/2023
5	Business	2/16/2023
5	Business	2/21/2023

Answer 1

Great

Happy to hear that.

Answer 2

Hello -

I was able to come up with a solution by keeping Private Sub txtDueDate1_AfterUpdate() then using txtDueDate2**_Change()** for each of the text box due dates. See the screen shot and code below. Once I enter the first date, the code will automatically calculate and populate each Due Date based on a Calendar or Business Day.

I hope this may help other users. I will remove public access and delete the file shared on OneDrive.

Private Sub txtDueDate1_AfterUpdate()

If Me.txtBusCal2.Text = "Calendar" Then

Me.txtDueDate2.Text = CDate(Me.txtDueDate1.Text) + (Me.txtDays2.Value)

Me.txtDueDate2.Text = Format(CDate(Me.txtDueDate2.Text))

Else

Me.txtDueDate2.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate1.Text), Me.txtDays2.Value)

Me.txtDueDate2.Text = Format(CDate(Me.txtDueDate2.Text))

End If

End Sub

Private Sub txtDueDate2_Change()

If Me.txtBusCal3.Text = "Calendar" Then

Me.txtDueDate3.Text = CDate(Me.txtDueDate2.Text) + (Me.txtDays3.Value)

Me.txtDueDate3.Text = Format(CDate(Me.txtDueDate3.Text))

Else

Me.txtDueDate3.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate2.Text), Me.txtDays3.Value)

Me.txtDueDate3.Text = Format(CDate(Me.txtDueDate3.Text))

End If

End Sub

Private Sub txtDueDate3_Change()

If Me.txtBusCal4.Text = "Calendar" Then

Me.txtDueDate4.Text = CDate(Me.txtDueDate3.Text) + (Me.txtDays4.Value)

Me.txtDueDate4.Text = Format(CDate(Me.txtDueDate4.Text))

Else

Me.txtDueDate4.Text = WorksheetFunction.WorkDay(CDate(Me.txtDueDate3.Text), Me.txtDays4.Value)

Me.txtDueDate4.Text = Format(CDate(Me.txtDueDate4.Text))

End If

End Sub

Answer 3

Hello Jeovany -

Thank you for your response and below is the link to the file posted onto OneDrive.

UserForm_Mockup.xlsm

I really appreciate your help.

Thank you,

Roe

Answer 4

Hi Roe_M

It is difficult to visualize and/or reproduce your scenario and requirement with the current information,  your worksheet is too complex to give you a solution without having access to your file.

Kindly suggest preparing and uploading a sample file to Onedrive, Dropbox, etc ... and share the link here.

Please first,

a)  Remove any personal/sensitive data.

b) Keep the headers, table structure, and formulas as they actually are.

c) ZIP the file if contains macros, pictures, shapes, or other objects.

d) Provide more details of your scenario and goals.  Also, post the results in the way you expect.

This will help us to give you a prompt and right solution.

If you need help with how to upload the file please, click the link below

https://support.office.com/en-us/article/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07

You may also follow the instructions in this video

https://www.youtube.com/watch?v=NnXsE0SNuCc&t=14s

Regards

Jeovany