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:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. 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]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
 

Test the code:

  1. Open the example spreadsheet
  2. 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.
  3. 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?.
  4. 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.
  5. 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 219 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express