How to know which cell caracters are underlined

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
If you want to check for a specific kind of underline, the predefined
constants to check against appear to be xlUnderlineStyleSingle and
xlUnderlineStyleDouble.
Click to expand...

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

Rick
 
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
 
Back
Top