CODEWITHSUNDEEP
vbams-word
JasonB
JasonB

Reputation: 13

Executing a macro upon changing a text field

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!

Upvotes: 1

Views: 1913

Answers (1)

Sam
Sam

Reputation: 5721

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

Upvotes: 2

Related Questions