Microsoft Office Forums - View Single Post - [Solved] Calculation to Pre poutlate Text

gmayor · 08-19-2015, 08:54 PM

You don't need more macros, you just need more case statements or more calculations e.g. as follows, which will also format with comma thousands separators. Here the field names are PA, PSF and PSM. You will have to provide your own calculations, those included are just examples.

Code:

Option Explicit

Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, Cancel As Boolean)
Dim oCC As ContentControl
    Select Case CC.Title
        Case "SQFT"
            'Validate/format data entered in variable content control.
            If Not IsNumeric(CC.Range.Text) Then
                Cancel = True
                Beep
                CC.Range.Text = "0.00"
                With ActiveDocument.SelectContentControlsByTitle("SQM").Item(1)
                    .LockContents = False
                    .Range.Text = "0.00"
                    .LockContents = True
                End With
                CC.Range.Select
                Exit Sub
            Else
                CC.Range.Text = Format(Val(CC.Range.Text), "#,###,##0.00")
            End If
            'Update content control contents.
            Set oCC = ActiveDocument.SelectContentControlsByTitle("SQM").Item(1)
            With oCC
                .LockContents = False
                .Range.Text = Format(Val(ActiveDocument.SelectContentControlsByTitle("SQFT").Item(1).Range.Text * 0.09290304), "#,###,##0.00")
                .LockContents = True
            End With
        Case "PA"
            If Not IsNumeric(CC.Range.Text) Then
                Cancel = True
                Beep
                CC.Range.Text = "0.00"
                With ActiveDocument.SelectContentControlsByTitle("PSF").Item(1)
                    .LockContents = False
                    .Range.Text = "0.00"
                    .LockContents = True
                End With
                With ActiveDocument.SelectContentControlsByTitle("PSM").Item(1)
                    .LockContents = False
                    .Range.Text = "0.00"
                    .LockContents = True
                End With
                CC.Range.Select
                Exit Sub
            Else
                CC.Range.Text = Format(Val(CC.Range.Text), "#,###,##0.00")
            End If
            Set oCC = ActiveDocument.SelectContentControlsByTitle("PSF").Item(1)
            With oCC
                .LockContents = False
                'The calculation for PSF - here 4 payments
                .Range.Text = Format(Val(ActiveDocument.SelectContentControlsByTitle("PA").Item(1).Range.Text / 4), "#,###,##0.00")
                .LockContents = True
            End With
            'Update content control contents.
            Set oCC = ActiveDocument.SelectContentControlsByTitle("PSM").Item(1)
            With oCC
                .LockContents = False
                'The calculation for PSM - here 12 payments
                .Range.Text = Format(Val(ActiveDocument.SelectContentControlsByTitle("PA").Item(1).Range.Text / 12), "#,###,##0.00")
                .LockContents = True
            End With
            
        'More Case statements as required
    End Select
    Set oCC = Nothing
lbl_Exit:
    Exit Sub
End Sub