Access VBA not changing Word Font.Color (1 Viewer)

Jeffr.Lipton

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2018
Messages
31
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:
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:
Code:
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:
Code:
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:
Code:
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.
 
Last edited:

Ranman256

Well-known member
Local time
Today, 08:25
Joined
Apr 9, 2015
Messages
4,337
it may not know what 'wdColorRed' is.
use: = vbRed
or
=255 (same as wdColorRed)
 

MarkK

bit cruncher
Local time
Today, 05:25
Joined
Mar 17, 2004
Messages
8,178
Ranman is guessing that "didn't work" means "color was incorrect," but it's a guess.
 

Jeffr.Lipton

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2018
Messages
31
Sorry. I meant why the code didn't change the font color.

I replaced
Code:
If IsNumeric(Replacement_Text) Then
    If Replacement_Text < 0 Then
        .Replacement.Font.Color = wdColorRed
    End If
End If
with
Code:
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

Wino Moderator
Staff member
Local time
Today, 05:25
Joined
Aug 30, 2003
Messages
36,118
Post 5 was moderated, I'm posting to trigger email notifications.
 

JHB

Have been here a while
Local time
Today, 13:25
Joined
Jun 17, 2012
Messages
7,732
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):
Code:
Source_Text = [B][COLOR=Red]"[/COLOR][/B][EstProjCost_AF][B][COLOR=red]"[/COLOR][/B]
 

Jeffr.Lipton

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2018
Messages
31
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):
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

Registered User.
Local time
Today, 23:25
Joined
Jul 4, 2013
Messages
2,770
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

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2018
Messages
31
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

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2018
Messages
31
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

You've got your good things, and you've got mine.
Local time
Today, 08:25
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 08:25
Joined
May 21, 2018
Messages
8,463
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
Code:
.Replacement.Font.Color = wdColorRed
Not only would it not work it would not even compile.
 

Cronk

Registered User.
Local time
Today, 23:25
Joined
Jul 4, 2013
Messages
2,770
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

You've got your good things, and you've got mine.
Local time
Today, 08:25
Joined
May 21, 2018
Messages
8,463
Code:
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.
 
Last edited:

Jeffr.Lipton

Registered User.
Local time
Today, 05:25
Joined
Sep 14, 2018
Messages
31
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.
 

Users who are viewing this thread

Top Bottom