user form dates

theYaniac

New Member
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
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
In what way was it unsuccessful?
 

theYaniac

New Member
'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
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
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
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top