Some worksheet returns NULL for CodeName property. Why?

[Guest]

To illustrate my problem, sample code given below:

set oEx = New Excel.Application
set oBk = oEx.Workbooks.Open("MyBook.xls")

dim oSh as Excel.Worksheet
For Each oSh In oBk.Worksheets
Debug.Print oSh.CodeName
Next

Sometimes I could not refer to the codename of worksheet. After I
intentionally create a tiny macro in the "MyBook.xls" and delete it, I could
refer to the codename of the sheets by the above code. I need to always
refer to the codename in my current project. Someone knows how?

 

[Dave Peterson]

Take a look at this bifurcated thread:
http://groups.google.com/[email protected]
and
http://groups.google.com/groups?threadm=EFFF848A-8A14-4912-806A-789230F614E6@microsoft.com
(one line in your browser)

 

[Guest]

Thanks, Dave.

I gave it a try, but found that Office 2003 VBA did not allow us to
manipulate VBProject object from code for security purpose. Precisely
speaking, my VBA code is running at Access 2003 to get codename of worksheet
in an Excel book user created. Name of worksheet may goes, but I hate the
possibility that users may change the name of worksheet unintentionnaly.

Any other tips to avoid NULL codename will be appreciated.

Regards,

Shigeo Morita

 

[Dave Peterson]

That thread was the best attempt I've seen. You could search google for more
suggestions (or maybe change that security setting???)

 

[Peter T]

Hi Shigeo,

Are your users creating a new workbook from "your" template. If so open the
template in the VBE, select one of it's modules. Close everything down and
save. I think this should be enough to update all the codenames in the
template. Thereafter if user renames one of the existing sheets you should
be able to return its original codename.

Problem comes if user adds a new sheet, codename for this might not get
updated. I don't know any solution other than one of the methods in the
links Dave Peterson referred you to.

I have found that simply setting a reference to the project is enough to
update codenames. If it's acceptable to reference VBProject within the
template (rather than your code), try this in the "ThisWorkbook" module of
the template:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim oVBProj As Object
Set oVBProj = Me.VBProject
Set oVBProj = Nothing
End Sub

If users are not using a template that you can adapt as above I think you're
stuck! Try something like this:

Sub UpDateCodename(sht As Worksheet, str As String)
Dim oVBProj As Object
Set oVBProj = sht.Parent.VBProject
Set oVBProj = Nothing
str = sht.CodeName
End Sub

Sub TestUpdate()
'with vbe closed run from Alt F8
Dim wb As Workbook, ws As Worksheet, sName As String
Dim s, x
Set wb = Workbooks.Add
again:
For Each ws In ActiveWorkbook.Worksheets
s = ""
sName = ws.CodeName
If sName = "" Then
UpDateCodename ws, sName
s = "UpDateCodename called"
End If
MsgBox ws.Name & vbCr & s, , sName
Next
If x Then Exit Sub
x = 1
wb.Worksheets.Add
GoTo again ' is update required with the new sheet
End Sub

Until reading your post I thought the problem of updating codenames only
related to XL97 and XL2000 -
a) codename of new inserted sheet while the vbe is closed
b) codenames of all sheets in a wb that has never seen the vbe and while the
vbe is closed.

So, does a) or b) also apply to later versions, anyone?

Regards,
Peter

 

[Dave Peterson]

There's still a problem with xl2002.

Hi Shigeo,

Are your users creating a new workbook from "your" template. If so open the
template in the VBE, select one of it's modules. Close everything down and
save. I think this should be enough to update all the codenames in the
template. Thereafter if user renames one of the existing sheets you should
be able to return its original codename.

Problem comes if user adds a new sheet, codename for this might not get
updated. I don't know any solution other than one of the methods in the
links Dave Peterson referred you to.

I have found that simply setting a reference to the project is enough to
update codenames. If it's acceptable to reference VBProject within the
template (rather than your code), try this in the "ThisWorkbook" module of
the template:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim oVBProj As Object
Set oVBProj = Me.VBProject
Set oVBProj = Nothing
End Sub

If users are not using a template that you can adapt as above I think you're
stuck! Try something like this:

Sub UpDateCodename(sht As Worksheet, str As String)
Dim oVBProj As Object
Set oVBProj = sht.Parent.VBProject
Set oVBProj = Nothing
str = sht.CodeName
End Sub

Sub TestUpdate()
'with vbe closed run from Alt F8
Dim wb As Workbook, ws As Worksheet, sName As String
Dim s, x
Set wb = Workbooks.Add
again:
For Each ws In ActiveWorkbook.Worksheets
s = ""
sName = ws.CodeName
If sName = "" Then
UpDateCodename ws, sName
s = "UpDateCodename called"
End If
MsgBox ws.Name & vbCr & s, , sName
Next
If x Then Exit Sub
x = 1
wb.Worksheets.Add
GoTo again ' is update required with the new sheet
End Sub

Until reading your post I thought the problem of updating codenames only
related to XL97 and XL2000 -
a) codename of new inserted sheet while the vbe is closed
b) codenames of all sheets in a wb that has never seen the vbe and while the
vbe is closed.

So, does a) or b) also apply to later versions, anyone?

Regards,
Peter