Access VBA not changing Word Font.Color

[Jeffr.Lipton]

I'm using Access to change value in a Word document and I want to change the font color to red for numbers less than zero. Here's my calling code:

Set oWord = CreateObject("Word.Application")
oWord.Visible = True
oWord.Activate
Set doc = oWord.Documents.Open(fpath & "AF Final Costs Notification template.docx", True)
Set oSelection = oWord.Documents(1).Content
oSelection.Select
Set sel = oWord.Selection

Source_Text = "[EstProjCost_AF]"
Replacement_Text = Format(Me.EstProjCost_IF, "currency")
Call Replace_Text(sel, Source_Text, Replacement_Text)

Here's my subroutine:

Private Sub Replace_Text(sel, Source_Text, Replacement_Text)

Replacement_Text = Nz(Replacement_Text, "--NULL--")
With sel
.Find.ClearFormatting
.Find.Replacement.ClearFormatting
With .Find
.Text = Source_Text
.Replacement.Font.Color = wdColorBlack
If IsNumeric(Replacement_Text) Then
If Replacement_Text < 0 Then
.Replacement.Font.Color = wdColorRed
End If
End If
.Replacement.Text = Replacement_Text
.Forward = True
.Wrap = 1 'wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
sel.Find.Execute Replace:=2 'wdReplaceAll
End With
End With

End Sub

Here's the Word macro which works as expected:

Sub change_font() Dim Replacement_Text As String
'
' change_font Macro
'
'
Replacement_Text = "def"
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
If IsNumeric(Replacement_Text) Then
If CInt(Replacement_Text) < 0 Then
Selection.Find.Replacement.Font.Color = wdColorRed
End If
End If
With Selection.Find
.Text = "abc"
.Replacement.Text = Replacement_Text
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

I was able to find a work-around. Since all the text I want to change has a pattern of "(-9*)", I add this code after the last replace:

With sel
.Find.ClearFormatting
.Find.Replacement.ClearFormatting
.Find.Replacement.Font.Color = wdColorRed
With .Find
.Text = "\(^#*\)"
.Replacement.Text = "^&"
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
.Find.Execute Replace:=wdReplaceAll
End With

I'd still like to know why the original code didn't change the font color.

 

[Ranman256]

it may not know what 'wdColorRed' is.
use: = vbRed
or
=255 (same as wdColorRed)

 

[MarkK]

I'd still like to know why the original code didn't work.

"Didn't work" is not enough information.
hth
Mark

 

[MarkK]

Ranman is guessing that "didn't work" means "color was incorrect," but it's a guess.

 

[Jeffr.Lipton]

Sorry. I meant why the code didn't change the font color.

I replaced

If IsNumeric(Replacement_Text) Then
    If Replacement_Text < 0 Then
        .Replacement.Font.Color = wdColorRed
    End If
End If

with

If IsNumeric(Replacement_Text) Then
    If Replacement_Text < 0 Then
        .Replacement.Font.Color = vbRed
    End If
End If

with the same result. (I also tried 255.) I monitored the program and it's definitely getting to the .Replacement.Font.Color line, so the value used for the color doesn't seem to the problem...

 

[pbaldy]

Post 5 was moderated, I'm posting to trigger email notifications.

 

[JHB]

Should the Source_Text be a hardcoded text or should it be the value from [EstProjCost_AF]?
If value, then the below line is wrong, (it should be without quotes and adding Me. make it more clear reading the code):

Source_Text = [B][COLOR=Red]"[/COLOR][/B][EstProjCost_AF][B][COLOR=red]"[/COLOR][/B]

 

[Jeffr.Lipton]

Should the Source_Text be a hardcoded text or should it be the value from [EstProjCost_AF]?
If value, then the below line is wrong, (it should be without quotes and adding Me. make it more clear reading the code):

Source_Text = [B][COLOR=Red]"[/COLOR][/B][EstProjCost_AF][B][COLOR=red]"[/COLOR][/B]

I have "hard-coded" text in my word document, such as Estimated and Actual Project Costs and the difference between them. My document might have something like "Your Actual Project Cost of [ActProjCost] is [more/less] than your Estimated Project Cost of [EstProjCost] by [Act-Minus-Est]." All of the bracketed text will be replaced by fileds on my forms, or by values calculated from them.

I'm going to try commenting some of the ClearFormat statements and see if that helps.

 

[Cronk]

Have you stepped through your code?


Unlike Sub Replace_Text, Sub change_font has no parameters passed to it. Is that an issue?

 

[Jeffr.Lipton]

Have you stepped through your code?

Several times!

Unlike Sub Replace_Text, Sub change_font has no parameters passed to it. Is that an issue?

The Word macro shown gets Source_Text and Replacement_Text as hard-coded values in the macro. (I've changed them both to be entered from InputBox calls.) So it doesn't need the values to be passed.

 

[Jeffr.Lipton]

I tried commenting the ClearFormatting lines -- no change. My work-around isn't perfect but will do for now. Thanks to @Ranman256, @MarkK, @pbaldy, @JHB and @Cronk for taking the time to reply.

 

[MajP]

Not sure if it is your problem, but I do not know why you would work with a Selection object. Seems like such a round about way. You should be working with a Range object instead.

In most cases, Range objects are preferred over the Selection object for the following reasons:
0) You can define and use multiple Range objects, whereas you can only have one Selection object per document window.
1) Manipulating Range objects doesn't change the selected text.
2) The user will not see anything when a Range object is being manipulated.
3) Manipulating Range objects is faster than working with the Selection.
4) You can always use the Range.Select method to make a range selected.
5) Some properties and methods are not available to the Selection object.
6) Using the Range Method to Return a Range Object

 

[MajP]

One other thing. This looks like you are attempting late binding, but you are not since you obviously have a reference to Word. If that was not the case then

.Replacement.Font.Color = wdColorRed

Not only would it not work it would not even compile.

 

[Cronk]

Not only would it not work it would not even compile

If variable declaration was not compulsory, it would compile.


This could be an explanation in that the default maybe taken as 0 which is the same as vbBlack

 

[MajP]

If variable declaration was not compulsory, it would compile.

Yeah, I forgot that is actually a thing. One of the most stupid things in VBA allowing you not to declare your variables. About as dumb as having bang and dot notation. However wihtout testing I would be suprised if it was 0 and not null. If you do not declare a variable it is a variant and the default would be Null. But you are right in that it may cast the null to 0.

 

[Jeffr.Lipton]

I tried it with vbRed and 255 with the same result. And all variables must be declared or a get a compile error.

I've moved on to my next project -- using ACCESS to read a PDF and populate a Word document -- but I'll check back in case someone has an insight.