Sleeper: Run-time error '2147417848 (80010108)'

[LongBeard]

How does one dynamically add both new controls and VBA code that responds to events associated with these new controls on a userform one is currently using?

All my attempts have given me both the new controls and the new VBA code, but also an error box:

Run-time error '2147417848 (80010108)'

Automation error
The object involved has disconnected from its clients.

My research has told me I should not add code to a code module currently being used, but I am clueless how to solve my problem. The code I am currently using is:


Sub AddCodetoFormAddData(str1 As String)
Dim appExcel As Excel.Application
Dim objForm As Object
Set appExcel = Excel.Application
Set objForm = appExcel.Workbook("Data.xls").VBProject.VBComponents("Form_AddData")
'This line added just to confirm execution of this sub
MsgBox objForm.CodeModule.countoflines + 1
With objForm.CodeModule
.InsertLines .countoflines + 1, str1
End With
End Sub

Any help would be greatly appreciated,
Long Beard

[Daniel Klann]

Hi,

Your code works ok for me when ran from within a standard module within either the same workbook, or a different workbook. Can you describe how you're using this code? Are you using it from a Visual Basic project, rather than from within Excel? Are you running from within the Data.xls workbook or outside? What kind of code module are you running the procedure in?

Cheers,
Dan

[doctordoggie]

This ran for me, too - I wonder if the problem may only be sporadic - If a module is being compiled while code within it is running, I suspect it would disconnect. Because the code opens a dialogue box where you enter your macro name, the compilation may be so fast there isn't an error. On longer code though there could be enough of a delay to crash the program.

Thant's my guess anyway.

[LongBeard]

No other workbooks are involved, and all my code is written in Excel's Microsoft Visual Basic editor.

I have tried running the code in both Form_AddData's code module, and as a call from Form_AddData's code module. Form_AddData is the userform I am attempting to modify dynamically.

The code I showed you written on a standard module page, and was called from Form_AddData's code module. I have read that one should not modify a code module from the form's code, which is why I attempted to use the call approach.

Also, the problem is not sproatic, but unfortunately very consistant.

I appreciate your feedback,
Long Beard

[Bob Phillips]

No other workbooks are involved, and all my code is written in Excel's Microsoft Visual Basic editor.

I have tried running the code in both Form_AddData's code module, and as a call from Form_AddData's code module. Form_AddData is the userform I am attempting to modify dynamically.

The code I showed you written on a standard module page, and was called from Form_AddData's code module. I have read that one should not modify a code module from the form's code, which is why I attempted to use the call approach.

Also, the problem is not sproatic, but unfortunately very consistant.

I appreciate your feedback,
Long Beard

Can you post the workbook so that we can see it in action?

[LongBeard]

xld,

Will be glad to, but will need some time to remove proprietary info. Can accomplish that by days end.

But question? How do I post an entire workbook?

LongBeard

[Bob Phillips]

xld,

Will be glad to, but will need some time to remove proprietary info. Can accomplish that by days end.

But question? How do I post an entire workbook?

LongBeard

When you reply, if you scroll down the page, there is a 'Manage Attachmnents' button.

[Ken Puls]

Hi Longbeard,

Just an FYI, you must zip the attachment before attempting to upload it to our server.

HTH,

[LongBeard]

Here is a simplified version of my workbook. I was hoping the simplified version would make the code work, but the issue still remains.

Any help would be appreciated,
LongBeard