VBA Form Autofill

[nanofied]

Hi guys, I have a form with many text boxes. The 2 text boxes for importance in my query is "Date of Commencement" and "Estimated Date of Completion". The "Estimated Date of Completion" is to be 120 days after the "Date of Commencement". By using the code:

EstimatedDateofCompletion.Value =  DateAdd("d", 120, DateofCommencement.Value

I wish to autofill "Estimated Date of Completion" text box. however I have been receiving a type mismatch error. How many I solve this issue. or code it in a better way

 

[My Aswer Is This]

Try this:
EstimatedDateofCompletion = DateAdd("d", 120, CDate(DateofCommencement))

 

[nanofied]

Thanks! I have another question. In my form after I had type in the DateofCommencement textbox, the value in EstimatedDateofCompletion does not refresh automatically. The value will only be inputted if I make a change to the the form. Is there anyway to for the value to be inputted immediately and automatically without making a change

 

[My Aswer Is This]

Put this script in your userform

Private Sub DateofCommencement_Exit(ByVal Cancel As MSForms.ReturnBoolean)
EstimatedDateofCompletion = DateAdd("d", 120, CDate(DateofCommencement))
End Sub

You enter the date you want in the

DateofCommencement

Textbox then when you press tab or some how exit the textbox the script will run.

​

 

[nanofied]

Thank you! It worked well for me!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you! It worked well for me!

 

[My Aswer Is This]

Now that we have you what you want.
Use this script:

This script will warn you and stop the script if a improper value is entered which is not a date.

Private Sub DateofCommencement_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Modified  11/12/2018  4:10:23 AM  EST
On Error GoTo M
EstimatedDateofCompletion = DateAdd("d", 120, CDate(DateofCommencement))
Exit Sub
M:
MsgBox "The value you entered is not a proper date"
End Sub