VBA question

[Emmily]

Hi can someone please explain why i am get the below error on

.LineStyle = xlContinuous

Run-Time error 1004
Unable to set the LineStyle property of the border class

Private Sub Borders()
Dim LastRow As Long
Dim Rng As Range
Dim ShtSummary As Excel.Worksheet
Dim myBorders() As Variant, item As Variant
                myBorders = Array(xlEdgeLeft, _
                                    xlEdgeTop, _
                                    xlEdgeBottom, _
                                    xlEdgeRight, _
                                xlInsideVertical, _
                                xlInsideHorizontal)
                                
For Each item In myBorders
Set ShtSummary = Sheets(gstrSummary)
   With ShtSummary
        With .Range("A5:D5").Borders(item)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set Rng = .Range("A6:D" & LastRow)
        With Rng.Borders(item)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = 0
        End With
   End With
Next item
 
End Sub

 

[JoeMo]

The code you posted runs without error for me and appears to do what it is intended to do, provided I change the sheet name gstrSummary to the name of the sheet I tried it on.

 

[Peter_SSs]

Edit: Oops, didn't see JoeMo's response

I copied your code and it ran without error (after I set gstrSummary to be the name of one of my worksheets)

 

[sulakvea]

hi, you can also look into this

 

[Emmily]

Edit: Oops, didn't see JoeMo's response

I copied your code and it ran without error (after I set gstrSummary to be the name of one of my worksheets)

It works in Excel 2010 but not 2002, which is very strange

 

[JoeMo]

See the link Sulakvea posted - it's a known error in Excel 2002.

 

[Peter_SSs]

Try this modification

If LastRow > 6 Or item < 12 Then
    Set Rng = .Range("A6:D" & LastRow)
    With Rng.Borders(item)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 0
    End With
End If