VBA auto due date

[GingerBeardo]

I have a user form that accepts dates that certs have been issued, then when the "submit" button is hit sends it to the corresponding column on the sheet. I would like for what is in the columns to show the due date instead but the options I have googled either arent for my specific needs or I dont understand. attached is my code, right now it just sends the input to the sheet as is.

Private Sub Label9_Click()

End Sub

Private Sub UserForm_Initialize()
With cmbPosition
    .AddItem "Trainee"
    .AddItem "Operator I"
    .AddItem "Operator II"
    .AddItem "Operator III"
End With

End Sub
Private Sub cmdSubmit_Click()

    Dim ws As Worksheet
    Dim freeRow As Long
    
             
        If cmbPosition.Text = "" Or txtFName.Text = "" Or txtLName.Text = "" Or txtDOB.Text = "" Or txtPhone.Text = "" Or txtLast4.Text = "" Then
    
             Else
                Set ws = ActiveSheet
                    freeRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1 'End(xlUp) jumps to the next free cell

             If Not ws Is Nothing Then
                ws.Cells(freeRow, 2) = cmbPosition.Text
                ws.Cells(freeRow, 3) = txtFName.Text
                ws.Cells(freeRow, 4) = txtLName.Text
                ws.Cells(freeRow, 5) = txtDOB.Text
                ws.Cells(freeRow, 6) = txtLast4.Text
                ws.Cells(freeRow, 7) = txtPhone.Text
                ws.Cells(freeRow, 9) = txtPEC.Text
                ws.Cells(freeRow, 10) = txtH2S.Text
                ws.Cells(freeRow, 11) = txtCS.Text
                ws.Cells(freeRow, 12) = txtFT.Text
                ws.Cells(freeRow, 13) = txtFA.Text
                ws.Cells(freeRow, 14) = txtCPR.Text
                ws.Cells(freeRow, 15) = txtBBP.Text
                ws.Cells(freeRow, 16) = txtFS.Text
                ThisWorkbook.Save
                cmbPosition.Text = ""
                txtFName.Text = ""
                txtLName.Text = ""
                txtDOB.Text = ""
                txtPhone.Text = ""
                txtLast4.Text = ""
                txtPEC.Text = ""
                txtH2S.Text = ""
                txtCS.Text = ""
                txtFT.Text = ""
                txtFA.Text = ""
                txtCPR.Text = ""
                txtBBP.Text = ""
                txtFS.Text = ""
                cmbPosition.SetFocus
            
            End If
        End If
End Sub

 

[StephenCrump]

Your question is light on detail ...

- Where does the due date come from? From the Userform? Or is it calculated? If so, how?
- Where in the worksheet do you want to put the due date?

 

[GingerBeardo]

Your question is light on detail ...

- Where does the due date come from? From the Userform? Or is it calculated? If so, how?
- Where in the worksheet do you want to put the due date?

Sorry, the date issued will be entered in the form, I want to calculate the due date from that, e.g. issue date 10/14/2020 is entered, after hitting submit VBA magic would calculate the due date from a predetermined number in the code, then drop it in the row that the rest of the information will go to (next free row) under the applicable column. attached is what the sheet looks like and the area the calculated date will drop in.

 

[StephenCrump]

So perhaps some extra code lines something like:

Const NoDays = 30

'...

With ws.Cells(freeRow, 17)  'adjust column number 17 as required
    .Value = CVDate(txtYourDateField.Text) + NoDays
    .NumberFormat = "mm/dd/yyyy"    
End With
    
'...

txtYourDateField.Text = ""

 

[GingerBeardo]

So perhaps some extra code lines something like:

Const NoDays = 30

'...

With ws.Cells(freeRow, 17)  'adjust column number 17 as required
    .Value = CVDate(txtYourDateField.Text) + NoDays
    .NumberFormat = "mm/dd/yyyy"   
End With
   
'...

txtYourDateField.Text = ""

I'm sorry again, I tried putting this where I thought it'd go but I received an error for duplicate declaration, and the other spot it didn't work so I assume I'm not putting it in the right place.

 

[StephenCrump]

I haven't tested this, and you'll need to adjust names/columns etc for your layout. I have also assumed a simple: Due date = date + 30, which may not be correct for you?

Private Sub cmdSubmit_Click()

    Dim ws As Worksheet
    Dim freeRow As Long
    Const NoDays = 30
    
    If Not (cmbPosition.Text = "" Or txtFName.Text = "" Or txtLName.Text = "" Or txtDOB.Text = "" Or txtPhone.Text = "" Or txtLast4.Text = "") Then
        Set ws = ActiveSheet
        freeRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1 'End(xlUp) jumps to the next free cell
        ws.Cells(freeRow, 2) = cmbPosition.Text
        ws.Cells(freeRow, 3) = txtFName.Text
        ws.Cells(freeRow, 4) = txtLName.Text
        ws.Cells(freeRow, 5) = txtDOB.Text
        ws.Cells(freeRow, 6) = txtLast4.Text
        ws.Cells(freeRow, 7) = txtPhone.Text
        ws.Cells(freeRow, 9) = txtPEC.Text
        ws.Cells(freeRow, 10) = txtH2S.Text
        ws.Cells(freeRow, 11) = txtCS.Text
        ws.Cells(freeRow, 12) = txtFT.Text
        ws.Cells(freeRow, 13) = txtFA.Text
        ws.Cells(freeRow, 14) = txtCPR.Text
        ws.Cells(freeRow, 15) = txtBBP.Text
        ws.Cells(freeRow, 16) = txtFS.Text
        With ws.Cells(freeRow, 17)  'adjust column number 17 as required
            .Value = CVDate(txtYourDateField.Text) + NoDays
            .NumberFormat = "mm/dd/yyyy"
        End With
        ThisWorkbook.Save
        cmbPosition.Text = ""
        txtFName.Text = ""
        txtLName.Text = ""
        txtDOB.Text = ""
        txtPhone.Text = ""
        txtLast4.Text = ""
        txtPEC.Text = ""
        txtH2S.Text = ""
        txtCS.Text = ""
        txtFT.Text = ""
        txtFA.Text = ""
        txtCPR.Text = ""
        txtBBP.Text = ""
        txtFS.Text = ""
        txtYourDateField.Text = ""
        cmbPosition.SetFocus
    End If

End Sub