user form dates

[theYaniac]

I would like for a date being taken from a user form to have one year added to the date. For example if you enter 1-9-18 in the text box on the form I would like the date that goes into the data sheet to be 1-9-19. Any help would be greatly appreciated

 

[dmt32]

Hi,
see if following suggestion does what you want

     Dim sDate As Date
    With Me.TextBox1
        If IsDate(.Text) Then
        sDate = DateAdd("yyyy", 1, .Text)
        End If
    End With

the variable sDate should be applied in your project as required.

Dave

 

[theYaniac]

This attempt was unsuccessful

 

[dmt32]

This attempt was unsuccessful

Worked ok for me - Post your All then forms code so I & others here can assist further


Dave

 

[Norie]

In what way was it unsuccessful?

 

[theYaniac]

'Begin Data Input to Database
Sheets("Data").Range("Data_Start").Offset(TargetRow, 0).Value = TargetRow
Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value = Txt_FirstName 'first name
Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value = Txt_LastName 'last name
Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value = Txt_FirstName & " " & Txt_LastName 'full name
Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value = Txt_Phone 'contact number
Sheets("Data").Range("Data_Start").Offset(TargetRow, 5).Value = Combo_Craft 'craft
Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value = Combo_Classification 'classification
Sheets("Data").Range("Data_Start").Offset(TargetRow, 7).Value = Combo_Group 'group affiliation
Sheets("Data").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_BadgeNumber 'BP badge number
Sheets("Data").Range("Data_Start").Offset(TargetRow, 9).Value = Txt_AKIDNumber 'L&I ID number
Sheets("Data").Range("Data_Start").Offset(TargetRow, 10).Value = Txt_DrivingCert 'BP driving cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 11).Value = Txt_ATFLCert 'All terrain forklift cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 12).Value = Txt_MLCert 'manlift cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 13).Value = Txt_RespCert 'respirator cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 14).Value = Txt_CSECert 'confined space entry cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 15).Value = Txt_CSACert 'confined space attendant cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 16).Value = Txt_LOTOCert 'lockout tagout cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 17).Value = Txt_SkidSteerCert 'bobcat cert
Sheets("Data").Range("Data_Start").Offset(TargetRow, 18).Value = Txt_FELCert 'front end loader cert

This the code that takes the information out of the user form and enters it in the data sheet. The underlined rows are for the text boxes that will have a date entered in the user form. It is to the entered date that I would like the year increased by one. Training date is 1/1/2018 and data in sheet will display as 1/1/2019

 

[dmt32]

Hi,
your code just shows data going from the forms textboxes to your worksheet - have not shown how you attempted to incorporate my suggestion into your project.

If not sure how to see if this update to code you have shared does what you want

Placing following in forms code page replacing existing code

'Begin Data Input to Database
With Sheets("Data").Range("Data_Start")
    .Offset(TargetRow, 0).Value = TargetRow
    .Offset(TargetRow, 1).Value = Txt_FirstName 'first name
    .Offset(TargetRow, 2).Value = Txt_LastName 'last name
    .Offset(TargetRow, 3).Value = Txt_FirstName & " " & Txt_LastName 'full name
    .Offset(TargetRow, 4).Value = Txt_Phone 'contact number
    .Offset(TargetRow, 5).Value = Combo_Craft 'craft
    .Offset(TargetRow, 6).Value = Combo_Classification 'classification
    .Offset(TargetRow, 7).Value = Combo_Group 'group affiliation
    .Offset(TargetRow, 8).Value = Txt_BadgeNumber 'BP badge number
    .Offset(TargetRow, 9).Value = Txt_AKIDNumber 'L&I ID number
    
'increment dates + 1 year
    .Offset(TargetRow, 10).Value = GetDate(Txt_DrivingCert) 'BP driving cert
    .Offset(TargetRow, 11).Value = GetDate(Txt_ATFLCert) 'All terrain forklift cert
    .Offset(TargetRow, 12).Value = GetDate(Txt_MLCert) 'manlift cert
    .Offset(TargetRow, 13).Value = GetDate(Txt_RespCert) 'respirator cert
    .Offset(TargetRow, 14).Value = GetDate(Txt_CSECert) 'confined space entry cert
    .Offset(TargetRow, 15).Value = GetDate(Txt_CSACert) 'confined space attendant cert
    .Offset(TargetRow, 16).Value = GetDate(Txt_LOTOCert) 'lockout tagout cert
    .Offset(TargetRow, 17).Value = GetDate(Txt_SkidSteerCert) 'bobcat cert
    .Offset(TargetRow, 18).Value = GetDate(Txt_FELCert) 'front end loader cert
End With

Place following Function either in standard module or your forms code page

Function GetDate(ByVal Text As String) As Variant
    If IsDate(Text) Then GetDate = DateValue(DateAdd("yyyy", 1, Text)) Else GetDate = (Text)
End Function

Solution is untested but should if a valid date is entered in a textboxes increment the year + 1 otherwise it will just return what was entered.


ALWAYS MAKE BACKUP OF YOUR WORKBOOK BEFORE TESTING NEW CODE>

Dave

 

[theYaniac]

Where would I insert the first portion of code you provided?

Dim sDate As Date
With Me.TextBox1
If IsDate(.Text) Then
sDate = DateAdd("yyyy", 1, .Text)
End If
End With

 

[dmt32]

Have you tried the update in #post 7?

Dave

 

[theYaniac]

When I insert the function within the code it returns a compile error : Expected End Sub. It is not changing anything where it is now. The following is the entire code entry for the user form:

Private Sub CommandButton1_Click()
Dim TargetRow As Integer
Dim FullName As String 'full name
Application.ScreenUpdating = False
Sheets("Engine").Visible = True
If Sheets("Engine").Range("B4").Value = "NEW" Then
TargetRow = Sheets("Engine").Range("B3").Value + 1
Else
TargetRow = Sheets("Engine").Range("B5").Value
End If
FullName = Txt_FirstName & " " & Txt_LastName
If Sheets("Engine").Range("B4").Value = "NEW" Then
'begin validation check
If Application.WorksheetFunction.CountIf(Sheets("Data").Range("E8:E10008"), FullName) > 0 Then
MsgBox "Name already exists", 0, "Check"
Exit Sub
End If
End If
'Begin Data Input to Database
With Sheets("Data").Range("Data_Start")
.Offset(TargetRow, 0).Value = TargetRow
.Offset(TargetRow, 1).Value = Txt_FirstName 'first name
.Offset(TargetRow, 2).Value = Txt_LastName 'last name
.Offset(TargetRow, 3).Value = Txt_FirstName & " " & Txt_LastName 'full name
.Offset(TargetRow, 4).Value = Txt_Phone 'contact number
.Offset(TargetRow, 5).Value = Combo_Craft 'craft
.Offset(TargetRow, 6).Value = Combo_Classification 'classification
.Offset(TargetRow, 7).Value = Combo_Group 'group affiliation
.Offset(TargetRow, 8).Value = Txt_BadgeNumber 'BP badge number
.Offset(TargetRow, 9).Value = Txt_AKIDNumber 'L&I ID number
'increment dates + 1 year
.Offset(TargetRow, 10).Value = Txt_DrivingCert 'BP driving cert
.Offset(TargetRow, 11).Value = Txt_ATFLCert 'All terrain forklift cert
.Offset(TargetRow, 12).Value = Txt_MLCert 'manlift cert
.Offset(TargetRow, 13).Value = Txt_RespCert 'respirator cert
.Offset(TargetRow, 14).Value = Txt_CSECert 'confined space entry cert
.Offset(TargetRow, 15).Value = Txt_CSACert 'confined space attendant cert
.Offset(TargetRow, 16).Value = Txt_LOTOCert 'lockout tagout cert
.Offset(TargetRow, 17).Value = Txt_SkidSteerCert 'bobcat cert
.Offset(TargetRow, 18).Value = Txt_FELCert 'front end loader cert
End With
Sheets("Engine").Visible = xlVeryHidden
Unload NewEmployee_UF 'close the user form
MsgBox FullName & " was added to database", 0, "Complete"
Application.ScreenUpdating = True
End Sub
Function GetDate(ByVal Text As String) As Variant
If IsDate(Text) Then GetDate = DateValue(DateAdd("yyyy", 1, Text)) Else GetDate = (Text)
End Function




Private Sub CommandButton2_Click() 'Cancel


Unload NewEmployee_UF


End Sub