Excel VBA, Border Style and Thickness

In this article I will explain how you can modify border thickness and style using VBA for Excel. For more information about creating borders please see Excel VBA, Create Border. In that article I’ve explained how you can create a border for the different sections of a range of cells (i.e top edge, left edge, …)  Therefore I’m assuming readers are familiar with the basics of creating a border.

Jump To:

You can download the code and file related to this article here.


Border Line Styles, .LineStyle:

Basically we’ve got 6 different border line styles:

  • Continuous (xlContinuous)
  • Dot, (xlDot)
  • DashDotDot, (xlDashDotDot)
  • Dash, (xlDash)
  • SlantDashDot, (xlSlantDashDot)
  • Double, (xlDouble)

You can see them in the figure below:

Excel VBA Line Styles
The borders were created using the code below:

Sub main()
Dim i As Integer
i = 1
Range(Cells(i, 3), Cells(i, 5)).Borders(xlEdgeBottom).LineStyle _
= xlContinuous
i = 3
Range(Cells(i, 3), Cells(i, 5)).Borders(xlEdgeBottom).LineStyle _
= xlDot
i = 5
Range(Cells(i, 3), Cells(i, 5)).Borders(xlEdgeBottom).LineStyle _
= xlDashDot
i = 7
Range(Cells(i, 3), Cells(i, 5)).Borders(xlEdgeBottom).LineStyle _
= xlDash
i = 9
Range(Cells(i, 3), Cells(i, 5)).Borders(xlEdgeBottom).LineStyle _
= xlSlantDashDot
i = 11
Range(Cells(i, 3), Cells(i, 5)).Borders(xlEdgeBottom).LineStyle _
= xlDouble
End Sub

The code above is equivalent to the code below:

Sub main()
Range("C1:E1").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C3:E3").Borders(xlEdgeBottom).LineStyle = xlDot
Range("C5:E5").Borders(xlEdgeBottom).LineStyle = xlDashDot
Range("C7:E7").Borders(xlEdgeBottom).LineStyle = xlDash
Range("C9:E9").Borders(xlEdgeBottom).LineStyle = xlSlantDashDot
Range("C11:E11").Borders(xlEdgeBottom).LineStyle = xlDouble
End Sub


Border Line Thickness, .Weight:

There are 3 different border line thicknesses available:

  • Thin
  • Medium
  • Thick

The different thicknesses can be seen in the figure below:

Excel VBA, Line  Thickness
The code below was used to generate these lines:

Sub main()
Range("C1:E1").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C1:E1").Borders(xlEdgeBottom).Weight = xlThin

Range("C3:E3").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C3:E3").Borders(xlEdgeBottom).Weight = xlMedium

Range("C5:E5").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C5:E5").Borders(xlEdgeBottom).Weight = xlThick

End Sub


Example:

In this example the user selects the line style and line weight using drop down lists in cells A2 and B2. Upon selecting a new value from the drop down lists, the border on the bottom edge of the cells C4:F4 is updated:

Excel VBA, Example, Border, Line style

Excel VBA, Example, Border, Line Thickness
Result:

Excel VBA, Example
The drop down list is created using data validation. For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists.

The code used for this example can be seen below:

'exectues when the user makes changes to the sheet
Private Sub worksheet_change(ByVal target As Range)
'set line style
If Cells(2, 1) = "Continuous" Then
    Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlContinuous
ElseIf Cells(2, 1) = "Dot" Then
    Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlDot
ElseIf Cells(2, 1) = "Dash" Then
    Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlDash
ElseIf Cells(2, 1) = "DashDotDot" Then
    Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlDashDotDot
ElseIf Cells(2, 1) = "Dash" Then
    Range("c4:F4").Borders(xlEdgeBottom).LineStyle = xlSlantDashDot
ElseIf Cells(2, 1) = "SlantDashDot" Then
    Range("c4:F4").Borders(xlEdgeBottom).LineStyle = xlDouble
End If

'set the border thickness
If Cells(2, 2) = "Thin" Then
    Range("C4:F4").Borders(xlEdgeBottom).Weight = xlThin
ElseIf Cells(2, 2) = "Medium" Then
    Range("C4:F4").Borders(xlEdgeBottom).Weight = xlMedium
ElseIf Cells(2, 2) = "Thick" Then
    Range("C4:F4").Borders(xlEdgeBottom).Weight = xlThick
End If

End Sub

The main function for this example is a worksheet_change event handler. The event handler executes when the user selects a new value from the drop down lists:

Private Sub worksheet_change(ByVal target As Range)
...
End Sub

The first part of the code checks which value was chosen from the drop down list in cell A2 and adjusts the border style of the bottom edge of the range C4:F4 accordingly:

If Cells(2, 1) = "Continuous" Then
    Range("C4:F4").Borders(xlEdgeBottom).LineStyle = xlContinuous
ElseIf Cells(2, 1) = "Dot" Then
...
End If

The second part of the code checks which value was chosen from the drop down list in cell B2 and adjusts the border thickness of the bottom edge of the range C4:F4 accordingly:

If Cells(2, 2) = "Thin" Then
    Range("C4:F4").Borders(xlEdgeBottom).Weight = xlThin
ElseIf Cells(2, 2) = "Medium" Then
...
End If

 

You can download the code and file related to this article here.

 

 

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *