What happens when you copy that code into a module in the real workbook? It
didn't work for me.

The only way I know to use the codename from a different workbook is to cycle
through the sheets and check:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim mySheet1 As Worksheet

Set mySheet1 = Nothing
For Each wks In ActiveWorkbook.Worksheets
If StrComp(wks.CodeName, "sheet1", vbTextCompare) = 0 Then
Set mySheet1 = wks
Exit For
End If
Next wks

If mySheet1 Is Nothing Then
MsgBox "not found"
Else
MsgBox "Found it and its name is: " & mySheet1.Name
End If

End Sub


Dianne wrote:

I have a workbook that has a reference to an add-in. I know that the
reference works, because I can call the addin's functions and variables from
within my workbook.

However, in my addin, I refer to the workbook's worksheets using their
codenames (so that if my users change the worksheet name, it doesn't mess up
my code). When I run the code, I get an error message:

"Object doesn't support this property or method"

Here's an example --

ActiveWorkbook has a worksheet with the codename shtDiary

Addin code looks like this:

strDiaryDate = ActiveWorkbook.shtDiary.Cells(1,1).Value2

if I change it to refer to the collection using the worksheet name it works
just fine:

strDiaryDate = ActiveWorkbook.Worksheets("Diary").Cells(1, 1).Value2

Why isn't this working? Any help greatly appreciated.

Dianne

--

Dave Peterson