Simple Macro - Borders

[UDcc123]

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

 

[Andrew Poulsom]

You can't have a border inside a single cell. Try amending that section of 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]

Thanks!

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