locked
Word 2010 - Add a year to a 2nd TextBox value?? RRS feed

  • Question

  • I have a text box which contains a one of a list of dates e.g. 1st January 2013, 1st February 2013, 1st March 2013 etc. I would like a 2nd TextBox to auto-populate with one year later. For example TextBox1 contains 1st January 2013 I would like TextBox2 to auto-populate with 31st December 2013.

    Quickest way to achieve this in VBA Word 2010 would be great.

    Thanks Guys & Gals

    Toni


    Toni Chaffin aka Talisa

    Wednesday, January 23, 2013 10:06 AM

Answers

  • Hi,

    This is possible, but could you wipe off the "st" after the day number. That is to say, the code need the date to be entered like "2 January 2013".

    Here's the code:

    Private Sub TextBox1_LostFocus()
    Dim str_date
    Dim date_format
    Dim date_result

    str_date = TextBox1.Value
    date_format = Format(str_date, "d mmmm yyyy")

    date_result = DateAdd("yyyy", 1, date_format)
    date_result = DateAdd("y", -1, date_result)
    date_result = Format(date_result, "d mmmm yyyy")

    TextBox2.Value = date_result

    End Sub


    Jaynet Zhang
    TechNet Community Support

    • Marked as answer by TSRC Thursday, January 24, 2013 7:04 AM
    Thursday, January 24, 2013 6:41 AM

All replies

  • Hi,

    This is possible, but could you wipe off the "st" after the day number. That is to say, the code need the date to be entered like "2 January 2013".

    Here's the code:

    Private Sub TextBox1_LostFocus()
    Dim str_date
    Dim date_format
    Dim date_result

    str_date = TextBox1.Value
    date_format = Format(str_date, "d mmmm yyyy")

    date_result = DateAdd("yyyy", 1, date_format)
    date_result = DateAdd("y", -1, date_result)
    date_result = Format(date_result, "d mmmm yyyy")

    TextBox2.Value = date_result

    End Sub


    Jaynet Zhang
    TechNet Community Support

    • Marked as answer by TSRC Thursday, January 24, 2013 7:04 AM
    Thursday, January 24, 2013 6:41 AM
  • Thank you for your answer. I got the the problem before I saw your answer, I ended up putting two controls on the document itself and then running this code to support it;

    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim StartDay As Integer
    Dim StartMonth As String
    Dim StartYear As Integer
    Dim EndDateDisplay As String
    Dim EndDateControls As ContentControls
    Dim EndDateControl As ContentControl
      If ContentControl.Title = "Start Date" Then
             ChosenDate = ContentControl.Range.Text
             StartDay = Left(ChosenDate, 1)
             StartMonth = Mid(ChosenDate, 5, Len(ChosenDate) - 9)
             StartYear = Right(ChosenDate, 4)
             EndDate = DateValue(StartDay & "/" & StartMonth & "/" & StartYear + 1) - 1
        EndDateDisplay = Day(EndDate) & Mid("thstndrdth", (DatePart("d", EndDate) Mod 10) * 2 + 1, 2) & " " & Format(EndDate, "mmmm yyyy")
                       
        Set EndDateControls = ActiveDocument.SelectContentControlsByTag("EndDate")
        For Each EndDateControl In EndDateControls
           EndDateControl.Range.Text = EndDateDisplay
          
        Next
      End If
    End Sub

    It is a bit Brute Force but works.

    Thanks

    Toni


    Toni Chaffin aka Talisa


    • Edited by TSRC Thursday, January 24, 2013 7:12 AM Update Text
    Thursday, January 24, 2013 7:04 AM