Simple Macro - Borders

UDcc123

Board Regular
Joined
Feb 24, 2004
Messages
93
Hello.

I tried to create a simple macro that inserts bordersinto highlighted cells whenever I type control-l. I've done this many times in the past with other macros without issue (ex: control-g = gray background, etc).

For this one, I went to Tools > Macro > Record New Macro, and then when the macro started, I went up to the borders toolbar and clicked the all borders button, and then stopped the macro.

This macro works great when dealing with multiple rows, but when I try & use it with just one cell, or even multiple cells in just one row, I get an error message:

"Run-time error '1004':
Unable to set the LineStyle property of the Border class.

I was able to find the vb code in my personal.xls workbook that runs when I hit control-l. I've pasted it below. When I ask to debug, it highlights the .linestyle line in the 2nd to last With statement.

Any help that you can provide is greatly appreciated. Thanks.

Sub Gridlines()
' Keyboard Shortcut: Ctrl+l
'
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End Sub
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can't have a border inside a single cell. Try amending that section of code:

Code:
If Selection.Columns.Count > 1 Then
    With Selection.Borders(xlInsideVertical) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
End If
If Selection.Rows.Count > 1 Then
    With Selection.Borders(xlInsideHorizontal) 
        .LineStyle = xlContinuous 
        .Weight = xlThin 
        .ColorIndex = xlAutomatic 
    End With 
End If
 

UDcc123

Board Regular
Joined
Feb 24, 2004
Messages
93
Thanks!

Worked perfectly. Someday I'll learn how to code VB. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top