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