VBA Express : Excel - Create various borders for a range

Excel

Create various borders for a range

Ease of Use

Intermediate

Version tested with

2000, 2003

Submitted by:

MWE

Description:

xlBorders programmatically creates borders for a target range

Discussion:

Excel has a rich and easy-to-use border creation capability. VBA provides a simple link to Excel?s interactive border-creation window, but little to programmatically create borders. xlBorders creates borders for/within a range. xlBorders is called by a parent procedure or application; there is no user interaction. There are five passed arguments: the target range, the outside line weight, the inside line weight, [optional] the outside line color and [optional] the inside line color. Line weights can be ?hairline?, ?thin?, ?medium?, ?thick?, or ?none?. The outside line weight controls the four outside edges of the range. The inside line weight controls the inside horizontal and vertical edges. Line colors are the numeric color desired; default is ?xlAutomatic? (or ?4105). No special line ?styles? or diagonal properties are supported. The demonstration spreadsheet contains xlBorders, a demo ?parent? routine, and utility routine to toggle active window gridlines. A single worksheet contains some scattered character data, several yellow command buttons (each command button demonstrates a particular combination of outside/inside line weights and colors), and additional command buttons to clear all borders and toggle active window gridlines on and off (to better visualize borders).

Code:

instructions for use

			Sub Test_xlBorders(CaseNum) 
     '
     '       Demonstrates the use of xlBorders procedure
     '
    Dim OutLineWeight   As String 
    Dim InLineWeight    As String 
    Dim xlRange         As Range 
     
    Select Case CaseNum 
    Case 0 'Out = none; In = none; Color = xlAutomatic
        OutLineWeight = "none" 
        InLineWeight = "none" 
        Set xlRange = Range(Cells(1, 1), Cells(28, 10)) 
        Call xlBorders(xlRange, OutLineWeight, InLineWeight) 
    Case 1 'Out = thick; In = thin; color = xlAutomatic
        OutLineWeight = "thick" 
        InLineWeight = "thin" 
        Set xlRange = Range(Cells(2, 6), Cells(5, 10)) 
        Call xlBorders(xlRange, OutLineWeight, InLineWeight) 
    Case 2 'Out = none; In = medium; color = red
        OutLineWeight = "none" 
        InLineWeight = "medium" 
        Set xlRange = Range(Cells(7, 1), Cells(10, 5)) 
        Call xlBorders(xlRange, OutLineWeight, InLineWeight, 3, 3) 
    Case 3 'Out = medium; In = hairline; color = blue
        OutLineWeight = "medium" 
        InLineWeight = "hairline" 
        Set xlRange = Range(Cells(12, 3), Cells(18, 5)) 
        Call xlBorders(xlRange, OutLineWeight, InLineWeight, 5, 5) 
    Case 4 'Out = thick; In = none
        OutLineWeight = "thick" 
        InLineWeight = "none" 
        Set xlRange = Range(Cells(20, 1), Cells(21, 10)) 
        Call xlBorders(xlRange, OutLineWeight, InLineWeight) 
    Case 5 'Out = thick/red; In = medium/blue
        OutLineWeight = "thick" 
        InLineWeight = "medium" 
        Set xlRange = Range(Cells(23, 5), Cells(28, 10)) 
        Call xlBorders(xlRange, OutLineWeight, InLineWeight, 3, 5) 
    Case Else 
    End Select 
     
End Sub 
 
Sub xlBorders(xlRange As Range, OutLineWeight As String, InLineWeight As String, _ 
    Optional OutLineColor As Long = -4105, _ 
    Optional InLineColor As Long = -4105) 
     '
     '****************************************************************************************
     '       Function    generates Inside and Outside borders for a target range
     '       Passed Values
     '           xlRange [in, range] target range
     '           OutLineWeight [in, string] weight of Outerior lines:
     '               hairline, thin, medium, thick, none
     '           InLineWeight  [in, string] weight of Interior lines:
     '               hairline, thin, medium, thick, none
     '           OutLineColor  [in, long, OPTIONAL] outside line color {default = xlAutomatic}
     '           InLineColor     [in, long, OPTIONAL] inside line color {default = xlAutomatic}
     '
     '****************************************************************************************
     '
     '
    Dim OutLineStyle    As Variant 
    Dim OutLineWt       As Variant 
    Dim InLineStyle     As Variant 
    Dim InLineWt        As Variant 
     
    OutLineStyle = xlContinuous 
    Select Case LCase(OutLineWeight) 
    Case "hairline" 
        OutLineWt = xlHairline 
    Case "thin" 
        OutLineWt = xlThin 
    Case "medium" 
        OutLineWt = xlMedium 
    Case "thick" 
        OutLineWt = xlThick 
    Case "none" 
        OutLineStyle = xlNone 
    Case Else 
        MsgBox "ERROR:  bad value for OutLineWeight", _ 
        vbCritical, "xlBorders" 
        Exit Sub 
    End Select 
    InLineStyle = xlContinuous 
    Select Case LCase(InLineWeight) 
    Case "hairline" 
        InLineWt = xlHairline 
    Case "thin" 
        InLineWt = xlThin 
    Case "medium" 
        InLineWt = xlMedium 
    Case "thick" 
        InLineWt = xlThick 
    Case "none" 
        InLineStyle = xlNone 
    Case Else 
        MsgBox "ERROR:  bad value for InLineWeight", _ 
        vbCritical, "xlBorders" 
        Exit Sub 
    End Select 
     
    On Error Resume Next 
    xlRange.Borders(xlDiagonalDown).LineStyle = xlNone 
    xlRange.Borders(xlDiagonalUp).LineStyle = xlNone 
    With xlRange.Borders(xlEdgeLeft) 
        .LineStyle = OutLineStyle 
        .Weight = OutLineWt 
        .ColorIndex = OutLineColor 
    End With 
    With xlRange.Borders(xlEdgeTop) 
        .LineStyle = OutLineStyle 
        .Weight = OutLineWt 
        .ColorIndex = OutLineColor 
    End With 
    With xlRange.Borders(xlEdgeBottom) 
        .LineStyle = OutLineStyle 
        .Weight = OutLineWt 
        .ColorIndex = OutLineColor 
    End With 
    With xlRange.Borders(xlEdgeRight) 
        .LineStyle = OutLineStyle 
        .Weight = OutLineWt 
        .ColorIndex = OutLineColor 
    End With 
    With xlRange.Borders(xlInsideVertical) 
        .LineStyle = InLineStyle 
        .Weight = InLineWt 
        .ColorIndex = InLineColor 
    End With 
    With xlRange.Borders(xlInsideHorizontal) 
        .LineStyle = InLineStyle 
        .Weight = InLineWt 
        .ColorIndex = InLineColor 
    End With 
     
End Sub 
 
Sub GridToggle() 
     '
     '           utility to toggle gridlines on and off
     '
    If ActiveWindow.DisplayGridlines = True Then 
        ActiveWindow.DisplayGridlines = False 
    Else 
        ActiveWindow.DisplayGridlines = True 
    End If 
     
End Sub

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
See ?Test The Code? below

Test the code:

Open the example spreadsheet
Clicking on one of the five yellow command button starts the test procedure which then calls xlBorders with appropriate arguments; each example targets a different range.
Clicking on the green command button starts the test procedure which then does a ?special? call to xlBorders with the range set to the overall range for all examples and both line weights = ?none?.
Clicking on the blue command button calls a utility that toggles the active window gridlines on or off. With the gridlines off, the results of xlBorders is easier to see.
NOTE: xlBorders is a subroutine that is called by some parent procedure or application. Thus final testing will depend on how xlBorders is to be used.

Sample File:

xlBorders.zip 29.34KB

Approved by mdmackillop

This entry has been viewed 154 times.