Executing a macro upon changing a text field

Question

I'm in the process of designing a document that must be protected in order to allow users to only edit certain fields. I need to automatically execute a macro that calculates a score description when a raw score is changed. I am currently using a "Rich Text Content Control" to allow the user to enter a numeric score.

I'm using the following macro code:

Sub UpdateDescriptor()
Dim TheFinalScore As String
TheFinalScore = ActiveDocument.Bookmarks("FinalScore3").Range.Text

Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks("FinalScore2").Range

If IsNumeric(TheFinalScore) Then
    Select Case TheFinalScore
        Case 1 To 1.5: BMRange.Text = "Outstanding"
        Case 1.51 To 2: BMRange.Text = "Excellent"
        Case 2.01 To 2.5: BMRange.Text = "Average"
        Case 2.51 To 3: BMRange.Text = "Below Average"
        Case Is > 3: BMRange.Text = "Unsatisfactory"
        Case Else: BMRange.Text = "Invalid Final Score"
    End Select
Else
    BMRange.Text = "Invalid Final Score"
End If
    ActiveDocument.Bookmarks.Add "FinalScore2", BMRange
End Sub

Manually running the macro works fine, but I've not been able to find any way to execute the macro automatically on change.

I've also tried using a legacy "Text Form Field". I can get the macro to fire upon entry, but not exit. Any ideas on how I can get the "Rich Text Content Control" to fire a macro upon exit? Or to get the "Text Form Field" to fire upon exit? I'm also noticing that that number format specified in the properties of the "Text Form Field" is not being enforced.

Thanks!

FWIW, using a formfield, with it's 'calculate on exit property set, the whole thing could be done without the need for a macro. It could all be done with field coding. That has the advantage, too, that your process isn't held hostage to the users enabling/disabling macros. — macropod

Sam Sam · Accepted Answer · 2018-01-30T19:19:39

Add this code to the ThisDocument module:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim TheFinalScore As String

    TheFinalScore = ContentControl.Range.Text
    ...
    ...

I think the rest will be quite obvious. If you need to tell more controls apart, use ContentControl.ID

Executing a macro upon changing a text field

1 Answers