Excel 2013 64 bit - VBA Userform - Add 28 days to date of textbox and display in another textbox

Question

In my Userform I have a textbox "ADD_Inc_DATE_TXT" which the user manually enters the date of the incident.

I have another textbox "TxT_SWIRL_DueDate" which should display the result of the adding of 28 days to the date entered in the "ADD_Inc_DATE_TXT" textbox or even just add 1 month to the date of the incident.

I have tried this in a module named "Mod_SWIRL_Due_Date":

Sub SWIRL_ExpiryDate()

    TxT_SWIRL_DueDate = CDate(ADD_Inc_DATE_TXT)

    ADD_Inc_DATE_TXT = DateAdd("m", 1, ADD_Inc_DATE_TXT)

End Sub

But this doesn't seem to do anything at all.

I would like the "TxT_SWIRL_DueDate" to display the expiry date when the "ADD_INC_Time_TXT" textbox is selected.

And in anticipation of the User amending the Incident Date, I'd like it to update the TxT_SWIRL_DueDate with the amended date.

Further Information: I have included below all the code I have relating to dates. I have 4 Date Pickers/Popup Calendars ... and none of the dates come up in the dd/mm/yyyy format. (having some problem with putting the code in the 'code sample' )

    Private Sub Calendar1_Click()
     ADD_Inc_DATE_TXT.value = CalendarForm.GetDate
     If IsDate(ADD_Inc_DATE_TXT.Text) Then
     Me.LBL_Inc_Day_Type.Caption = Format(ADD_Inc_DATE_TXT.Text, "ddd")

    End If
     If IsDate(ADD_Inc_DATE_TXT.Text) Then
     Me.ADD_Inc_DATE_TXT.Text = Format(ADD_Inc_DATE_TXT.Text, "dd/mm/yyyy") 
    End If
    End Sub

    Private Sub Calendar2_Click()
     TXT_AssetMgr_DATE = CalendarForm.GetDate
       If IsDate(ADD_Inc_DATE_TXT.Text) Then
        Me.TXT_AssetMgr_DATE.Text = Format(TXT_AssetMgr_DATE.Text, "dd/mm/yyyy")
    End If
    End Sub


    Private Sub Calendar3_Click()
     TXT_LastUserDATE = CalendarForm.GetDate
     If IsDate(ADD_Inc_DATE_TXT.Text) Then
     Me.TXT_LastUserDATE.Text = Format(TXT_LastUserDATE.Text, "dd/mm/yyyy")
    End If
    End Sub

    Private Sub Calendar4_Click()
      ADD_Date_ServiceJobLogged_TXT = CalendarForm.GetDate
      If IsDate(ADD_Inc_DATE_TXT.Text) Then
      Me.ADD_Date_ServiceJobLogged_TXT.Text =  Format(ADD_Date_ServiceJobLogged_TXT.Text, "dd/mm/yyyy")
     End If
     End Sub

‘Under Private Sub UserForm_Initialize() I have the following date related code: Private Sub UserForm_Initialize()

            Me.ADD_Date_Recorded_TXT.value = Format(Now, "dd/mm/yyyy") ‘ this works perfectly (correct format is returned)
            Me.ADD_Time_Recorded_TXT.Text = Format(Now(), "HH:mm")

‘***Note: my system date is dd/mm/yyyy and in the spreadsheet cells, the format is set to dd/mm/yyyy

vacip vacip · Accepted Answer · 2016-05-08T14:00:45

Use this code in the form's code module (right click the text box, "View code"):

Private Sub ADD_Inc_DATE_TXT_AfterUpdate()
  TxT_SWIRL_DueDate.Value = Format(DateAdd("m", 1, CDate(ADD_Inc_DATE_TXT.Value)),"dd/mm/yyyy")
End Sub

I recommend useing the AfterUpdate event, as the Change event fires every time the value in the textbox gets changed - eg also while typing. This only fires when the user has finished typing, and moved to the next tiem on the form (clicks somewhere else).

Also consider using some form of date control instead of textbox control. There are quite a few out there, and depending on the Excel version you are using, you may only be able to use a select few of those.

Excel 2013 64 bit - VBA Userform - Add 28 days to date of textbox and display in another textbox

1 Answers