View Single Post
 
Old 04-27-2018, 03:45 AM
gmayor's Avatar
gmayor gmayor is offline Windows 10 Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The code is simple enough - annotated below:

Code:
Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim oTable As Table
Dim EndDate As Date
Dim oCell As Range
Dim i As Long, j As Long
    If ContentControl.Tag = "Date" Then 'Process only the date content control
        If ContentControl.ShowingPlaceholderText = False Then 'Only process if it is not showing the placeholder text
            'Get the end date from the content control
            EndDate = CDate(ContentControl.Range.Text)
            'If it is not a Friday, get the next Friday
            EndDate = EndDate - Weekday(EndDate) + 6
            'Define which table to process (here table 2)
            Set oTable = ThisDocument.Tables(2)
            'Process the cells in the first row
            With oTable.Rows(1)
                'From cell 4 to cell 17
                For i = 4 To 17
                    'adjust the count to allow for the unwanted cells
                    j = 13 - i + 4
                    'Set a range to each cell in turn
                    Set oCell = .Cells(i).Range
                    'omit the end of cell character from the range
                    oCell.End = oCell.End - 1
                    'write the date in the cell
                    oCell.Text = Format(DateAdd("d", -j, EndDate), "dd/mm/yyyy")
                Next i
            End With
            'Process the cells in the third row
            With oTable.Rows(3)
                For i = 4 To 17
                    'eveything as row 1
                    j = 13 - i + 4
                    Set oCell = .Cells(i).Range
                    oCell.End = oCell.End - 1
                    'except the value is the first three letters of the day of the week
                    oCell.Text = Format(DateAdd("d", -j, EndDate), "ddd")
                Next i
            End With
        End If
    End If
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com

Last edited by gmayor; 04-27-2018 at 11:28 PM.
Reply With Quote