VBA Userform Loop through Due Date textboxes.

[Roe_01]

Hello -
I am trying to loop 17 textboxes after a user needs to edit the record and update the Due Date in txtDueDate1. The screen shot below shows some textboxes in grey because they are default values when the userform is launched. I read many threads and watch videos on looping through textboxes but I can't get the code below to work and must be missing something simple. When a user selects a record from the Listbox to edit and changes the Due Date in txtDueDate1 then when clicking the Update Due Dates button, I want to subsequent dates to get updated based on Calendar or Business Days. I am coming across two (2) errors.

1) A mismatch error where the TDD value is blank
2) The Business Days (WorksheetFunction.WorkDay) is not working and message says argument not optional so I don't know how to solve it.


Below is the code and I hope anyone can help.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim TDD
Dim txtDays
Dim txtBusCal
For i = 2 To 17
Controls("txtDueDate" & i).Value = "<>"

If Controls("txtBusCal" & i).Value = "Calendar" Then
TDD.Value = CDate(Controls("txtDueDate" & i - 1).Value) + (Controls("txtDays" & i).Value)
TDD.Value = Format(CDate(TDD.Value))

Else
If Controls("txtBusCal" & i).Value = "Business" Then
TDD.Value = WorksheetFunction.WorkDay(CDate(Controls("txtDueDate" & i - 1).Value)) + (Controls("txtDays" & i).Value)
TDD.Value = Format(CDate(TDD.Value))

End If
End If
Next i
End Sub

 

[Alex Blakenburg]

1) A mismatch error where the TDD value is blank

CDate("") will give you that, what do you want the code to do if TDD is blank ?

2) The Business Days (WorksheetFunction.WorkDay) is not working and message says argument not optional so I don't know how to solve it.

The syntax for the WorkDay function is:
with only holidays being optional

=WORKDAY(start_date, days, [holidays])

Your VBA version is missing the "days" parameter it is a required parameter and you need to supply it.
Is your plus meant to be a "," and lead to the 2nd parameter.
eg untested.

TDD.Value = WorksheetFunction.WorkDay(CDate(Controls("txtDueDate" & i - 1).Value), Controls("txtDays" & i).Value)

 

[Roe_01]

Alex -
Thank you for your feedback. Please see my responses below.

1) CDate("") - All dates are required in order for the record to be saved and I have a msgbox appear if the Days field is blank since it is one of the conditions to calculate the Due Date. Below is a screen shot showing what happens to TDD2 & TDD3 when I change the date in TDD1 when editing the record. It seems to be with Controls("txtDueDate" & i).Value = "<>" but when I comment the Controls then I get the object error. I must be doing something wrong with the controls.

2) WorkDayFunction - You were right about the (+) and it was my error. However, when I copied the updated code you provided, I get an Object error and the TDD2 doesn't change.

I decided to try the code below for txtDueDate1 AfterUpdate event and Exit event for the other textboxes and it seems to work. As you can see, it is the same steps as the looping code so I must be missing something.

Private Sub txtDueDate1_AfterUpdate()
If txtBusCal2.Value = "Calendar" Then
txtDueDate2.Value = CDate(txtDueDate1.Value) + (txtDays2.Value)
txtDueDate2.Value = Format(CDate(txtDueDate2.Value))

Else
txtDueDate2.Value = WorksheetFunction.WorkDay(CDate(txtDueDate1.Value), txtDays2.Value)
txtDueDate2.Value = Format(CDate(txtDueDate2.Value))
End If
End Sub

Private Sub txtDueDate2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If txtBusCal3.Value = "Calendar" Then
txtDueDate3.Value = CDate(txtDueDate2.Value) + (txtDays3.Value)
txtDueDate3.Value = Format(CDate(txtDueDate3.Value))

Else
txtDueDate3.Value = WorksheetFunction.WorkDay(CDate(txtDueDate2.Value), txtDays3.Value)
txtDueDate3.Value = Format(CDate(txtDueDate3.Value))
End If
End Sub

 

[Roe_01]

Hi Alex -
The problem was that I was missing the word controls in the code below. Thank you very much for your help and I will mark as solution.
Set TDD = Controls("txtDueDate" & i)
Roe

 

[Alex Blakenburg]

Thanks for coming back and letting me know. Glad I was of some help.