How to know which cell caracters are underlined

[Alex St-Pierre]

Hi!
I use VBA automation to apply table excel formatting into word. In this
program, all work except the Font.Underline.
bMixed = Cells(5,1).Characters(Start:=i, Length:=1).Font.Underline return
always true for any i if there's one underlined character in the cell.. Is
there a way to know which characters are underlined?
All other properties (bold, alignment, italic, superscript, etc. is correct)
Any idea?
Thank you!
Alex


With Range(Cells(iLine, jBegin), Cells(iLine, jBegin)) 'Word table range
.HorizontalAlignment =
ThisWorkbook.Sheets(SheetName).Range(sLoadData).HorizontalAlignment
.VerticalAlignment =
ThisWorkbook.Sheets(SheetName).Range(sLoadData).VerticalAlignment

For i = 1 To Len(.Value)
With .Characters(Start:=i, Length:=1).Font
.Bold =
ThisWorkbook.Sheets(SheetName).Range(sLoadData).Characters(Start:=i,
Length:=1).Font.Bold
.Superscript =
ThisWorkbook.Sheets(SheetName).Range(sLoadData).Characters(Start:=i,
Length:=1).Font.Superscript
.Subscript =
ThisWorkbook.Sheets(SheetName).Range(sLoadData).Characters(Start:=i,
Length:=1).Font.Subscript
.Italic =
ThisWorkbook.Sheets(SheetName).Range(sLoadData).Characters(Start:=i,
Length:=1).Font.Italic
.Underline =
ThisWorkbook.Sheets(SheetName).Range(sLoadData).Characters(Start:=i,
Length:=1).Font.Underline 'THIS DOESN'T WORK
End With
Next i

 

[Rick Rothstein \(MVP - VB\)]

It appears that there is more than one type of underline (single, double,
none), so the Underline property is not a Boolean value. Try this for a
general "is it underlined (either single or double)"...

bMixed = Cells(5,1).Characters(Start:=i, Length:=1).Font.Underline <>
xlUnderlineStyleNone

Note: The above is supposed to be a single line statement (in case your
newsreader wraps it).

If you want to check for a specific kind of underline, the predefined
constants to check against appear to be xlUnderlineStyleSingle and
xlUnderlineStyleDouble.

Rick

 

[Rick Rothstein \(MVP - VB\)]

If you want to check for a specific kind of underline, the predefined

constants to check against appear to be xlUnderlineStyleSingle and
xlUnderlineStyleDouble.

Actually, in addition to the above, there are two other predefined underline
constants available...xlUnderlineStyleSingleAccounting and
xlUnderlineStyleDoubleAccounting.

Rick

 

[Peter T]

As Rick says the various Underline styles can be any of the following (from
help)

"Can be one of the following XlUnderlineStyle constants:
xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble,
xlUnderlineStyleSingleAccounting, or xlUnderlineStyleDoubleAccounting.
Read/write Long."

When looking for potentially mixed formats start by reading to a Variant,
which can accept Null if mixed, eg (untested)

s = ""
v = cell.font.underline
if isnull(v) then
for i = 1 to len(cell)
s = s & iif(cell.characters(i,1).font.underline = xlNone,"o","x")
next
else
for i = 1 to len(cell)
if v = xlNone then s = s & "o" else s = s & "x"
next
end if

If you are using Late Binding change any xl constants to their intrinsic
values, eg for xlNone use -4142

Regards,
Peter T