Running the code below in excel 97 VBA on a just opened new
workbook (book1.xls), then reopening the workbook after saving it
causes excel to crach before getting the book reopend.
It looks like the process of programatically changing the VBE
component name of a worksheet irreversibly corrupts
a workbook.

The code below is what I boiled down a bug that crept into
the process of cloning a workbook I was attempting create by
rebuilding all workbook components from scratch.
I was hoping to be able to reset all worksheet VBE component
names to the values as they were in the source book I was cloning.
(BTW, I can successfully reassign VBE names to user forms
and modules, just not worksheets)

I believe these names are rather cosmetic: So far I have not
seen any funcionality to using anything but the default ones
assigned when the sheets are created, but I find it nice to give them
more meaningful names for ease in navigating through the project
brower. Changing the names manually is no problem, but running the
code below definitle is . . .

Anybody run into this problem and have a work-around?

Here's the code:

Function ComponentofWS(ws As Worksheet) As VBComponent
Dim ThisProperty As Property
Dim SourceComponent As VBComponent, SourceComponents As VBComponents
Set SourceComponents = ws.Parent.VBProject.VBComponents
For Each SourceComponent In SourceComponents
If SourceComponent.Type = vbext_ct_Document Then
For Each ThisProperty In SourceComponent.Properties
If ThisProperty.Name = "Name" Then
If ThisProperty.Value = ws.Name Then
Set ComponentofWS = SourceComponent
Exit Function
End If
End If
Next
End If
Next
End Function
Sub CorruptBook1()
ComponentofWS(Workbooks("book1.xls").Worksheets(1) ).Name = "Sheet2"
End Sub

|\|.

Try setting the Microsoft VBA Extensilibity references.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=489708

That's not the issue! Of course I have to set those references
to even get the code I posted to run. The problem is *not* that
the code fails to run. The problem is that even though it runs
and terminates without any error showing up at the time, it
appears to cause some sort of corruption that only exhibits
itself when you try to re-open the sheet it ran on
after having saved it.

|\|.

On Wed, 30 Nov 2005 23:19:41 -0600, Myles
wroth:


Try setting the Microsoft VBA Extensilibity references.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=489708