I just tried this and it still didn't work. After putting in some msgbox
statements, I discovered that the "CodeName" property doesn't exist until
you enter the VBE environment. Your comment about using for each ... next
instead of for i ... next is valid in general. However, I found that a
workbook will always have at least one worksheet, thus the for i ... next
does work. I did switch to for each ... next because it makes the code
shorter and easier to read.

Thanks,
Mike Ober.

"J.E. McGimpsey" wrote in message
...
I would expect it to fail 100% of the time, unless you set i to
something 0 before the for i = ... line (since Worksheets(i) will
return a subscript out of range error if i=0).

How about:

Dim ws As Worksheet
Dim found As Boolean
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
found = True
Exit For
End If
Next ws
If found Then
'do stuff, e.g.,
MsgBox ws.Name
Else
MsgBox "No worksheets found with codename Sheet1"
End If


In article . net,
"Michael D. Ober" wrote:

I have some VBA code that does the following:

Option Compare Text

for i = 1 to ActiveWorkBook.Worksheets(i)
if ActiveWorkBook.Worksheets(i).CodeName = "Sheet1" then
set ws = ActiveWorkBook.Worksheets(i)
exit for
end if
next i

The problem is that this code doesn't always return as expected. In the
debuger, it always works, when run from an Add-In menu item, it works
about
10% of the time.

Any ideas,
Thanks,
Mike.