Links between VBComponents and Sheets 3

[Paul]

I apologize for inadequately explaining my problem.

We have several nearly identical Excel workbooks that
make reference, i.e., ActiveVBProject.References.Add, to
the VBA in a particular Addin. The code in the Addin
assumes the existence of a VBComponent, in the client
Excel workbook, associated with a particular sheet.

For some reason, i.e., unexplained error, in one of these
workbooks, the Properties of the specified VBComponent
have been set to Nothing. This is observed through the
Locals window. As a result, none of the Controls on the
associated sheet function; they use the referenced Addin
code that makes reference back to the corrupted component.

An attempt was made to delete the sheet. The worksheet
was removed, from the Sheets collection, but the
Component remained as part of the VBProject. Also, there
is no way to rename or delete the corrupted component.
It looks like Excel treats it as the ThisWorkbook
component.

We simply want to remove the currupted component, or at
least give it a useless name, and copy a working version
of the sheet, along with its VBComponent, to the
workbook. The referenced VBA Addin code would work
correctly.

What should be done? Thanks again.

 

[Rob Bovey]

Hi Paul,

What you describe is a very common form of workbook corruption. There's
nothing I know of you can do to solve it other than rebuild the corrupted
workbook from scratch. Don't copy anything directly between the corrupted
workbook and the version you're rebuilding or you risk bringing the
corruption into the new workbook.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *