Word Form Field Negative Numbers

[inkserious]

I have a series of Form Fields in a Word document that are formatted as numbers with the format $#,##0;($#,##0). Is it possible to make these numbers red if they are a negative value?

I tried using $#,##0_);[Red]($#,##0) which is the same format used in Excel; however, it did not work.

I tried using a switch instead of Word's own Form Field options, but it did not work either.

{FORMTEXT \#"$#,##0;($#,##0)"}

I had a feeling it wasn't going to be that simple.

Anyone have any ideas on this one?

-edward

 

[SkipVought]

Hi,

Word is a [red]TEXT[/red] processor, not a spreadsheet application.

Each tool has its purpose; forte and limitations. Why not embed Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[inkserious]

Sorry for the lag time in making a response. The Word document is more of a narrative of the daily activity that gets sent to the CEO and COO of the company, and there are only about a dozen or so Form Fields that contain numbers. So far standard formatting has been sufficient. I ran across a google search where someone was able to change a check box red when it was checked using a macro, so I was hoping perhaps that same type of functionality could be used in Word without having to embed Excel.

I can't seem to find the google article, or I would go back to it and try to tailor it to negative numbers in a Form Field. The document already has several macros in it, so I don't mind adding another one in order to solve the problem.

Thanks.

Edward

 

[fumei]

Yes, it can be done. However, unless you do a bit more processing the code belows does NOT check for a negative value per se. It checks to see if the formfield result starts with "(". If it does, it makes the formfield text red, if it does not, text is "automatic". In other words, if you are using #"$#,##0;($#,##0)"

23.00 will show as $23.00
($23.00) will show as (23.00)[/color red]

' adapted from the Word MVP site code
Option Explicit

[COLOR=red]' a public variable to hold current formfield[/color red]
Public fldFF As Word.FormField

Private Function CurrentFF() As Word.FormField
[COLOR=red]' function to set current formfield into public object variable[/color red]
With Selection
        Set CurrentFF = .FormFields(1)
    ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
        Set CurrentFF = ActiveDocument.FormFields _
        (.Bookmarks(.Bookmarks.Count).Name)
    End If
End With
End Function


Sub myOnExit()
[COLOR=red]' sub set as the OnExit macro for formfield
' if starting character is "(" make .Result red
' if not, leave as automatic[/color red]
Set fldFF = CurrentFF
With fldFF
   If .Type = wdFieldFormTextInput Then
      If Left(.Result, 1) = "(" Then
         .Range.Font.Color = wdColorDarkRed
      Else
         .Range.Font.Color = wdColorAutomatic
      End If
   End If
End With
End Sub

BTW: if you are doing something like this, I would recommend removing the shading of the formfield. It looks kind of weird with red text shaded.

Gerry

 

[inkserious]

Thanks Gerry. You helped me out with some code on this project earlier as well. I though I'd give you an update as to what I've come up with so far with your help. The last two issues I've run into are as follows:

Several of the Form Fields are calculated fields. The code to change the negative value to red will not work on these fields.

Also, on several of the fields the user enters a number and the field is formatted as 0.0%;(0.0%. The problem is that if the user keys in 12.5, they are returned with 1250.0%. Is there a way to make the result return as 12.5% as opposed to having the user type in .125

Thanks again for all of your help.

Edward

Option Explicit

Public fldFF As Word.FormField

Sub FillBM(strBM As String, strText As String)
[COLOR=red]' procedure that dynamically changes the bookmark content
' value is IN the bookmark, not outside it[/color]
Dim r As Range
Set r = ActiveDocument.Bookmarks(strBM).Range
If ActiveDocument.FormFields("date").Result = "" Then Exit Sub
r.Text = strText
ActiveDocument.Bookmarks.Add Name:=strBM, Range:=r
End Sub

Private Function CurrentFF() As Word.FormField
[COLOR=red]' gets the current formfield as an object[/color]
With Selection
    If .FormFields.Count = 1 Then
        ' CheckBox or DropDown
        Set CurrentFF = .FormFields(1)
            ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
        Set CurrentFF = ActiveDocument.FormFields _
        (.Bookmarks(.Bookmarks.Count).Name)
    End If
End With
End Function

Sub myOnExit()
Set fldFF = CurrentFF
With fldFF
   If .Type = wdFieldFormTextInput Then
      [COLOR=red]' files the BOOKMARK in the header with
      ' the formfield value[/color]
      Call FillBM("h_date", CurrentFF.Result)
   End If
   [COLOR=red]' makes the value blank[/color]
   .Result = ""
End With

If ActiveDocument.FormFields.Shaded = True Then ActiveDocument.FormFields.Shaded = False
If ActiveWindow.View.TableGridlines = True Then ActiveWindow.View.TableGridlines = False

If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
    ActiveDocument.Unprotect ' Password:="aPassword"
Else

End If
    [COLOR=red]'delete the "Enter Date Here" text in cell 1[/color]
    ActiveDocument.Tables(1).Cell(Row:=1, Column:=1).Range.Select
    Selection.Delete Unit:=wdCharacter, Count:=1
    [COLOR=red]'Reprotect Document without resetting form fields[/color]
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
        NoReset:=True ', Password:="aPassword"
End Sub

Sub redNumber()
[COLOR=red]' sub set as the OnExit macro for formfield
' if starting character is "(" make .Result red
' if not, leave as automatic[/color]
Set fldFF = CurrentFF
With fldFF
   If .Type = wdFieldFormTextInput Then
      If Left(.Result, 1) = "(" Then
         .Range.Font.Color = wdColorRed
      Else
         .Range.Font.Color = wdColorAutomatic
      End If
   End If
End With
End Sub

 

[fumei]

Several of the Form Fields are calculated fields. The code to change the negative value to red will not work on these fields."

Then I would suggest a procedure that does the coloring globally (checking ALL formfields). This of course would have to be executed on its own, rather than a response to the current formfield.

Sub NegativeRed()
Dim docFF As FormFields
Dim oFF As FormField

Set docFF = ActiveDocument.FormFields
For Each oFF In docFF
     If Left(oFF.Result, 1) = "(" Then
         oFF.Range.Font.Color = wdColorRed
     Else
         oFF.Range.Font.Color = wdColorAutomatic
     End If
Next
End Sub

This checks all formfields, and if the results starts with "(", that formfield becomes red.

Gerry