CodeName property not being populated

[Doug]

I have encountered a problem when creating a new Worksheet
in code and then attempting to refer to that sheets
CodeName property. It appears that when the Code window is
open the CodeName property for the newly added sheet is
populated and available immediately. When the Code window
is not open the CodeName property of the newly added
Worksheet is empty.

Any thoughts would be greatly appreciated.

[Chip Pearson]

Doug,

Can you post some code or procedures that I can use to replicate
the problem? What version of Excel are you using?

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Doug" <do...@dscarr.com> wrote in message
news:093901c2c327$cf7000c0$89f82ecf@TK2MSFTNGXA01...

[Doug]

I am using Excel97 running on Windows2000 Professional.

This example that I placed in a standard code module
generates the same error. When the VBA Code Window is open
it runs just fine producing the correct CodeName. When the
VBA Code Window is closed the CodeName property is empty.

Public Sub GetCodeName()
Dim wks As Excel.Worksheet

Set wks = ActiveWorkbook.Worksheets.Add()

MsgBox wks.CodeName

Set wks = Nothing

End Sub

>.
>

[Chip Pearson]

Doug,

That is a strange problem -- I've never run into it before, but I
can confirm what you see. It would appear that when the VBA Editor
is closed, VBA doesn't do the recompile that is required to assign
the CodeName property to the workbook. However, if you force a
recompile, the codename gets populated.

For example, run the following code with the VBA Editor closed.

Public Sub GetCodeName()
Dim wks As Excel.Worksheet

Dim S As String
Set wks = ActiveWorkbook.Worksheets.Add()

'Application.VBE.CommandBars.ActiveMenuBar.FindControl(ID:=578).Exe
cute
S = wks.CodeName
If S = "" Then
S = "<empty>"
End If
MsgBox "SheetName: " & wks.Name & " Codename: " & S
End Sub

You get the result you describe: CodeName is empty. Now, uncomment
the Application..... line of code, close the VBA Editor, and run
the code again. You get proper code name. It would seem that VBA
must recompile in order to populate the CodeName property.

Strange.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Doug" <do...@dscarr.com> wrote in message

news:0a2a01c2c6dd$cda9e210$d5f82ecf@TK2MSFTNGXA12...