user form dates

theYaniac

New Member
Joined
Jan 7, 2018
Messages
31
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,134
Hi,
see if following suggestion does what you want

Code:
     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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,277
In what way was it unsuccessful?
 

theYaniac

New Member
Joined
Jan 7, 2018
Messages
31
'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

Well-known Member
Joined
Jul 3, 2012
Messages
5,134
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

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

Code:
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

New Member
Joined
Jan 7, 2018
Messages
31
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
 

theYaniac

New Member
Joined
Jan 7, 2018
Messages
31
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
 

Forum statistics

Threads
1,077,721
Messages
5,335,812
Members
399,051
Latest member
WPO

Some videos you may like

This Week's Hot Topics

Top