Tags

, , , ,

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:

  1. Left edge (xlEdgeLeft)
  2. Top edge (xlEdgeTop)
  3. Bottom edge (xlEdgeBottom)
  4. Right edge (xlEdgeRight)
  5. Inside vertical (xlInsideVertical)
  6. Inside horizontal (xlInsideHorizontal)
  7. Diagonal down (xlDiagonalDown)
  8. Diagonal up (xlDiagonalUp)

I have also provided a complete sample here.

You can download the code and file for the sample here.


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:

Excel VBA, Left Edge


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:

Excel VBA, Top Edge


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:

Excel VBA, Bottom Edge


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:

Excel VBA, Right Edge


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:

Excel VBA, Inside Vertical


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:

Excel VBA, Diagonal Down


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:

Excel VBA, Diagonal Up


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:

Excel VBA, Sample Code Create Border
Result:

Excel VBA, Sample Code Create Border 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 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:

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