Changing underlining in a style.

[PhilipJPSmith]

Hi. I have a quarterly report which is used as a standard template for many clients. Each client has its own branding style, which will include underlining.

I have a macro that amends built in styles from data held in a table.

The following code works, except for the underlining.

  With ActiveWorkbook.Styles(StyleName).Font
  .Name = Range("Font_Name").Value
  .Size = Range("Size").Value
  .Bold = Range("Bold").Value
  .Italic = Range("Italic").Value
  .Underline = Range("Underline").Value
  .Strikethrough = Range("Strikethrough").Value
  .Color = RGB(Range("Red").Value, Range("Green").Value, Range("Blue").Value)
  .TintAndShade = Range("Tint_And_Shade").Value
  End With

The named range "Underline" can include one of the following options selected from a data validated list:
xlUnderlineStyleDouble
xlUnderlineStyleDoubleAccounting
xlUnderlineStyleNone
xlUnderlineStyleSingle
xlUnderlineStyleSingleAccounting

StyleName is one of the standard Excel styles.

I would be grateful if anyone could spot the error in the syntax. I've googled but have been able to find a solution.

 

[jindon]

The named range "Underline" can include one of the following options selected from a data validated list:
xlUnderlineStyleDouble
xlUnderlineStyleDoubleAccounting
xlUnderlineStyleNone
xlUnderlineStyleSingle
xlUnderlineStyleSingleAccounting

StyleName is one of the standard Excel styles.

I would be grateful if anyone could spot the error in the syntax. I've googled but have been able to find a solution.

Those are the Constants in VBA.
While you are in vbe.
1) go to [View] - [Object Browser]
2) Enter those constants in search box one by one.
3) you will see the real numeric value in the bottom.

e.g.
Const xlUnderlineStyleDouble = -4119 (&HFFFFEFE9)
so you can use -4119 for xlUnderlineStyleDouble

 

[PhilipJPSmith]

Fantastic, many thanks