CODEWITHSUNDEEP
vbaexcel
Ian Gough
Ian Gough

Reputation: 43

Excel Automation error when inserting a row

I have a big problem and its driving me insane. I have a very simple piece of code that is supposed to copy a row and add it in below the active row plus a validation at the start of the code to check that you are allowed to add the row on that particular line.

The macro works perfectly when you first go in to the sheet. However, as soon as i enter anything in on any of the cells on the sheet the code bombs out with an automation error. Please say someone has found this before and has a fix for it?

The line it doesn't like is as shown here. Selection.Insert Shift:=xlDown

Sub Staffing_AddRow()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    ActiveCell.Select
    Cells(ActiveCell.Row, 223).Select
    If ActiveCell.Value = "Y" Then
        ActiveSheet.Unprotect Password:="PasswordGoesHere"
        '------------------------------------
        ActiveCell.Rows("1:1").EntireRow.Select
        Selection.Copy
        Selection.Insert Shift:=xlDown
        '------------------------------------
        Cells(ActiveCell.Row, 13).Select
        ActiveSheet.Protect Password:="PasswordGoesHere"
    Else
        If Response = MsgBox("You can't insert a row here!", _
            vbCritical, "Warning") Then
        Cells(ActiveCell.Row, 13).Select
        End If
        Cells(ActiveCell.Row, 13).Select
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

when it tries to paste that specific row in the worksheet I get Run-time error '-2147417848 (80010108)': Automation error the object invoked has disconnected from its clients.

Upvotes: 1

Views: 1399

Answers (1)

peege
peege

Reputation: 2477

Try this: Using With ActiveSheet

Sub Staffing_AddRow()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    ActiveCell.Select

    'CHANGES BEGIN HERE
    With ActiveSheet
        If .Cells(ActiveCell.row, 223).Value = "Y" Then
            ActiveSheet.Unprotect Password:="PasswordGoesHere"
            '------------------------------------
            ActiveCell.Rows("1:1").EntireRow.Select
            Selection.Copy
            Selection.Insert Shift:=xlDown
            '------------------------------------
            .Cells(ActiveCell.row, 13).Select
            ActiveSheet.Protect Password:="PasswordGoesHere"
        Else
            If Response = MsgBox("You can't insert a row here!", _
                vbCritical, "Warning") Then
            .Cells(ActiveCell.row, 13).Select
            End If
            .Cells(ActiveCell.row, 13).Select
        End If
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End With
End Sub

See also: How to avoid using select statements in macros

Upvotes: 2

Related Questions