CODEWITHSUNDEEP
excelvba
Antares797
Antares797

Reputation: 97

Can't set borders correctly in styles in VBA

I have a bunch of code which adds 8 custom styles to Excel and removes all others. Most of the code works except for 1 bit. This concerns a style which has a colored border only on the bottom edge, the other edges have no border.

With ActiveWorkbook.Styles.Add("Table top row")
   .Font.Name = "Tahoma"
   .Font.Size = 8
   .Font.Bold = True
   .Font.Italic = False
   .Font.Color = vbBlack
   .Borders(xlEdgeBottom).Color = RGB(120, 159, 218)
   .Borders(xlEdgeBottom).Weight = xlThin
   .Borders(xlEdgeBottom).LineStyle = xlContinuous
   .HorizontalAlignment = xlLeft
End With

This piece of code results in the right font but no border at all. I then tried it another way:

With ActiveWorkbook.Styles.Add("Table top row")
    .Font.Name = "Tahoma"
    .Font.Size = 8
    .Font.Bold = True
    .Font.Italic = False
    .Font.Color = vbBlack
    .Borders.Color = RGB(120, 159, 218)
    .Borders.Weight = xlThin
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .HorizontalAlignment = xlLeft
End With

This results in a border everywhere except the left edge. I'm absolutely puzzled to why I can't get this to work. Some solutions I've found include transforming a range instead of a style, but i need the style to quickly use in different files.

Upvotes: 2

Views: 1891

Answers (1)

asongtoruin
asongtoruin

Reputation: 10359

Try using xlRight etc rather than xlEdgeRight etc. This seems to work, though I don't fully understand why.

With ActiveWorkbook.Styles.Add("Table top row")
    .Font.Name = "Tahoma"
    .Font.Size = 8
    .Font.Bold = True
    .Font.Italic = False
    .Font.Color = vbBlack
    .Borders.Color = RGB(120, 159, 218)
    .Borders.Weight = xlThin
    .Borders(xlBottom).LineStyle = xlContinuous
    .Borders(xlTop).LineStyle = xlNone
    .Borders(xlLeft).LineStyle = xlNone
    .Borders(xlRight).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .HorizontalAlignment = xlLeft
End With

Upvotes: 2

Related Questions