Set Bottom Border Continuous and Thick Excel Macro

In this article I will explain how you can modify border thicknessand 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:

• Border Line Styles, .LineStyle
• Border Line Thickness, .Weight
• Example

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:

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 differentthicknessescan be seen in the figure below:

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:

Result:

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 thicknessof 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:

• Excel VBA, Create Border
• Excel VBA, Format Cells and Ranges Using the Macro Recorder
• Excel VBA Border Colors
• Excel VBA, Remove Border
• Excel VBA, Get Border Properties
• Excel VBA Borders

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

gibsoncant1970.blogspot.com

Source: https://software-solutions-online.com/excel-vba-border-style-and-thickness-afdad/

Share this post