locked
EXCEL VBA: Inserting rows code returns error "2147417848 The object invoked has disconnected from its clients" RRS feed

  • Question

  • I was executing the following code in Excel 2013 that is linked to a button I use to add multiple rows in a chosen section of a worksheet

    Sub Add_Rows_dc()
    
        Sheet53.Unprotect ("xxx")
        
        X = Range("C1").Value + 1 'Section Counter
        Y = ListSheet.Range("I" & X).Value 'Existing rows counter
        Z = ListSheet.Range("H" & X).Value 'Position counter
        Righe = InputBox("How many rows would you like to add?", , "1")
        
        If Righe < 1 Then GoTo err  ' test for invalid row number
         RigheSheet.Rows(X).Copy
         Rows(Z - 1 & ":" & Righe + Z - 2).Insert Shift:=xlDown
        GoTo Fine
       
    err:
        
    Mess = MsgBox("PLEASE INSERT A VALID NUMBER OF ROWS", vbCritical)
        
    Fine:
    
    'Formulas update
    
    Z = ListSheet.Range("H" & X).Value 'Position counter update
    Range("U" & Z - Righe - 2 & ":AF" & Z - 2).FillDown
    
        Sheet53.Protect "xxx", , , , , True
        
    End Sub

    From time to time it returns me the error "2147417848 The object invoked has disconnected from its clients" and the debug point out the line

    Rows(Z - 1 & ":" & Righe + Z - 2).Insert Shift:=xlDown

    I cannot find any specific reason due to the fact that the error seems randomic, sometimes the code is perfectly executed, sometimes not.

    I wrote this piece of code in Excel 2010 and never encountered such problem before reusing it in Excel 2013

    Has someone an insight or a suggestion?

    Thank you very much

    Thursday, February 12, 2015 11:49 PM

Answers

  • Re:  error won't go away

    Have you checked the value of the variable "Z" when the error occurs?
    If Z is < 2 then your code will error... there is no Row(0).

    Also, add another "protect" line in the err section and keep that section last, just before End Sub.
    That's all I've got.
    '---
    Jim Cone

    • Marked as answer by Caillen Sunday, March 1, 2015 11:03 AM
    Monday, February 16, 2015 10:39 PM
  • Re:  strange error

    Try it this way...
    (might work, might not)
    '---
    Sub Add_Rows_dc_R1()
     Dim X As Double
     Dim Y As Double
     Dim Z As Double
     Dim Righe As Variant
     Dim Mess As Long

        Sheet53.Unprotect ("xxx")

        X = Range("C1").Value + 1 'Section Counter
        Y = ListSheet.Range("I" & X).Value 'Existing rows counter
        Z = ListSheet.Range("H" & X).Value 'Position counter
        Righe = InputBox("How many rows would you like to add?", , "1")

        If Righe < 1 Then GoTo errX  ' test for invalid row number
         RigheSheet.Rows(X).Copy
         Rows(Z - 1 & ":" & Righe + Z - 2).Insert Shift:=xlDown
        GoTo Fine  

    Fine:
    'Formulas update
     Z = ListSheet.Range("H" & X).Value 'Position counter update
     Range("U" & Z - Righe - 2 & ":AF" & Z - 2).FillDown
     Sheet53.Protect "xxx", , , , , True
    Exit Sub

    errX:
    Mess = MsgBox("PLEASE INSERT A VALID NUMBER OF ROWS", vbCritical)
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by Caillen Sunday, March 1, 2015 11:03 AM
    • Edited by James Cone Monday, October 31, 2016 7:09 PM
    Friday, February 13, 2015 4:56 AM

All replies

  • Re:  strange error

    Try it this way...
    (might work, might not)
    '---
    Sub Add_Rows_dc_R1()
     Dim X As Double
     Dim Y As Double
     Dim Z As Double
     Dim Righe As Variant
     Dim Mess As Long

        Sheet53.Unprotect ("xxx")

        X = Range("C1").Value + 1 'Section Counter
        Y = ListSheet.Range("I" & X).Value 'Existing rows counter
        Z = ListSheet.Range("H" & X).Value 'Position counter
        Righe = InputBox("How many rows would you like to add?", , "1")

        If Righe < 1 Then GoTo errX  ' test for invalid row number
         RigheSheet.Rows(X).Copy
         Rows(Z - 1 & ":" & Righe + Z - 2).Insert Shift:=xlDown
        GoTo Fine  

    Fine:
    'Formulas update
     Z = ListSheet.Range("H" & X).Value 'Position counter update
     Range("U" & Z - Righe - 2 & ":AF" & Z - 2).FillDown
     Sheet53.Protect "xxx", , , , , True
    Exit Sub

    errX:
    Mess = MsgBox("PLEASE INSERT A VALID NUMBER OF ROWS", vbCritical)
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by Caillen Sunday, March 1, 2015 11:03 AM
    • Edited by James Cone Monday, October 31, 2016 7:09 PM
    Friday, February 13, 2015 4:56 AM
  • Thanks Jim,

    I've try it but returns me the same error at the same point (I just kept the err: lines above because otherwise  the worksheet remains unprotected).

    Also in this case it seems randomic, several times it works but suddenly the error is popping out; I just notice that it seems more frequent when I had the VBA windows opened in Background.

    Monday, February 16, 2015 9:44 PM
  • Re:  error won't go away

    Have you checked the value of the variable "Z" when the error occurs?
    If Z is < 2 then your code will error... there is no Row(0).

    Also, add another "protect" line in the err section and keep that section last, just before End Sub.
    That's all I've got.
    '---
    Jim Cone

    • Marked as answer by Caillen Sunday, March 1, 2015 11:03 AM
    Monday, February 16, 2015 10:39 PM