CODEWITHSUNDEEP
excelvbaruntime-error
Breakingfaith
Breakingfaith

Reputation: 63

VBA: How to Automatically Include Cell Borders Without Error (Runtime 1004) Line Style

So this a question about the same big project my others are.

I am now just tidying up some formatting and essentially want a sub (that I can call from another) which will automatically add the borders I need around the new data being posted to the sheet.

I recorded the macro of adding the borders (and changed the range to one that is variable - RowToPasteTo) and all was working well.

I tried to call it from another piece of code and it went haywire, now giving me an error of:

Run-Time error '1004':

Unable to set the LineStyle Property of the Borders class

As the error mentions, it seems to be an error with the .LineStyle but I am using the same style as what their recorder gave me.

How can I get this working again so that is completes all borders?

EDIT: Forgot to add the code - sorry!

Sub Borders()

Dim RowToPasteTo As Long

With Sheets(4)

 RowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
 Range("B" & RowToPasteTo & ":" & "Z" & RowToPasteTo).Select
  With Selection.Borders
    .LineStyle = xlContinuous
    .ThemeColor = 3
    .TintAndShade = -9.99786370433668E-02
    .Weight = xlThin
    End With
End With

End Sub

EDIT 2: This piece of code is to insert the borders when each new row of data is moved to this sheet (grid marks are off, as the borders are only to be shown on the lines with data.

I have tried all amendments so far suggested and am still receiving errors when running the above code alone.

I have now also created a blank sheet (and adjusted the sheets number accordingly) and I receive a different error there.

The (minimised) code that will later call this piece if as follows:

Sub ToTank()
Dim RowToPasteTo As Long
        RowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

        Sheets(4).Unprotect
        .Range("A" & RowToPasteTo & ":" & "Z" & RowToPasteTo).Locked = False
Call Borders()

End Sub

Upvotes: 0

Views: 939

Answers (2)

Damian
Damian

Reputation: 5174

I think there is something going on from the main sub calling the border sub. To avoid these kind of problems you need to full qualify all your ranges, another way is to do this:

Option Explicit
Sub ToTank()

    Dim RowToPasteTo As Long

    With ThisWorkbook.Sheets(4) 'If the code runs for the same workbook the code is in
        RowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
        .Unprotect
        Borders .Range("B" & RowToPasteTo & ":" & "Z" & RowToPasteTo) 'call the procedure inserting the borders passing the range you need
    End With

End Sub
Sub Borders(MyRange As Range)

    With MyRange.Borders
        .LineStyle = xlContinuous
        .ThemeColor = 3
        .TintAndShade = -9.99786370433668E-02
        .Weight = xlThin
    End With

End Sub

This way you can always call the Borders sub giving the range you want without any modifications. You could also make variables the LineStyle and all the properties you need by passing more arguments.

Upvotes: 1

FAB
FAB

Reputation: 2569

As said above, .Borders require at least 1 argument to work. One way to achieve what you are trying to do is:

Sub Borders()

Dim RowToPasteTo As Long

With Sheets(4)

    RowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

    For X = 1 To 4
        With .Range("B" & RowToPasteTo & ":" & "Z" & RowToPasteTo).Borders(X)
            .LineStyle = xlContinuous
            .ColorIndex = 3
            .Weight = xlThin
        End With
    Next X
End With

End Sub

Correction as per @Damian's comment, though the above is perfectly usable as well:

Sub Borders()

Dim RowToPasteTo As Long

With Sheets("Sheet1")

    RowToPasteTo = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
    With .Range("B" & RowToPasteTo & ":" & "Z" & RowToPasteTo).Borders
        .LineStyle = xlContinuous
        .ColorIndex = 3
        .Weight = xlThin
    End With
End With

End Sub

See Borders for more details.

Upvotes: 1

Related Questions