Dave,

I am testing with both Excel 2000 and Excel 2002.

I have discovered a new twist to this issue.

In all my tests I have never seen
ActiveWorkbook.ActiveSheet.CodeName return a blank result
when the code was in an XLS.

However, in an XLA the .CodeName property returns a blank
result in some situations and works properly in other
situations. The key to whether .CodeName works or not is
tied to whether the workbook and its owned worksheets
that .CodeName is being used on have ever been "opened"
in the VBE (Alt-F11).

For example, load an XLA Add-In that displays the
CodeName of the currently active worksheet. Create a new
workbook, e.g. Book2.xls, and the Add-In will display a
blank CodeName for Sheet1. Now press Alt-F11 to open the
VBE. Then close the VBE. Now the Add-In will properly
display Sheet1 as the CodeName of the sheet. Next, save
Book2.xls to disk and then exit from Excel.

Now, to prove that once a workbook has been opened in the
VBE then CodeName will forever work after that, start
Excel again. Use the Add-In on Sheet1 of the new
workbook Book1.xls that Excel creates by default, and you
will find that CodeName is blank. Now open Book2.xls
that was previously saved to disk, and the Add-In will
properly show that CodeName for Sheet1 is Sheet1. Click
back on Book1 and CodeName doesn't work on that
workbook. Click back on Book2.xls and CodeName works
properly on all sheets of that workbook.

Apparently, opening a workbook in the VBE does something
to that workbook that persists to disk and thereafter
CodeName in an Add-In works properly on that workbook.

I hope Microsoft fixes this. As for me, I've abandoned
using CodeName in my Add-In and have come up with an
alternative approach where I use the sheet name along
with a pseudo "sheet rename event" suggested by Shah
Shailesh.

Bob

-----Original Message-----
Two questions:

1. Are you saying that with the code in an xla, it
fails to find the code name in the activeworkbook?

2. Or are you saying that the no matter where the code
is, it fails to find the codename of a worksheet in an
xla file?

If you meant the first, I just tried it in xl2002 and it
found the correct worksheet for codename Sheet1 in a .xls
file.

If you meant the latter, then maybe it's the
activeworkbook.worksheets that's causing problems. (I've
never seen a .xla file the activeworkbook.)

What happened when you tried it and what version of
excel are you using?

Bob wrote:

Dave,

Thanks for the possible fix, but that doesn't fix the
problem in an XLA Add-In.

Oddly, the CodeName property always works properly in
an
XLS, but not in an XLA. I suppose for an XLA the VBE
must be brought into the picture, thus causing CodeName
to work.

Bob

-----Original Message-----
Maybe just looping through the worksheets:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myCodeName As String

myCodeName = ""
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = ActiveSheet.Name Then
myCodeName = wks.CodeName
Exit For
End If
Next wks

If myCodeName = "" Then
MsgBox "this shouldn't happen!"
Else
MsgBox myCodeName & vbNewLine & wks.Name
End If

End Sub

--

Dave Peterson

.


--

Dave Peterson

.