Can I update Date Picker Content Control with date and time inserted in it to add 90 days?

Question

Can I update Date Picker Content Control with date and time inserted in it to add 90 days?

Anonymous

I do not know how to write code. I found a code posted by another user that helped me update one date by adding 90 days. However, I want the start date to be today's date no matter what date it's opened. Will this code work for it (because it's not working)? where Date1 is today's date and Date2 is the second date with 90 days added.

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)

Application.ScreenUpdating = False

Dim Rng As Range

With CCtrl

If .Title <> "Date1" Then Exit Sub

Set Rng = ActiveDocument.SelectContentControlsByTitle("Date2")(1).Range

If .ShowingPlaceholderText Then: Rng.Text = "": Exit Sub

If IsDate(.Range.Text) Then

Rng.Text = Format(CDate(.Range.Text) + 90, CCtrl.DateDisplayFormat)

Else

MsgBox "Not a valid date!", vbExclamation: Rng.Text = ""

End If

End With

Application.ScreenUpdating = True

End Sub

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Answer 1

For what you want to do, you don't need a DatePicker.

For a plain date, all you need is a plain text content control. For example, with a content control titled 'USDate' and US date format:

Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)

Application.ScreenUpdating = False

Dim Rng As Range

With CCtrl

  If .Title <> "USDate" Then Exit Sub

  .Range.Text = Format(Now + 90, "MMM d, YYYY")

End With

Application.ScreenUpdating = True

End Sub

For a superscripted Ordinal date, you could use a rich text content control with:

Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)

Application.ScreenUpdating = False

Dim Rng As Range

With CCtrl

  If .Title <> "USDate" Then Exit Sub
  .Range.Text = Format(Now + 90, "MMM d, YYYY")

  Set Rng = .Range.Words(2): Rng.Text = Ordinal(Rng.Text): Rng.Start = Rng.End - 2: Rng.Font.Superscript = True

End With

Application.ScreenUpdating = True

End Sub

Function Ordinal(Val As Integer) As String

Dim strOrd As String

If (Val Mod 100) < 11 Or (Val Mod 100) > 13 Then strOrd = Choose(Val Mod 10, "st", "nd", "rd") & ""

Ordinal = Val & IIf(strOrd = "", "th", strOrd)

End Function

The content control will update immediately you enter it.

Similarly, if you have two Rich Text content controls, titled 'Date1' & 'Date2', a macro like:

Private Sub Document_ContentControlOnEnter(ByVal CCtrl As ContentControl)

Application.ScreenUpdating = False

Dim Rng As Range

With CCtrl

  If .Title <> "Date1" Then Exit Sub

  .Range.Text = Format(Now, "MMM d, YYYY")

  Set Rng = .Range.Words(2): Rng.Text = Ordinal(Rng.Text): Rng.Start = Rng.End - 2: Rng.Font.Superscript = True

  With ActiveDocument.SelectContentControlsByTitle("Date2")(1)

    .Range.Text = Format(Now + 90, "MMM d, YYYY")

    Set Rng = .Range.Words(2): Rng.Text = Ordinal(Rng.Text): Rng.Start = Rng.End - 2: Rng.Font.Superscript = True

  End With

End With

Application.ScreenUpdating = True

End Sub

Function Ordinal(Val As Integer) As String

Dim strOrd As String

If (Val Mod 100) < 11 Or (Val Mod 100) > 13 Then strOrd = Choose(Val Mod 10, "st", "nd", "rd") & ""

Ordinal = Val & IIf(strOrd = "", "th", strOrd)

End Function

will update both immediately you enter the 'Date1' content control - 'Date1' with today's date and 'Date2' with today's date +90.

Answer 2

Doug Robbins - MVP - Office Apps and Services 323K MVP Volunteer Moderator

Do you really need to use a DatePicker Content Control?

See Macropod's Word Date Calculation Tutorial at:

http://www.gmayor.com/downloads.htm#Third\_party

0 comments

Share via

Can I update Date Picker Content Control with date and time inserted in it to add 90 days?

2 answers