Word 2003 table cell values have trailing squares when tested in vba

[Loci]

I have created an invoice template with some code in the background as shown below:

I am using a table formula to add up all the cell values and therefore require 0 to be entered where a value is not, I would then like to hide all the zero values by changing them to a white font. At this point I say if the selection.text = "0.00" then... however the value received by the code is 0.00££ How can I check for the zero values nad include or remove the trailing sqaures and what are they??


Sub ReCalcCodes()
' recalc all field codes in table and select total field
' Set all zero values in table cells to white font

Dim r As Integer
Dim c As Integer
Dim tbrows As Integer

' Select table and update field values.
ActiveDocument.Tables(1).Select
Selection.Fields.Update

r = 3
c = 3
tbrows = 1

While tbrows < 23
ActiveDocument.Tables(1).Cell(Row:=r, Column:=c).Select
If Selection.Text = "0.00" Then
With Selection.Font
.Color = wdColorWhite
End With
Else
With Selection.Font
.Color = wdColorAutomatic
End With
End If
r = r + 1
tbrows = tbrows + 1

Wend

ActiveDocument.Bookmarks("total").Select

End Sub

Many thanks for any help you can give.

loci

 

[krazymike]

Ok, I replicated your situation, and this is what I got. The first
"white square" is the ASCII character 07, audible bell, and the second
one is ASCII character 13, Carriage Return.

See it for yourself:

Debug.Print Asc(Right$(Selection.Text, 1)) & " - " & Asc(Mid$
(Selection.Text, Len(Selection.Text) - 1, 1))

Why 07 is in there, I don't know, but here's my handle:


sVal = Replace$(Replace$(Selection.Text, Chr(7), ""), Chr(13), "")
If sVal = "0.00" Then
' ....rest of your code

 

[Loci]

Krazymike you are a genius, that worked a treat, many thanks.

Ok, I replicated your situation, and this is what I got. The first
"white square" is the ASCII character 07, audible bell, and the second
one is ASCII character 13, Carriage Return.

See it for yourself:

Debug.Print Asc(Right$(Selection.Text, 1)) & " - " & Asc(Mid$
(Selection.Text, Len(Selection.Text) - 1, 1))

Why 07 is in there, I don't know, but here's my handle:


sVal = Replace$(Replace$(Selection.Text, Chr(7), ""), Chr(13), "")
If sVal = "0.00" Then
' ....rest of your code

 

[macropod]

Hi Loci,

Your code is picking up the end-of-cell markers. Try:

Sub ReCalcCodes()
' recalc all field codes in table and select total field
' Set all zero values in table cells to white font
Dim r As Integer
Dim c As Integer
Dim tbrows As Integer
Dim RngCel As Range
' Select table and update field values.
With ActiveDocument.Tables(1)
.Fields.Update
r = 3
c = 3
tbrows = 1
While tbrows < 23
Set RngCel = .Cell(Row:=r, Column:=c).Range
RngCel.End = RngCel.End - 1
RngCel.Font.Color = wdColorAutomatic
If RngCel.Text = "0.00" Then RngCel.Font.Color = wdColorWhite
r = r + 1
tbrows = tbrows + 1
Wend
.Bookmarks("total").Select
End With
End Sub

--
Cheers
macropod
[MVP - Microsoft Word]


I have created an invoice template with some code in the background as shown below:

I am using a table formula to add up all the cell values and therefore require 0 to be entered where a value is not, I would then like to hide all the zero values by changing them to a white font. At this point I say if the selection.text = "0.00" then... however the value received by the code is 0.00££ How can I check for the zero values nad include or remove the trailing sqaures and what are they??


Sub ReCalcCodes()
' recalc all field codes in table and select total field
' Set all zero values in table cells to white font

Dim r As Integer
Dim c As Integer
Dim tbrows As Integer

' Select table and update field values.
ActiveDocument.Tables(1).Select
Selection.Fields.Update

r = 3
c = 3
tbrows = 1

While tbrows < 23
ActiveDocument.Tables(1).Cell(Row:=r, Column:=c).Select
If Selection.Text = "0.00" Then
With Selection.Font
.Color = wdColorWhite
End With
Else
With Selection.Font
.Color = wdColorAutomatic
End With
End If
r = r + 1
tbrows = tbrows + 1

Wend

ActiveDocument.Bookmarks("total").Select

End Sub

Many thanks for any help you can give.

loci