Automation Error on "VBProject.VBComponents"- VBA (Excel)

Question

I have this code:

Sub ChangeImportedBOQCodeName(importedName As String)
    ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name = "BOQ_" & importedName
End Sub

That is called from

Private Sub cmdOK_Click()

    Dim TargetName As String

    TargetName = cbxSheets.Text

    Set TargetSheet = TargetWB.Sheets(TargetName)
    TargetSheet.Copy After:=SourceWB.Sheets(SourceSheet.Index)

    ChangeImportedBOQCodeName ActiveSheet.CodeName

    ' Close the User Form
    Unload Me

    ' Inform User on Successful BOQ Import
    MsgBox "The selected BOQ was successfully imported to the Analysis", vbInformation, "Import Successful"

    ' Ask User to generate codes automatically
    Dim msgTxt As String
    msgTxt = "Generate codes in the imported BOQ, automatically ?" & vbNewLine & vbNewLine & _
             "(The proccess may take a while depending on the System specs and BOQ layout and size)"

    If MsgBox(msgTxt, vbYesNo, "Auto Code BOQ") = vbYes Then
        CheckImportedBOQ ActiveSheet
    Else
        MsgBox "Auto code genrate was aborted", vbInformation, "Aborted"
    End If

End Sub

But I am getting this error:

Run-time error '-2147417848 (80010108)' Automation error The object invoked has disconnected from its client

And excel exits abruptly.

Problem is that this error never occurred before and the code was working very fine.

I also tried commenting ChangeImportedBOQCodeName ActiveSheet.CodeName this line and the code works.

Shai Rado Shai Rado · Accepted Answer · 2017-08-13T10:49:34

In your Sub ChangeImportedBOQCodeName you are passing ActiveSheet.CodeName, so all you need to do is change:

ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).Name = "BOQ_" & importedName

To:

ActiveWorkbook.VBProject.VBComponents(importedName).Name = "BOQ_" & importedName

Automation Error on "VBProject.VBComponents"- VBA (Excel)

1 Answers