Excel VBA, Create Border
In this article I will explain how you can make basic borders using VBA for Excel. Each range of cells can accept up to 8 different types of borders:
- Left edge (xlEdgeLeft)
- Top edge (xlEdgeTop)
- Bottom edge (xlEdgeBottom)
- Right edge (xlEdgeRight)
- Inside vertical (xlInsideVertical)
- Inside horizontal (xlInsideHorizontal)
- Diagonal down (xlDiagonalDown)
- Diagonal up (xlDiagonalUp)
I have also provided a complete sample here.
You can download the code and file for the sample here.
Contents
Left edge (xlEdgeLeft):
The code below will create a simple border for the left edge of the range “B2:D4”:
Range("B2:D4").Borders(xlEdgeLeft).LineStyle = xlContinuous
The code below is equivilant to the code above:
Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeLeft).LineStyle = xlContinuous
Result:
Top edge (xlEdgeTop):
The code below will create a simple border for the top edge of the range “B2:D4”:
Range("B2:D4").Borders(xlEdgeTop).LineStyle = xlContinuous
The code below is equivilant to the code above:
Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeTop).LineStyle = xlContinuous
Result:
Bottom edge (xlEdgeBottom):
The code below will create a simple border for the bottom edge of the range “B2:D4”:
Range("B2:D4").Borders(xlEdgeBottom).LineStyle = xlContinuous
The code below is equivilant to the code above:
Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeBottom).LineStyle = xlContinuous
Result:
Right edge (xlEdgeRight):
The code below will create a simple border for the bottom edge of the range “B2:D4”:
Range("B2:D4").Borders(xlEdgeRight).LineStyle = xlContinuous
The code below is equivilant to the code above:
Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeRight).LineStyle = xlContinuous
Result:
Inside Vertical (xlInsideVertical):
The code below will create a simple border for the inside vertical edge of the range “B2:D4”:
Range("B2:D4").Borders(xlInsideVertical).LineStyle = xlContinuous
The code below is equivilant to the code above:
Range(cells(2, 2), cells(4, 4)).Borders(xlInsideVertical).LineStyle = xlContinuous
Result:
Diagonal Down (xlDiagonalDown):
The code below will create a simple diagonal down border for the range “B2:D4”:
Range("B2:D4").Borders(xlDiagonalDown).LineStyle = xlContinuous
The code below is equivilant to the code above:
Range(cells(2, 2), cells(4, 4)).Borders(xlDiagonalDown).LineStyle = xlContinuous
Result:
Diagonal Up (xlDiagonalUp):
The code below will create a simple diagonal up border for the range “B2:D4”:
Range("B2:D4").Borders(xlDiagonalUp).LineStyle = xlContinuous
The code below is equivilant to the code above:
Range(cells(2, 2), cells(4, 4)).Borders(xlDiagonalUp).LineStyle = xlContinuous
Result:
Complete Example:
In this section I’ve provided a complete example using borders in VBA for Excel. The user selects a border style from the drop down list in cell B1. Upon selecting a new value from the drop down list the borders for the range “G7:L15” will be adjusted accordingly:
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 in this example can be seen below:
Private Sub worksheet_change(ByVal target As Range)
'removes the current border in the range
Range("G7:L15").Borders.LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalDown).LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalUp).LineStyle = xlNone
'checks if the left edge border was selected
If Cells(1, 2) = "Left Edge" Then
Range("G7:L15").Borders(xlEdgeLeft).LineStyle = _
xlContinuous
'checks if the top edge border was selected
ElseIf Cells(1, 2) = "Top Edge" Then
Range("G7:L15").Borders(xlEdgeTop).LineStyle = _
xlContinuous
'checks if the bottom edge border was selected
ElseIf Cells(1, 2) = "Bottom Edge" Then
Range("G7:L15").Borders(xlEdgeBottom).LineStyle = _
xlContinuous
'checks if the right edge border was selected
ElseIf Cells(1, 2) = "Right Edge" Then
Range("G7:L15").Borders(xlEdgeRight).LineStyle = _
xlContinuous
'checks if the inside vertical border was selcted
ElseIf Cells(1, 2) = "Inside Vertical" Then
Range("G7:L15").Borders(xlInsideVertical).LineStyle = _
xlContinuous
'checks if the inside horizontal border was selected
ElseIf Cells(1, 2) = "Inside Horizontal" Then
Range("G7:L15").Borders(xlInsideHorizontal).LineStyle = _
xlContinuous
'checks if the diagonal down border was selected
ElseIf Cells(1, 2) = "Diagonal Down" Then
Range("G7:L15").Borders(xlDiagonalDown).LineStyle = _
xlContinuous
'checks if the diagonal up border was selected
ElseIf Cells(1, 2) = "Diagonal Up" Then
Range("G7:L15").Borders(xlDiagonalUp).LineStyle = _
xlContinuous
End If
End Sub
The main function for this program is a worksheet_change event handler. The event handler executes when ever the user selected a new value from the drop down list:
Private Sub worksheet_change(ByVal target As Range)
...
End Sub
The lines below remove any previous borders from the range “G7:L15”:
Range("G7:L15").Borders.LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalDown).LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalUp).LineStyle = xlNone
The If statement below checks if the selected value from the drop list is the left edge:
If Cells(1, 2) = "Left Edge" Then
Range("G7:L15").Borders(xlEdgeLeft).LineStyle = _
xlContinuous
If the If statement returns true a simple border for the left edge of range “G7:L15” is created.
You can download the file and code for this example here.
See also:
- Excel VBA, Format Cells and Ranges Using the Macro Recorder
- Excel VBA, Border Style and Thickness
- 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
One thought on “Excel VBA, Create Border”